Secure PostgreSQL Access with SSO, TLS, and Auditing
Secure PostgreSQL access with SSO while keeping databases off the public internet, authenticating with short-lived client certificates, and recording every query in the audit log.
Setup takes four steps: create a join token, configure the PostgreSQL server,
grant access through AlmaForge RBAC, and connect with native psql or GUI clients.
Running CockroachDB? See the CockroachDB database access guide.
Prerequisites
PostgreSQL CLI
On your computer, check psql, the PostgreSQL command-line client:
psql --versionpsql (PostgreSQL) 18.6 (Homebrew)If it is missing, install libpq, which includes the client:
brew install libpq...==> Summary...export PATH="$(brew --prefix libpq)/bin:$PATH"psql --versionpsql (PostgreSQL) 18.6 (Homebrew)export prints nothing. Add the same export line to ~/.zshrc to
keep the PATH setting in future terminals. For other operating systems,
use PostgreSQL downloads.
PostgreSQL server
On your computer, open a second terminal and SSH into the database
server. Replace <ssh-user> and <database-host> with your SSH
account and server address:
ssh <ssh-user>@<database-host>...[<ssh-user>@<database-host> ~]$On the database server, check that you can run administrator commands, then check the operating system and installed database package:
sudo -v# No output on success. A password prompt may appear first.grep -E '^(ID_LIKE|VERSION_ID)=' /etc/os-releaseID_LIKE="rhel centos fedora"VERSION_ID="9.8"rpm -q postgresql-serverpostgresql-server-13.23-6.el9_8.x86_64sudo -v must succeed. If PostgreSQL is not installed, install the distribution's
postgresql-server package and initialize the database with postgresql-setup --initdb
before continuing.
This guide uses the postgresql systemd service and /var/lib/pgsql/data directory.
Packages from other repositories may use different service names and paths.
AlmaForge CLI
On your computer, check whether alma is installed:
alma version --clientClient Version: v2026.1.0+<build>If the command is not found, install it and repeat the check:
curl -fL https://get.almaforge.com/install.sh | sh...==> AlmaForge installed successfullyalma version --clientClient Version: v2026.1.0+<build>The installer supports Linux and macOS on both amd64 and arm64. See CLI installation for package-specific options.
AlmaForge Cluster
On your computer, sign in to your cluster and check your permissions.
Get your cluster's hostname from its administrator. If you do not have
an AlmaForge cluster yet, complete the
standalone deployment before continuing.
Replace almaforge.example.com below with that hostname. Run the login command
and complete the sign-in in your browser:
alma login --proxy=almaforge.example.com:443...
> Profile URL: https://almaforge.example.com:443
Logged in as: alice@example.com
Cluster: almaforge.example.com
Roles: admin
...
After signing in, check your account and roles:
alma status> Profile URL: https://almaforge.example.com:443
Logged in as: alice@example.com
Cluster: almaforge.example.com
Roles: admin
...
In the status output, check that:
Clusternames the cluster you intend to configure.Logged in asshows your account.Rolesincludesadmin.
If admin is missing, ask a cluster administrator to grant that role,
then run alma logout and repeat
the login and status commands. Continue once Roles includes admin.
Step 1. Create AlmaForge join token
On your computer, create an AlmaForge join token. The database agent uses it to join your AlmaForge cluster:
alma create token --services=databaseToken created. client_id: <client-id> client_secret: <client-secret>You will copy client_id and client_secret into the agent configuration
in Step 2. They are valid for 30 minutes. If they expire before the agent
joins, create a new token and use its values.
Step 2. Configure the PostgreSQL server
Install and configure the agent
The agent is almad with its Database Service enabled. It
connects outward to your cluster on port 443 and carries connections
between users and PostgreSQL.
On the PostgreSQL server, check whether almad is installed:
almad versionAlmaForge v2026.1.0+<build>If the command is not found, install the AlmaForge package:
curl -fL https://get.almaforge.com/install.sh | sh...==> AlmaForge installed successfullyalmad versionAlmaForge v2026.1.0+<build>On an RPM-based system, the installer uses the RPM package and installs the
almad systemd service. It supports both amd64 and arm64.
The version command must succeed before you configure the agent.
For other Linux distributions, see
Linux package installation.
Already running an agent on this host?
Keep its existing settings and enable ALMA_DATABASE_SERVICE=true.
Preserve the other services and label selectors. The join credentials
must permit the database service. The local target created below
does not need a label selector.
On the PostgreSQL server, open the agent configuration:
sudoedit /etc/almaforge/almad.env# Your text editor opens.For a new agent, enter the settings below. Replace ALMA_PROXY with
your cluster's hostname and port 443. Set ALMA_CLIENT_ID and
ALMA_CLIENT_SECRET to the values from Step 1:
# Database Service agent on the PostgreSQL host.
# Install at /etc/almaforge/almad.env as root:root with mode 0600.
ALMA_PROXY=almaforge.example.com:443
# Printed by `alma create token --services=database`.
ALMA_CLIENT_ID=paste-client-id-here
ALMA_CLIENT_SECRET=paste-client-secret-here
ALMA_DATABASE_SERVICE=true
ALMA_SSH_SERVICE=false
# Agent labels, separate from the database's labels.
ALMA_LABELS=env=demo
Save the file and close the editor. Restrict it to root because it contains the join credentials:
sudo chown root:root /etc/almaforge/almad.env# No output on success.sudo chmod 0600 /etc/almaforge/almad.env# No output on success.Create the TLS certificates
The agent and PostgreSQL run on the same server. Their connection uses the
loopback interface (localhost, or 127.0.0.1), so this traffic stays
on that server. A self-signed certificate for localhost is sufficient.
You do not need a public hostname or a certificate from a public CA.
PostgreSQL uses the AlmaForge database CA to verify client certificates presented by the agent.
Keep running these commands on the PostgreSQL server.
Download your cluster's public database CA. Replace almaforge.example.com
with your cluster's hostname:
curl -fsS -o almaforge-db-ca.pem 'https://almaforge.example.com:443/api/v1/auth/export?type=db'# No output on success.openssl x509 -in almaforge-db-ca.pem -noout# No output means the file contains a valid certificate.sudo install -o postgres -g postgres -m 0644 almaforge-db-ca.pem /var/lib/pgsql/data/server.cas# No output on success.Create a self-signed server certificate and private key for localhost:
sudo openssl req -x509 -newkey rsa:2048 -nodes -days 3650 \ -subj '/CN=localhost' \ -addext 'subjectAltName=DNS:localhost,IP:127.0.0.1' \ -keyout /var/lib/pgsql/data/server.key -out /var/lib/pgsql/data/server.crt...-----sudo chown postgres:postgres /var/lib/pgsql/data/server.key /var/lib/pgsql/data/server.crt# No output on success.sudo chmod 0600 /var/lib/pgsql/data/server.key# No output on success.Enable TLS in PostgreSQL
Open the PostgreSQL configuration on the server:
sudoedit /var/lib/pgsql/data/postgresql.conf# Your text editor opens.Configure PostgreSQL to require TLS and use the certificate files:
# Local TLS configuration on a dedicated PostgreSQL host.
# Configures mutual TLS using the local server certificate and AlmaForge database CA.
listen_addresses = 'localhost'
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_ca_file = 'server.cas'
Open pg_hba.conf and require client certificates for local TCP connections:
sudoedit /var/lib/pgsql/data/pg_hba.conf# Your text editor opens.# Local TLS configuration on a dedicated PostgreSQL host.
# Local root and postgres connect over the socket. TCP connections require client certificates.
local all all peer
hostssl all all 127.0.0.1/32 cert
hostssl all all ::1/128 cert
Save the file and restart PostgreSQL:
sudo systemctl restart postgresql# No output on success.systemctl is-active postgresqlactiveCreate the database and accounts
Run psql as the postgres user over the local socket to create the demo database, the administrative account for automatic user provisioning, the user role, and the sample table:
sudo -u postgres psql -v ON_ERROR_STOP=1 <<'EOF'SELECT 'CREATE DATABASE demo' WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'demo')\gexecSELECT 'CREATE ROLE "alma-admin" LOGIN CREATEROLE' WHERE NOT EXISTS (SELECT FROM pg_roles WHERE rolname = 'alma-admin')\gexecSELECT 'CREATE ROLE "alma-auto-user"' WHERE NOT EXISTS (SELECT FROM pg_roles WHERE rolname = 'alma-auto-user')\gexecSELECT 'CREATE ROLE "alma-demo-reader"' WHERE NOT EXISTS (SELECT FROM pg_roles WHERE rolname = 'alma-demo-reader')\gexecGRANT "alma-auto-user" TO "alma-admin" WITH ADMIN OPTION;GRANT "alma-demo-reader" TO "alma-admin" WITH ADMIN OPTION;\c demoGRANT USAGE, CREATE ON SCHEMA public TO "alma-admin";CREATE TABLE IF NOT EXISTS demo (id INT PRIMARY KEY, name TEXT);INSERT INTO demo VALUES (1, 'alice'), (2, 'bob'), (3, 'carol') ON CONFLICT DO NOTHING;GRANT USAGE ON SCHEMA public TO "alma-demo-reader";GRANT SELECT ON ALL TABLES IN SCHEMA public TO "alma-demo-reader";EOFRegister PostgreSQL with the agent
Still on the PostgreSQL server, display the public server certificate:
sudo cat /var/lib/pgsql/data/server.crt-----BEGIN CERTIFICATE-----...-----END CERTIFICATE-----Copy the entire certificate, including the BEGIN and END lines.
The agent uses it to verify PostgreSQL. Then open the target configuration:
sudo install -d -o root -g root -m 0700 /etc/almaforge/almad.d# No output on success.sudoedit /etc/almaforge/almad.d/postgres.yaml# Your text editor opens.For a new file, copy the following YAML. If the file already exists,
update the existing resource and preserve its other settings. Replace
<paste the complete server certificate here> with the certificate you
copied. Indent every certificate line by six spaces beneath caCert: |:
# PostgreSQL target for an agent running on the database server.
# Replace caCert with the complete server certificate or its issuing CA.
apiVersion: almaforge.com/v1
kind: DatabaseTarget
metadata:
name: postgres
labels:
db: postgresql
env: prod
spec:
protocol: postgres
uri: 127.0.0.1:5432
adminUser:
name: alma-admin
defaultDatabase: demo
tls:
caCert: |
<paste the complete server certificate here>
Check that the certificate includes both its BEGIN and END lines,
then save the file and close the editor. Restrict the file to root:
sudo chown root:root /etc/almaforge/almad.d/postgres.yaml# No output on success.sudo chmod 0600 /etc/almaforge/almad.d/postgres.yaml# No output on success.Start and check the agent
Enable the services at boot and start the agent:
sudo systemctl enable postgresql almad...sudo systemctl restart almad# No output on success.systemctl is-active postgresql almadactiveactiveBoth services must report active. If either fails, inspect its log:
sudo journalctl -u postgresql -u almad -n 50 --no-pager...<date> <time> <database-host> almad[<pid>]: <log-message>...Resolve any reported errors before continuing. The agent reads the
target configuration at startup, so restart almad after
changing /etc/almaforge/almad.d/postgres.yaml.
Step 3. Grant access
On your computer, confirm that the agent has registered the database:
alma get dbHost Name Protocol URI Labels Version---------------- --------------- -------------- ------------------- ------------------ --------<database-host> postgres postgres <database-uri> db=postgresql ... 2026.1.0Check that postgres appears with protocol postgres and your database
server in the Host column. If either is missing, see
Database is missing or has no host.
Download postgres-access.yaml
and save it locally.
This role allows access to targets labeled db: postgresql and to the
demo database. createDbUserMode: keep creates a database account
named after the user's full AlmaForge login, such as
alice@example.com, and retains it after the session ends. PostgreSQL
grants control what the account can do inside the database. The
alma-demo-reader role permits reading the sample table.
# Allow access to the dedicated PostgreSQL target.
# Map an SSO group to this role in your OIDC connector.
apiVersion: almaforge.com/v1
kind: Role
metadata:
name: postgresql-access
spec:
options:
createDbUserMode: keep # Create an account for the AlmaForge login and retain it after disconnect.
allow:
databaseLabels:
db: postgresql
databaseRoles:
- alma-demo-reader
databaseNames:
- demo
Apply the role:
alma apply -f postgres-access.yamlrole 'postgresql-access' has been createdCreating the role does not assign it to anyone. To grant it to an SSO group, list your connectors:
alma get oidcKind Name------------- -----------OIDCConnector company-ssoFind the connector your team uses to sign in. Replace <connector-name>
with its name from the output, then open it in your text editor:
alma edit oidc/<connector-name># Your text editor opens. After saving your changes:oidc connector "<connector-name>" has been updatedUnder spec.claimsToRoles, find the entry for the group that should have
PostgreSQL access. Add postgresql-access to that entry's roles list. Preserve
the existing roles and mappings, including those that grant admin,
then save and close the editor. The command applies your changes.
If the group has no mapping yet, follow
OIDC role mapping
to add one.
Step 4. Connect
On your computer, sign out of saved sessions, then sign back in with an
account in the group granted access in Step 3 to get a session with the
new role. logout clears saved sessions for all clusters. Use your
cluster's hostname in the login command:
alma logoutLogged out all users from all proxies.alma login --proxy=almaforge.example.com:443...> Profile URL: https://almaforge.example.com:443 Logged in as: alice@example.com Cluster: almaforge.example.com Roles: postgresql-access ...alma status> Profile URL: https://almaforge.example.com:443 Logged in as: alice@example.com Cluster: almaforge.example.com Roles: postgresql-access ...Check that Roles includes postgresql-access. If it is missing, check
your SSO group membership and the mapping from Step 3 before continuing.
Connect to the sample database:
alma db connect --db-name=demo postgres...demo=> SELECT current_user, current_database(); current_user | current_database-------------------+------------------ alice@example.com | demo(1 row)
demo=> SELECT count(*) FROM demo; count------- 3(1 row)The first query must show your full AlmaForge login and database
demo. The second must return 3. These results confirm that account
creation, certificate authentication, and access to the sample data work.
Type \q to exit.
Advanced configuration
Automatic user provisioning
The example enables createDbUserMode: keep. The agent connects as
alma-admin, creates an account named after the user's AlmaForge
login, and grants alma-demo-reader for the session. The account remains
after disconnect. Use bestEffortDrop to request its removal instead.
See automatic user provisioning
for role settings and cleanup behavior.
Dynamic labels
Use spec.dynamicLabels for values that a command on the agent refreshes,
such as the PostgreSQL version. The command must be available on that host.
# Self-hosted PostgreSQL with dynamic labels. Each entry under
# `spec.dynamicLabels` runs a shell command on the agent at the
# configured period and stores stdout as the label value. Use this
# for values that change between deployments — engine version,
# replica role, pgBouncer pool name — instead of hard-coding them
# under `metadata.labels`.
apiVersion: almaforge.com/v1
kind: DatabaseTarget
metadata:
name: orders-postgres
labels:
env: prod
team: payments
spec:
protocol: postgres
uri: orders-prod.internal:5432
dynamicLabels:
engine_version:
period: 5m # Refresh every five minutes.
command: # Argv form; the agent runs the binary directly, no shell parsing.
- /bin/sh
- -c
- psql --version | awk '{print $3}'
Multiple PostgreSQL instances per agent
Add a target file per database under /etc/almaforge/almad.d/ and restart the
agent. For centrally applied targets, configure ALMA_MATCH_LABELS
to match their labels. The agent must reach each target's address, and
the server certificates must cover those addresses.
Identity files for non-interactive psql
For unattended jobs, use workload identity and grant only the database names and roles the job needs.
GUI clients and scripts
For a GUI client such as DBeaver or pgAdmin, leave this command running:
alma proxy db --tunnel --db-name=demo postgresStarted authenticated tunnel for the PostgreSQL database "postgres" in cluster "almaforge.example.com" on 127.0.0.1:54321....Use the address and port it prints, your full AlmaForge login as the
user, and demo as the database. Use plain TCP to the local listener.
AlmaForge handles certificate authentication through the tunnel.
Architecture
The database agent runs beside PostgreSQL and opens an outbound tunnel to the AlmaForge Proxy. PostgreSQL listens on localhost and trusts the agent's client certificates. See the database architecture for the Auth, Proxy, and Database Service roles.
Troubleshooting
Database is missing or has no host
On the database server, check that /etc/almaforge/almad.d/postgres.yaml
exists and ALMA_DATABASE_SERVICE=true is set in /etc/almaforge/almad.env.
The agent must reach your cluster on port 443. Inspect its log:
sudo journalctl -u almad -n 50 --no-pager...<date> <time> <database-host> almad[<pid>]: <log-message>...Restart almad after correcting its configuration, then repeat
alma get db on your computer.
Access is denied
Run alma status and check for postgresql-access. If it is
missing, verify the SSO group mapping from Step 3, then sign out and
back in. The role's database labels must match the target file.
Account creation fails
Check that the target file has adminUser.name: alma-admin and
adminUser.defaultDatabase: demo, and that your role has
createDbUserMode: keep. The alma-admin account must have LOGIN,
CREATEROLE, permission to install its procedures, and permission to
grant alma-auto-user and alma-demo-reader. Connect with your own
AlmaForge login, which the CLI selects automatically.
PostgreSQL rejects the certificate login
For no pg_hba.conf entry or a password prompt from PostgreSQL, check
/var/lib/pgsql/data/pg_hba.conf. The setup uses hostssl ... cert
for both localhost addresses. PostgreSQL uses the first matching rule,
so an earlier password rule changes how the connection authenticates.
Restart PostgreSQL after correcting the configuration.
Certificate validation fails
PostgreSQL's ssl_ca_file must reference server.cas, the public
AlmaForge database CA downloaded from the cluster. The agent's
spec.tls.caCert must contain the PostgreSQL server certificate.
These files establish trust in opposite directions. If you change the
server certificate, update the target file and restart almad.
Limitations
- Automatic user provisioning requires connecting with your full
AlmaForge login. The agent does not provision an arbitrary name
supplied through
--db-user. - Database access roles control the connection and granted PostgreSQL roles. PostgreSQL grants determine permissions on schemas and tables.
References
- Database access overview: architecture and supported databases.
- RBAC reference: database access fields on a role.
- Declarative configuration: the
DatabaseTargetschema.