Enroll a Self-Hosted PostgreSQL into Teleport CE

By /

This post shows how to enroll a self-hosted PostgreSQL (Ubuntu 24.04, PostgreSQL 16) into an existing Teleport Community Edition cluster using Teleport’s Web UI PostgreSQL wizard, while keeping PostgreSQL’s server TLS certificate managed by Step CA (ACME v2) + Certbot.

Key idea (two trust directions):

  • Teleport → PostgreSQL (server identity): Teleport Database Service must trust the CA that signed the PostgreSQL server certificate (Step CA Root/Intermediate).
  • PostgreSQL → Teleport (client identity): PostgreSQL must trust Teleport’s DB client CA so it can verify the short-lived client certificates presented by Teleport during tsh db connect.

This Post Is Based On

Enroll an Ubuntu 24.04 Server as a Teleport SSH Resource (Community Edition, Web UI)
Use Step CA as an ACME Server (ACME v2) and Issue/Renew TLS Certificates with Certbot + NGINX

Design Goals

  • Teleport is internal-only (LAN/VPN), UI at: https://teleport.maksonlee.com
  • PostgreSQL host: artifactory.maksonlee.com
  • PostgreSQL server TLS is issued + auto-renewed by Step CA + Certbot
  • Human DB access is enforced via Teleport Database Access (short-lived client certs + audit)

Lab Context

  • OS: Ubuntu 24.04 (noble)
  • Teleport Proxy: teleport.maksonlee.com:443
  • Step CA: https://stepca.maksonlee.com
  • PostgreSQL: 16 (/etc/postgresql/16/main)
  • PostgreSQL hostname: artifactory.maksonlee.com

What You’ll Build

  • A Teleport Database resource for PostgreSQL
  • PostgreSQL server certificate issued by Step CA via ACME + Certbot (standalone)
  • PostgreSQL client certificate authentication that trusts Teleport DB client CA (mTLS)
  • A db Teleport role (wildcard for quick testing)
  • Test access using an existing Teleport user

Prerequisites

  • Step CA ACME provisioner already enabled (ACME directory reachable)
  • artifactory.maksonlee.com resolves correctly in your internal DNS
  • During Certbot issuance/renewal (standalone / HTTP-01), TCP/80 must be reachable on the PostgreSQL host
  • You have a Teleport admin user to run tctl on the Teleport server and to complete the Web UI wizard
  • A client machine has tsh and psql installed for testing
  1. Install packages on the PostgreSQL host

On artifactory.maksonlee.com:

sudo apt update
sudo apt install -y certbot curl ca-certificates openssl
  1. Trust your private PKI (Step CA Root) on the PostgreSQL host

Download Step CA roots and install into the OS trust store:

# Note: -k is only for first bootstrap (when the host does not yet trust Step CA).
sudo curl -kfsS https://stepca.maksonlee.com/roots.pem -o /tmp/stepca-roots.pem
openssl x509 -in /tmp/stepca-roots.pem -noout -subject -issuer -fingerprint -sha256

sudo install -m 0644 /tmp/stepca-roots.pem \
  /usr/local/share/ca-certificates/stepca-root-ca.crt

sudo update-ca-certificates
  1. Issue a PostgreSQL server certificate from Step CA (ACME v2) using Certbot standalone

Run on artifactory.maksonlee.com:

sudo certbot certonly --standalone -n \
  -d artifactory.maksonlee.com \
  --cert-name postgres-internal \
  --server https://stepca.maksonlee.com/acme/acme-internal/directory \
  --agree-tos --email admin@maksonlee.com

Expected output paths:

  • /etc/letsencrypt/live/postgres-internal/fullchain.pem
  • /etc/letsencrypt/live/postgres-internal/privkey.pem

Note (short-lived server cert): In this lab, the PostgreSQL server certificate is intentionally short-lived (48 hours). The lifetime is enforced by Step CA (ACME provisioner policy). Certbot will renew it automatically (we run renew daily), so the server certificate stays fresh without manual work.

(Optional) Verify the current certificate validity (start/end):

sudo openssl x509 -in /etc/letsencrypt/live/postgres-internal/fullchain.pem -noout -startdate -enddate

Example output:

notBefore=Jan 30 10:18:21 2026 GMT
notAfter=Feb  1 10:19:21 2026 GMT
  1. Put the Step CA–issued PostgreSQL server certificate/key where PostgreSQL can safely read them

