Teleport PostgreSQL Backend using client cert authentation

[stevenGravy]

Here is a set of steps to enable the PostgreSQL backend with client cert connections from Teleport. Below uses a self-signed CA which should be adaptable. This installs postgresql and Teleport within the same host for simplicity.

NOTE: An important consideration here is this uses Subject Alternate Name (SAN) that is required to use the verify-full setting. Most examples on creating and using client certs with PostgreSQL do not show this. You may have to use verify-ca mode if your certs do not have SAN settings.

Please see the documentation on configuring in a Azure Cloud environment. This is meant for standalone environments not using Cloud backends.

Pre-requisites:

• Teleport 13.3+
• Linux host for Postgresql and Teleport with administrative access
• Assumes open port 443 for using Let's Encrypt.
• S3-compatible storage. You can follow this guide to setup minio.

Install and configure Postgresql

Host type: Ubuntu 22.04

Install PostgreSQL

sudo apt update
# install postgresql and required wal2json logical decoding plugin
sudo apt install postgresql postgresql-14-wal2json -y

On Aug 9, 2023 this installed PostgreSQL 14 which meets the 13+ requirement.

Configure WAL

In /etc/postgresql/14/main/postgresql.conf set the required settings.

# the default value for wal_level is replica
wal_level = logical

# the default value for max_replication_slots is 10
max_replication_slots = 10

Create teleport user

sudo su postgres

CREATE USER teleport WITH REPLICATION CREATEDB;

Create CA and Client certs

To connect via client certs we are going to create and install a CA. We will then issue client certs for the teleport user. Note a major difference here is the specification of subject alternate name which is required for verify all usage.

# perform as postgres user if not still that user
sudo su postgres

# Change to the postgresql directory
cd /etc/postgresql/14/main
SERVER=localhost

MY_USER_NAME_FOR_CERT=teleport

CORPORATION=My-Corp
GROUP=My-Corporate-Group
CITY=City
STATE=State
COUNTRY=US


mkdir keys certs
chmod og-rwx keys certs

# Set up a directory that will serve as the pgconf mount
mkdir pgconf

# Create a key-pair that will serve both as the root CA and the server key-pair
# the "ca.crt" name is used to match what it expects later
openssl req -new -x509 -days 365 -nodes -out certs/ca.crt \
  -keyout keys/ca.key -subj "/CN=root-ca"
cp certs/ca.crt pgconf/ca.crt

# Create the server key and CSR and sign with root key
openssl req -new -nodes -out server.csr \
  -keyout pgconf/server.key -subj "/CN=${SERVER}" \
  -config <(cat /etc/ssl/openssl.cnf <(printf "[SAN]\nsubjectAltName=DNS:$SERVER"))

openssl x509 -req -in server.csr -days 365 \
    -CA certs/ca.crt -CAkey keys/ca.key -CAcreateserial \
    -out pgconf/server.crt \
  -extfile <(cat /etc/ssl/openssl.cnf <(printf "[SAN]\nsubjectAltName=DNS:$SERVER")) \
  -extensions SAN

# remove the CSR as it is no longer needed
rm server.csr

# Create client certs for teleport user

openssl req -new -nodes -out client.csr   -keyout keys/client.key -subj "/CN=${MY_USER_NAME_FOR_CERT}" \
  -config <(cat /etc/ssl/openssl.cnf <(printf "[SAN]\nsubjectAltName=DNS:$SERVER"))

openssl x509 -req -in client.csr -days 365     -CA certs/ca.crt -CAkey keys/ca.key -CAcreateserial  \
    -out certs/client.crt

# remove the client CSR
rm client.csr

Configure TLS

In postgresql.conf set the SSL settings

# - SSL -

ssl = on
ssl_ca_file = '/etc/postgresql/14/main/pgconf/ca.crt'
ssl_cert_file = '/etc/postgresql/14/main/pgconf/server.crt'
ssl_key_file = '/etc/postgresql/14/main/pgconf/server.key'

In pg_hba.conf in the same directory set the allowed connections. This
allows local connections by the postgres user and certs by all other users.

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             postgres                                peer
# do not let the "postgres" superuser login via a certificate
hostssl all             postgres        ::/0                    reject
hostssl all             postgres        0.0.0.0/0               reject
#
hostssl all             all             ::/0                    cert
hostssl all             all             0.0.0.0/0               cert

Restart PostgreSQL

# exit postgres user back to your admin user or root
sudo service postgresql restart

To check the health of your postgresql use journalctl

journalctl -fu postgresql
journalctl -fu postgresql@14-main

Confirm client connection

The root user will be running Teleport so it should have the client certs in its postgres folder.

