Self-Hosted PostgreSQL

Create certificate/key pair

Teleport uses mutual TLS authentication with self-hosted databases. As such, they must be configured with Teleport's certificate authority to be able to verify client certificates and a certificate/key pair that Teleport can verify.

Create the secrets:

Export Teleport's certificate authority and generate certificate/key pair
for host db.example.com with a 1-year validity period.
tctl auth sign --format=db --host=db.example.com --out=server --ttl=2190h

The command will create 3 files: server.cas, server.crt and server.key which you'll need to enable mutual TLS on your PostgreSQL server.

Configure PostgreSQL server

To configure PostgreSQL server to accept TLS connections, add the following to PostgreSQL configuration file postgresql.conf:

ssl = on
ssl_cert_file = '/path/to/server.crt'
ssl_key_file = '/path/to/server.key'
ssl_ca_file = '/path/toa/server.cas'

See Secure TCP/IP Connections with SSL in PostgreSQL documentation for more details.

Additionally, PostgreSQL should be configured to require client certificate authentication from clients connecting over TLS. This can be done by adding the following entries to PostgreSQL host-based authentication file pg_hba.conf:

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

You should also ensure that you have no higher-priority md5 authentication rules that will match, otherwise PostgreSQL will offer them first, and the certificate-based Teleport login will fail.

See The pg_hba.conf File in PostgreSQL documentation for more details.

Setup Teleport Auth and Proxy services

Teleport Database Access for PostgreSQL is available starting from 6.0 release.

Download the latest version of Teleport for your platform from our downloads page and follow the installation instructions.

Teleport requires a valid TLS certificate to operate and can fetch one automatically using Let's Encrypt ACME protocol. We will assume that you have configured DNS records for teleport.example.com and *.teleport.example.com to point to the Teleport node.

Generate Teleport config with ACME enabled:

$ teleport configure --cluster-name=teleport.example.com --acme [email protected] -o file

Start Teleport Auth and Proxy services:

$ sudo teleport start

Database service requires a valid auth token to connect to the cluster. Generate one and save it in /tmp/token:

$ tctl tokens add --type=db

Create role and user

Create the role that will allow a user to connect to any database using any database account:

tctl --config=/path/to/teleport.yaml create <<EOF
kind: role
version: v4
metadata:
  name: db
spec:
  allow:
    db_labels:
      '*': '*'
    db_names:
    - '*'
    db_users:
    - '*'
EOF

Create the user assigned the db role we've just created:

tctl --config=/path/to/teleport.yaml users add --roles=admin,db testuser

Start Database service with CLI flags

You can start Teleport database service without configuration file, using a CLI command:

teleport db start \   --token=/tmp/token \   --auth-server=teleport.example.com:3080 \   --name=test \   --protocol=postgres \   --uri=postgres.example.com:5432 \   --labels=env=dev

Note that the --auth-server flag must point to the Teleport cluster's proxy endpoint because database service always connects back to the cluster over a reverse tunnel.

Start Database service with config file

Below is an example of a database service configuration file that proxies a single self-hosted PostgreSQL database:

teleport:
  data_dir: /var/lib/teleport-db
  nodename: test
  # Proxy address to connect to. Note that it has to be the proxy address
  # because database service always connects to the cluster over reverse
  # tunnel.
  auth_servers:
  - teleport.example.com:3080
db_service:
  enabled: "yes"
  # This section contains definitions of all databases proxied by this
  # service, can contain multiple items.
  databases:
    # Name of the database proxy instance, used to reference in CLI.
  - name: "example"
    # Free-form description of the database proxy instance.
    description: "Example PostgreSQL"
    # Database protocol.
    protocol: "postgres"
    # Database address, PostgreSQL server endpoint in this case.
    #
    # Note: this URI's hostname must match the host name specified via --host
    # flag to tctl auth sign command.
    uri: "postgres.example.com:5432"
    # Labels to assign to the database, used in RBAC.
    static_labels:
      env: dev
auth_service:
  enabled: "no"
ssh_service:
  enabled: "no"
proxy_service:
  enabled: "no"

Start the database service:

teleport start --config=/path/to/teleport-db.yaml --token=/tmp/token

Connect

Once the database service has joined the cluster, login to see the available databases:

tsh login --proxy=teleport.example.com --user=testuser
tsh db ls
Name    Description        Labels
------- ------------------ --------
example Example PostgreSQL env=dev

Note that you will only be able to see databases your role has access to. See RBAC section for more details.

To connect to a particular database server, first retrieve credentials from Teleport using tsh db login command:

tsh db login example

You can optionally specify the database name and the user to use by default when connecting to the database instance:

tsh db login --db-user=postgres --db-name=postgres example

Once logged in, connect to the database:

tsh db connect aurora

If you would like to see the native psql shell connect command, run:

tsh db config --format=cmd aurora

To log out of the database and remove credentials:

Remove credentials for a particular database instance.
tsh db logout example
Remove credentials for all database instances.
tsh db logout