Secure CockroachDB Access with SSO, TLS, and Auditing

Secure CockroachDB 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 CockroachDB server, grant access through AlmaForge RBAC, and connect with native psql or cockroach sql.

info

Running PostgreSQL? See the PostgreSQL database access guide.

Prerequisites

CockroachDB CLI

On your computer, check psql, the PostgreSQL command-line client:

Terminal

psql --versionpsql (PostgreSQL) 18.6 (Homebrew)

If it is missing, install libpq, which includes the client:

Terminal

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.

CockroachDB 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:

Terminal

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 database version:

Terminal

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"cockroach version --build-tagv26.3.2

sudo -v must succeed. If cockroach is not found, install CockroachDB from Cockroach Labs into /usr/local/bin/cockroach. Otherwise, the command prints its version.

Use an RPM-based Linux host with dnf for this example.

AlmaForge CLI

On your computer, check whether alma is installed:

Terminal

alma version --clientClient Version: v2026.1.0+<build>

If the command is not found, install it and repeat the check:

Terminal

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:

Terminal

alma login --proxy=almaforge.example.com:443

text

...
> Profile URL:        https://almaforge.example.com:443
  Logged in as:       [email protected]
  Cluster:            almaforge.example.com
  Roles:              admin
  ...

After signing in, check your account and roles:

Terminal

alma status

text

> Profile URL:        https://almaforge.example.com:443
  Logged in as:       [email protected]
  Cluster:            almaforge.example.com
  Roles:              admin
  ...

In the status output, check that:

• Cluster names the cluster you intend to configure.
• Logged in as shows your account.
• Roles includes admin.

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:

Terminal

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 CockroachDB 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 CockroachDB.

On the CockroachDB server, check whether almad is installed:

Terminal

almad versionAlmaForge v2026.1.0+<build>

If the command is not found, install the AlmaForge package:

Terminal

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 CockroachDB server, open the agent configuration:

Terminal

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:

almad.env:

/etc/almaforge/almad.env

# Database Service agent on the CockroachDB 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:

Terminal

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 CockroachDB 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.

CockroachDB uses the AlmaForge database CA to verify client certificates presented by the agent. A local CA issues the node and administrative client certificates.

Keep running these commands on the CockroachDB server.

Create the system user and certificate directory:

Terminal

sudo useradd --system --home-dir /var/lib/cockroach --shell /sbin/nologin cockroach# No output on success.sudo install -d -o cockroach -g cockroach -m 0700 /var/lib/cockroach /var/lib/cockroach/certs# No output on success.

Download your cluster's public database CA. Replace almaforge.example.com with your cluster's hostname:

Terminal

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.

Create a local CA, a node certificate for localhost, and a root client certificate:

Terminal

sudo openssl req -quiet -x509 -newkey rsa:2048 -nodes -days 3650 -subj '/CN=CockroachDB local CA' \    -keyout /var/lib/cockroach/certs/ca.key -out /var/lib/cockroach/certs/ca.crtprintf 'subjectAltName=DNS:localhost,IP:127.0.0.1\nextendedKeyUsage=serverAuth,clientAuth\n' > /tmp/node.extsudo openssl req -quiet -new -newkey rsa:2048 -nodes -subj '/CN=node' \    -keyout /var/lib/cockroach/certs/node.key -out /tmp/node.csrsudo openssl x509 -req -days 3650 -in /tmp/node.csr -CA /var/lib/cockroach/certs/ca.crt \    -CAkey /var/lib/cockroach/certs/ca.key -CAcreateserial -extfile /tmp/node.ext \    -out /var/lib/cockroach/certs/node.crt 2>/dev/nullprintf 'extendedKeyUsage=clientAuth\n' > /tmp/root.extsudo openssl req -quiet -new -newkey rsa:2048 -nodes -subj '/CN=root' \    -keyout /var/lib/cockroach/certs/client.root.key -out /tmp/root.csrsudo openssl x509 -req -days 3650 -in /tmp/root.csr -CA /var/lib/cockroach/certs/ca.crt \    -CAkey /var/lib/cockroach/certs/ca.key -CAcreateserial -extfile /tmp/root.ext \    -out /var/lib/cockroach/certs/client.root.crt 2>/dev/nullrm -f /tmp/node.ext /tmp/node.csr /tmp/root.ext /tmp/root.csr