Copy the client certificates and root into the root user.

The root user will be connecting with the client certs when it runs Teleport to the postgres service.

sudo su -
mkdir .postgresql
cp /etc/postgresql/14/main/certs/ca.crt .postgresql/root.crt
cp /etc/postgresql/14/main/keys/client.key .postgresql/postgresql.key
cp /etc/postgresql/14/main/certs/client.crt .postgresql/postgresql.crt

Test connect

You now have the client certificates installed and should be able to connect
via client certs to the database.

sudo su -
psql -h localhost -U teleport postgres

psql (14.8 (Ubuntu 14.8-0ubuntu0.22.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=>

Install Teleport

Install Teleport and configure with your cluster address.

curl https://goteleport.com/static/install.sh | bash -s 13.3.2
# replace the email and clustername with your dns address
sudo teleport configure -o file \
    --acme [email protected] \
    --cluster-name=tele.example.com

Configure Teleport storage

Set the storage for the postgresql storage and sessions uri. in /etc/teleport.yaml Replace the minioserver address with the location of your server. You may want to put this on the same machine.

teleport:
  storage:
    type: postgresql

    conn_string: postgresql://teleport@localhost:5432/teleport_backend?sslmode=verify-full&pool_max_conns=20

    audit_events_uri:
      - 'postgresql://teleport@localhost:5432/teleport_audit?sslmode=verify-full'
    audit_sessions_uri: "s3://teleport?endpoint=http://minioserver:9000&insecure=true&disablesse=true&region=foo"

Confirm Backend storage

sudo su -
teleport start

In a separate terminal on the host create a user

sudo tctl users add --roles=auditor,editor,access steven --logins=root

As part of the output you should see for a postgres cluster state start.

2023-08-09T22:10:25Z INFO [PGBK]      Setting up backend. pgbk/pgbk.go:172

Viewing events in the db

You can connect to the postgresql databases to view Teleport audit entries.

root@ip-172-31-81-242:~# psql -h localhost -p 5432 -U teleport postgres
psql (14.8 (Ubuntu 14.8-0ubuntu0.22.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=> \connect teleport_audit
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
You are now connected to database "teleport_audit2" as user "teleport".
teleport_audit2=> \dt
             List of relations
 Schema |     Name      | Type  |  Owner   
--------+---------------+-------+----------
 public | audit_version | table | teleport
 public | events        | table | teleport
(2 rows)

teleport_audit=> select * from events;

[russjones]

If you want a more self contained Docker version, copy paste the below files and then run the docker command below.

postgresql.conf

listen_addresses = '*'
port = 5432
max_connections = 20
shared_buffers = 128MB
temp_buffers = 8MB
work_mem = 4MB

wal_level=logical
max_replication_slots=10

ssl=on
ssl_ca_file='/certs/ca.crt'
ssl_cert_file='/certs/server.crt'
ssl_key_file='/certs/server.key'

pg_hba.conf

# TYPE DATABASE USER CIDR-ADDRESS  METHOD
local   all             all                                     trust
hostssl all             all             ::/0                    cert
hostssl all             all             0.0.0.0/0               cert

init.sql

CREATE USER teleport WITH REPLICATION CREATEDB;

Dockerfile

FROM postgres:15.0
RUN apt-get update
RUN apt-get install -y postgresql-15-wal2json

Run the following Docker command to run Postgres fully configured to work with Teleport.

docker run --rm --name postgres \
    -e POSTGRES_DB=db \
    -e POSTGRES_USER=user \
    -e POSTGRES_PASSWORD=password \
    -v `pwd`/data:/var/lib/postgresql/data \
    -v `pwd`/pgconf/ca.crt:/certs/ca.crt:ro \
    -v `pwd`/pgconf/server.crt:/certs/server.crt:ro \
    -v `pwd`/pgconf/server.key:/certs/server.key:ro \
    -v `pwd`/postgresql.conf:/etc/postgresql/postgresql.conf \
    -v `pwd`/pg_hba.conf:/etc/postgresql/pg_hba.conf \
    -v `pwd`/init.sql:/docker-entrypoint-initdb.d/init.sql \
    -p 5432:5432 \
    $(shell docker build -q .) \
    postgres \
    -c hba_file=/etc/postgresql/pg_hba.conf \
    -c config_file=/etc/postgresql/postgresql.conf

[Pr0t0c01]

Hello @stevenGravy, I'm applying your config for teleport with postgresql backend storage. For high availability, my idea is that to setup 2 teleport nodes with postgresql and to replicate data between two nodes. Can it be possible and how can i make that approach?

[stevenGravy]

High availability would be handled by the Postgres setup. Please see their documentation for option.