Teleport with PostgreSQL

[evrynet1]

Sorry guys for keep bugging you, you are utmost help so far. I'm trying to configure PostgreSQL backend for Teleport and later a cluster for HA here I'm using Steven guide:

#30247

except that for testing purposes I'm using user/pass auth and the db name is also set there ("postgre")

The PostgreSQL is an off-site managed database and I adjusted the teleport.yaml as follows:

version: v3
teleport:
  storage:
    type: postgresql

    # conn_string is a libpq-compatible connection string (see
    # https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING);
    # pool_max_conns is an additional parameter that determines the maximum
    # number of connections in the connection pool used for the cluster state
    # database (the change feed uses an additional connection), defaulting to
    # a value that depends on the number of available CPUs.
    #
    # If your certificates are not stored at the default ~/.postgresql
    # location, you will need to specify them with the sslcert, sslkey, and
    # sslrootcert parameters.
    conn_string: postgresql://username:[email protected]/db-name?teleport_backend?sslmode=verify-full&pool_max_conns=20
    audit_events_uri:
      - postgresql://username:[email protected]/db-name?teleport_audit?sslmode=verify-full
    audit_sessions_uri: file://var/lib/teleport/log/records
  nodename: ny.domain.com
  data_dir: /var/lib/teleport
  log:
    output: stderr
    severity: INFO
    format:
      output: text
  ca_pin: ""
  diag_addr: ""
auth_service:
  enabled: "yes"
  listen_addr: 0.0.0.0:3025
  cluster_name: ny.domain.com
  proxy_listener_mode: multiplex
ssh_service:
  enabled: "yes"
  commands:
  - name: hostname
    command: [hostname]
    period: 1m0s
proxy_service:
  enabled: "yes"
  web_listen_addr: 0.0.0.0:443
  public_addr: ny.domain.com:443
  https_keypairs:
    - key_file: /etc/letsencrypt/live/ny.domain.com/privkey.pem
      cert_file: /etc/letsencrypt/live/ny.domain.com/fullchain.pem
  https_keypairs_reload_interval: 0s
  acme:
    enabled: "no"
    email: [email protected]

Unfortunately Teleport is not listening on auth ports or any ports thereafter although it starts without error:

Feb  3 19:45:13 ny systemd[1]: teleport.service: Succeeded.
Feb  3 19:45:13 ny systemd[1]: Stopped Teleport Service.
Feb  3 19:45:13 ny systemd[1]: Started Teleport Service.
Feb  3 19:45:14 ny teleport[58478]: 2024-02-03T19:45:14Z INFO             Starting Teleport v15.0.0 with a config file located at "/etc/teleport.yaml" common/teleport.go:636
Feb  3 19:45:14 ny teleport[58478]: 2024-02-03T19:45:14Z INFO [PGBK]      Setting up backend. pgbk/pgbk.go:193
I have 2 further questions after following https://goteleport.com/docs/reference/backends/:

1. What do I do wrong here?
2. Is there any guide for setting up the second node of a self-hosted Postgre cluster?
3. How do I import the data from an existing cluster to PostgreSQL? Is PostgreSQL compatible with tctl get export?

Thank you so much,
Andrew

[stevenGravy]

I'm not seeing any specific errors here. Please change to DEBUG for logging to give some issues.

In terms of migrating from another cluster you can get the certificate and other key information with tctl get all. Then use the bootstrap option to load that in the new cluster. If you take a look at the backup and restore https://goteleport.com/docs/management/operations/backup-restore/ you'll see what it does extract. Typically takes a few iterations to get everything if you don't already have it ready to set.

[evrynet1]

since it's hosted elsewhere I'm afraid I dont have access to that. Is the conn_string correct? as I also need to specify the database there to connect and in your guide it was not set (probably by default it's using "postgres" db but here it needs to use "postgre"):

postgresql://admin:[email protected]/postgre?teleport_backend?sslmode=verify-full&pool_max_conns=20

db: postgre
user: admin

[zmb3]

@evrynet1 the screencast you've shared above includes credentials to a publicly accessible Postgres server. I would rotate the credentials immediately and assume that it's been compromised.

[evrynet1]

@zmb3 yep, its an empty db. I changed the details already, thank you.

[evrynet1]

okay I figured it out I think. The problem is the database name set.

In your guide the command is without db name:

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

since the db name is also specified with "dbname?" like this it won't take that argument:

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

If I remove teleport_backend and teleport_audit then it connects to the db.

Is it possible to use PostgreSQL backend without replication_role? I have only have access to 1 db and 1 user:

Feb 4 08:10:51 ny teleport[97996]: Original Error: *pgconn.PgError ERROR: permission denied to use replication slots (SQLSTATE 42501)

[evrynet1]

In the guide I see this:

For convenience, Teleport will attempt to grant itself the initiating replication role attribute, to accommodate the ability of some managed services (such as Azure Database for PostgreSQL) to create superuser accounts through their API; this should only be leveraged if the entire PostgreSQL cluster is dedicated to Teleport.

Since this is a shared PostgreSQL cluster with only short live transactions how do change the settings in Teleport not to try to leverage replication role attributes?