PostgreSQL requires tight permissions on private keys.

Copy cert + key as postgres-owned files:

sudo install -o postgres -g postgres -m 0644 \
  /etc/letsencrypt/live/postgres-internal/fullchain.pem \
  /var/lib/postgresql/16/main/server.crt

sudo install -o postgres -g postgres -m 0600 \
  /etc/letsencrypt/live/postgres-internal/privkey.pem \
  /var/lib/postgresql/16/main/server.key
  1. Create a DB access Role (wildcard, for quick testing) BEFORE running the UI wizard

Teleport’s PostgreSQL wizard may temporarily add db_users / db_names traits to the user running the wizard so it can complete the “Test Connection” step.

To keep the flow clean, create the wildcard db role first and assign it to the setup user (e.g. teleport-admin) before you run the wizard.

On the Teleport server, create db.yaml:

cat > db.yaml <<'EOF'
kind: role
version: v8
metadata:
  name: db
spec:
  allow:
    db_labels:
      "*": "*"
    db_users: ["*"]
    db_names: ["*"]
EOF

Apply it:

sudo tctl create -f db.yaml

Assign it in the Web UI:

  • Go to Management → Users
  • Click your setup user (example: teleport-admin)
  • Edit Roles
  • Add role: db
  • Save

(You can also assign it to your final test user teleport-test now, or later.)

  1. Teleport Web UI: Register a Database (wizard Step 1)

In the Web UI, go to:

Add New → PostgreSQL

Register a Database

  • Database Name: artifactory
  • Database Connection Endpoint: artifactory.maksonlee.com
  • Endpoint Port: 5432
  • Labels: optional

Click Next.

  1. Teleport Web UI: Deploy Database Service (wizard Step 2)

The wizard shows “Manually Deploy a Database Service” and generates a command like:

sudo bash -c "$(curl -fsSL https://teleport.maksonlee.com/scripts/<id>/install-database.sh)"

Run the exact command shown in your UI on artifactory.maksonlee.com.

Verify the service:

sudo systemctl status teleport --no-pager
sudo cat /etc/teleport.yaml

Once the UI detects the Database Service, click Next.

  1. Teleport Web UI: Configure Mutual TLS (wizard Step 3)

This step configures mTLS between Teleport Database Service and PostgreSQL:

  • Teleport issues short-lived client certificates for humans (via tsh)
  • PostgreSQL must trust Teleport’s DB client CA
  • Teleport Database Service must trust Step CA to verify the PostgreSQL server cert

Run the wizard’s “download CA + generate cert/key” command

On artifactory.maksonlee.com, run the exact curl ... /sign/db ... command shown in the UI.

This generates a bundle including:

  • server.cas (CA bundle for verifying Teleport DB client certs)
  • server.crt / server.key (Teleport-signed server cert/key)

We keep Step CA for PostgreSQL’s server identity, so we only need server.cas from this output.

Note on $PGDATA: The wizard uses $PGDATA as a placeholder for PostgreSQL’s data directory (data_directory). On Ubuntu (PostgreSQL 16 packages), this is typically /var/lib/postgresql/16/main. Verify the exact path on your host with:

sudo -u postgres psql -tAc "show data_directory;"

Install server.cas for PostgreSQL to use:

sudo install -o postgres -g postgres -m 0644 server.cas \
  /var/lib/postgresql/16/main/server.cas

Add a copy of your DB CA certificate (Step CA) in the UI

In the UI text box “Add a copy of your CA certificate”, paste the Step CA Root (and intermediate if you use one) in PEM format:

-----BEGIN CERTIFICATE-----
...
-----END CERTIFICATE-----

This allows Teleport Database Service to trust and verify the PostgreSQL server certificate chain.

Click Next.

  1. Update postgresql.conf for TLS

If you are still using the default Ubuntu “snakeoil” certificate paths, they look like:

ssl = on
ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'
ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'

Update it to:

  • Use the Step CA-issued server cert/key (from Step 4)
  • Use Teleport’s client CA bundle server.cas (from Step 8)

Edit:

sudo -u postgres vi /etc/postgresql/16/main/postgresql.conf

Set:

ssl = on

# Step CA-issued server identity
ssl_cert_file = '/var/lib/postgresql/16/main/server.crt'
ssl_key_file  = '/var/lib/postgresql/16/main/server.key'