Combine the database CA and the local CA into ca-client.crt, and secure permissions:

Terminal

sudo sh -c 'cat almaforge-db-ca.pem /var/lib/cockroach/certs/ca.crt > /var/lib/cockroach/certs/ca-client.crt'sudo chown -R cockroach:cockroach /var/lib/cockroach/certssudo chmod 0600 /var/lib/cockroach/certs/ca.key /var/lib/cockroach/certs/node.key /var/lib/cockroach/certs/client.root.key

Start CockroachDB with TLS

Install the CockroachDB systemd service:

cockroachdb.service:

/etc/systemd/system/cockroachdb.service

# Run a single-node CockroachDB server with its node and client certificates.
[Unit]
Description=CockroachDB single node
After=network-online.target
Wants=network-online.target

[Service]
Type=notify
User=cockroach
Environment=COCKROACH_SKIP_ENABLING_DIAGNOSTIC_REPORTING=true
ExecStart=/usr/local/bin/cockroach start-single-node \
  --certs-dir=/var/lib/cockroach/certs --store=/var/lib/cockroach/data \
  --listen-addr=localhost:26257 --http-addr=localhost:8090
Restart=always
LimitNOFILE=65536

[Install]
WantedBy=multi-user.target

Reload systemd, enable the service, and start CockroachDB:

Terminal

sudo systemctl daemon-reloadsudo systemctl enable --now cockroachdbsystemctl is-active cockroachdbactive

Create the sample database, account, and table:

Terminal

sudo cockroach sql --user=root --certs-dir=/var/lib/cockroach/certs --host=localhost:26257 <<'EOF'CREATE DATABASE IF NOT EXISTS demo;CREATE USER IF NOT EXISTS demo;GRANT ALL ON DATABASE demo TO demo;CREATE TABLE IF NOT EXISTS demo.demo (id INT PRIMARY KEY, name STRING);INSERT INTO demo.demo VALUES (1, 'alice'), (2, 'bob'), (3, 'carol') ON CONFLICT DO NOTHING;GRANT ALL ON TABLE demo.demo TO demo;EOF

Register CockroachDB with the agent

Still on the CockroachDB server, display the local CA certificate:

Terminal

sudo cat /var/lib/cockroach/certs/ca.crt-----BEGIN CERTIFICATE-----...-----END CERTIFICATE-----

Copy the entire certificate, including the BEGIN and END lines. The agent uses it to verify CockroachDB. Then open the target configuration:

Terminal

sudo install -d -o root -g root -m 0700 /etc/almaforge/almad.d# No output on success.sudoedit /etc/almaforge/almad.d/cockroachdb.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: |:

target.yaml.in:

/etc/almaforge/almad.d/cockroachdb.yaml

# Connect the local CockroachDB instance through AlmaForge.
# Replace the placeholder with the contents of /var/lib/cockroach/certs/ca.crt.
apiVersion: almaforge.com/v1
kind: DatabaseTarget
metadata:
  name: cockroachdb
  labels:
    db: cockroachdb
    env: prod
spec:
  protocol: cockroachdb
  uri: localhost:26257
  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:

Terminal

sudo chown root:root /etc/almaforge/almad.d/cockroachdb.yaml# No output on success.sudo chmod 0600 /etc/almaforge/almad.d/cockroachdb.yaml# No output on success.

Start and check the agent

Enable the services at boot and start the agent:

Terminal

sudo systemctl enable cockroachdb almad...sudo systemctl restart almad# No output on success.systemctl is-active cockroachdb almadactiveactive

Both services must report active. If either fails, inspect its log:

Terminal

sudo journalctl -u cockroachdb -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/cockroachdb.yaml.

Step 3. Grant access

On your computer, confirm that the agent has registered the database:

Terminal

