PostgreSQL Automatic User Provisioning

Teleport can automatically create users in your database, removing the need for creating individual user accounts in advance or using the same set of shared database accounts for all users.

Prerequisites

• Teleport cluster v13.1 or above with a configured self-hosted PostgreSQL or RDS PostgreSQL database.
• Ability to connect to and create user accounts in the target database.

Step 1/3. Configure database admin

Teleport should be able to connect to the database as a user that can create other users and assign them roles. We recommend creating a separate user designated specifically for Teleport automatic user provisioning. Let's call it teleport-admin.

Teleport will use the same authentication mechanism when connecting as an admin user as for regular user connections: X.509 for self-hosted databases and AWS IAM for RDS. The admin user must have privileges within the database to create users and grant them privileges.

CREATE USER "teleport-admin" login createrole;
GRANT rds_iam TO "teleport-admin";

CREATE USER "teleport-admin" login createrole;

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

Users created by Teleport will be placed in the teleport-auto-user group in the database, which will be created automatically if it doesn't exist.

Next, enable the database admin on the Teleport database configuration:

db_service:
  enabled: "yes"
  databases:
  - name: "example"
    protocol: "postgres"
    uri: "localhost:5432"
    admin_user:
      name: "teleport-admin"
      # Optional default database the admin user logs into. Default is
      # the same database that the user is accessing, if not specified.
      # default_database: teleport

kind: db
version: v3
metadata:
  name: example
spec:
  protocol: "postgres"
  uri: "localhost:5432"
  admin_user:
    name: "teleport-admin"
    # Optional default database the admin user logs into. Default is
    # the same database that the user is accessing, if not specified.
    # default_database: teleport

GRANT CREATE ON SCHEMA public TO "teleport-admin";

Step 2/3. Configure Teleport role

To specify the database roles a user should be assigned within the database, use the db_roles role option:

kind: role
version: v7
metadata:
  name: auto-db-users
spec:
  options:
    # create_db_user_mode enables automatic user provisioning for matching databases
    create_db_user_mode: keep
  allow:
    db_labels:
      "*": "*"
    db_names:
    - "*"
    # db_roles is a list of roles the database user will be assigned
    db_roles:
    - reader
    - "{{internal.db_roles}}"
    - "{{external.db_roles}}"

With automatic user provisioning, users always connect to the database with their Teleport username so the db_users role field is ignored for roles that have database user provisioning enabled.

The available provisioning modes are:

• off: Disables user provisioning.

• keep: Enables user provisioning and disables users at session end. The user will be stripped of all roles and the user account will be locked.

• best_effort_drop: Enables user provisioning and tries to drop user at session end. If the drop fails, fallback to disabling them (same as keep mode).

Users created within the database will:

• Have the same name as Teleport username.
• Be a part of the teleport-auto-user role.
• Be assigned all roles from the Teleport user's role set that match the database. The role names must be valid and exist in the database. See PostgreSQL CREATE ROLE for information on how to create database roles.

Note that in case of a name conflict where a user with the same name already exists in the database and is not managed by Teleport (i.e. not assigned the teleport-auto-user role), the connection will be aborted.

Step 3/3. Connect to the database

Now, log into your Teleport cluster and connect to the database:

tsh login --proxy=teleport.example.com
tsh db connect --db-name <database> example

Troubleshooting

Permission denied for schema public error

PostgreSQL 15 revokes the CREATE permission from all users except a database owner from the public (or default) schema.

Grant the admin user CREATE privilege so the admin user can create procedures:

GRANT CREATE ON SCHEMA public TO "teleport-admin";

If admin_user.default_database is specified, the CREATE privilege is only required for the database specified in the default_database. Otherwise, you have to repeat the privilege grant for every database Teleport will access.

Next steps

• Connect using your GUI database client.
• Learn about role templating.
• Read automatic user provisioning RFD.
• The internal.db_roles traits we illustrated in this guide are replaced with values from the Teleport local user database. For full details on how variable expansion works in Teleport roles, see the Teleport Access Controls Reference.