# Trust Teleport DB client CA for client-certificate auth
ssl_ca_file   = '/var/lib/postgresql/16/main/server.cas'

Restart PostgreSQL:

sudo systemctl restart postgresql
  1. Teleport Web UI: Set Up Access (wizard Step 4)

This step defines which DB users (--db-user) and DB names (--db-name) Teleport will allow for database access.

  • Database Users: allowed DB usernames (e.g. artifactory, postgres). * means any DB user.
  • Database Names: allowed PostgreSQL database names (e.g. artifactory, postgres). * means any DB name.

For quick testing, set both to *.

Update pg_hba.conf (keep password logins; add the UI rules as-is)

The UI shows these pg_hba.conf rules:

hostssl all  all  ::/0        cert
hostssl all  all  0.0.0.0/0   cert

In this lab, password logins must remain enabled (for the application and break-glass access).
To avoid breaking existing password-based connections, keep your current scram-sha-256 rules and append the UI rules at the end of /etc/postgresql/16/main/pg_hba.conf.

Important (first-match wins): pg_hba.conf is evaluated top-to-bottom.
If you place the broad ::/0 and 0.0.0.0/0 cert rules above your password rules, PostgreSQL will require client certificates for all TLS connections and password logins may stop working.

Note: Appending the UI rules at the end preserves password logins, but it also means those cert rules may not be enforced if an earlier password rule matches first. In practice, certificate-based access can still be achieved by not distributing database passwords and requiring users to connect via Teleport.

Edit:

sudo -u postgres vi /etc/postgresql/16/main/pg_hba.conf

Reload PostgreSQL:

sudo systemctl reload postgresql

Click Next.

  1. Teleport Web UI: Test Connection (wizard Step 5)

In the UI, pick a database user and database name to test (or keep the defaults) and run the test.

You should see checks like:

  • A Teleport Database Service is available to proxy the connection
  • Database is accessible from the Teleport Database Service
  • Access to Database User and Database Name granted
  • Database User exists in the Database
  • Database Name exists in the Database
  1. Test database access via Teleport from a client machine

Login as your test user (example: teleport-test):

tsh login --proxy=teleport.maksonlee.com --user=teleport-test

Connect (example):

tsh db connect artifactory --db-user=artifactory --db-name=artifactory

You should land in psql without typing a database password (Teleport is using short-lived client certificates).

  1. Auto-renew: reload PostgreSQL after Certbot renews the Step CA-issued cert

If the host has multiple Certbot-managed certificates (e.g., one from Let’s Encrypt and one from Step CA), make the deploy hook run only when the PostgreSQL certificate (postgres-internal) is renewed. Certbot exposes the renewed certificate path via the RENEWED_LINEAGE environment variable.

Create a Certbot deploy hook on the PostgreSQL host:

sudo tee /etc/letsencrypt/renewal-hooks/deploy/90-postgres.sh >/dev/null <<'EOF'
#!/usr/bin/env bash
set -euo pipefail

# Only act on the PostgreSQL cert (cert-name: postgres-internal)
if [[ "${RENEWED_LINEAGE:-}" != "/etc/letsencrypt/live/postgres-internal" ]]; then
  exit 0
fi

install -o postgres -g postgres -m 0644 \
  "$RENEWED_LINEAGE/fullchain.pem" \
  /var/lib/postgresql/16/main/server.crt

install -o postgres -g postgres -m 0600 \
  "$RENEWED_LINEAGE/privkey.pem" \
  /var/lib/postgresql/16/main/server.key

systemctl reload postgresql
EOF

sudo chmod +x /etc/letsencrypt/renewal-hooks/deploy/90-postgres.sh

Dry-run renewal (test only this Step CA-managed certificate and force the Step CA ACME directory):

sudo certbot renew --cert-name postgres-internal --dry-run \
  --server https://stepca.maksonlee.com/acme/acme-internal/directory

Summary

You now have:

  • PostgreSQL server TLS issued/renewed by Step CA (ACME v2) + Certbot standalone
  • PostgreSQL configured to trust Teleport DB client CA (server.cas) for client certificate authentication
  • Teleport Database Service deployed using the Web UI wizard
  • A wildcard db role for testing, assigned to the setup user and/or test user
  • Human DB access via tsh db connect with audit and centralized access control

Did this guide save you time?

Support this site

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top