alma get dbHost             Name            Protocol       URI                 Labels             Version---------------- --------------- -------------- ------------------- ------------------ --------<database-host>  cockroachdb     cockroachdb    <database-uri>      db=cockroachdb ... 2026.1.0

Check that cockroachdb appears with protocol cockroachdb and your database server in the Host column. If either is missing, see Database is missing or has no host.

Download cockroachdb-access.yaml and save it locally. This role allows the demo SQL account on targets labeled db: cockroachdb, authorizing the demo account configured in Step 2. CockroachDB grants determine which databases and tables the account can use.

examples/databases/cockroachdb/roles/cockroachdb-access.yaml

# Allow access to the dedicated CockroachDB target.
# CockroachDB grants control access to databases and tables.
apiVersion: almaforge.com/v1
kind: Role
metadata:
  name: cockroachdb-access
spec:
  allow:
    databaseLabels:
      db: cockroachdb
    databaseUsers:
      - demo

Apply the role:

Terminal

alma apply -f cockroachdb-access.yamlrole 'cockroachdb-access' has been created

Creating the role does not assign it to anyone. To grant it to an SSO group, list your connectors:

Terminal

alma get oidcKind          Name------------- -----------OIDCConnector company-sso

Find the connector your team uses to sign in. Replace <connector-name> with its name from the output, then open it in your text editor:

Terminal

alma edit oidc/<connector-name># Your text editor opens. After saving your changes:oidc connector "<connector-name>" has been updated

Under spec.claimsToRoles, find the entry for the group that should have CockroachDB access. Add cockroachdb-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:

Terminal

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:       [email protected]  Cluster:            almaforge.example.com  Roles:              cockroachdb-access  ...alma status> Profile URL:        https://almaforge.example.com:443  Logged in as:       [email protected]  Cluster:            almaforge.example.com  Roles:              cockroachdb-access  ...

Check that Roles includes cockroachdb-access. If it is missing, check your SSO group membership and the mapping from Step 3 before continuing.

Connect to the sample data:

Terminal

alma db connect --db-user=demo --db-name=demo cockroachdb...demo=> SELECT current_user, current_database(); current_user | current_database--------------+------------------ demo         | demo(1 row)
demo=> SELECT count(*) FROM demo; count-------     3(1 row)

The results must show user demo, database demo, and three sample rows. These confirm certificate authentication and access to the sample data. Type \q to leave psql.

Advanced configuration

GUI clients and scripts

Leave this command running while your client uses the connection:

Terminal

alma proxy db --tunnel --db-user=demo --db-name=demo cockroachdbStarted authenticated tunnel for the CockroachDB database "cockroachdb" in cluster "almaforge.example.com" on 127.0.0.1:54321....

Use the printed local address and port in your client, with user demo and database demo. Leave the password empty and disable SSL for this local connection. The tunnel authenticates you to AlmaForge, and the agent uses a client certificate to connect to CockroachDB.

Architecture

The database agent runs beside CockroachDB and opens an outbound tunnel to the AlmaForge Proxy. CockroachDB listens on localhost and trusts the agent's client certificates.

Troubleshooting

Database is missing or has no host

On the database server, check that /etc/almaforge/almad.d/cockroachdb.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:

Terminal

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 cockroachdb-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.

Database user is rejected

Connect with --db-user=demo. The setup creates this SQL user explicitly. The AlmaForge role must allow demo, and the database must trust the AlmaForge database CA in ca-client.crt.

Local administrator certificates stop working

Keep the local CA in ca-client.crt alongside the AlmaForge database CA, preserving the local root certificate's trust.

Certificate validation fails

The target's spec.tls.caCert must contain the local ca.crt that signed node.crt. The node certificate covers localhost, which must match the host in the target URI.

Limitations

• In this release, AlmaForge does not provision CockroachDB users. Create the SQL account before connecting.
• The role's databaseNames does not restrict CockroachDB access. Use CockroachDB grants to control access to databases and tables, including queries that name another database.

References

• Database access overview: architecture and supported databases.
• RBAC reference: database access fields on a role.
• Declarative configuration: the DatabaseTarget schema.