Skip to main content
Version: 14.x

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​

RDS compatibility

Automatic user provisioning is not compatible with RDS Aurora reader endpoints.

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.

The RDS PostgreSQL admin user must have the rds_iam role attached to allow IAM authentication:

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

Note that the RDS database must have IAM authentication enabled.

Refer to the AWS documentation to make sure you are using the rds_iam role correctly. for more information.

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, configure the database admin user in 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
Auto-discovered databases

For auto-discovered cloud databases, the name of the admin user is taken from the teleport.dev/db-admin label, and the default database is taken from the teleport.dev/db-admin-default-database label.

Procedure Privileges in PostgreSQL 15+

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.

Step 2/3. Configure a 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, when the session ends, drops the user if no resources depend on it. In cases where any resource depends on the user, it falls back to disabling the user, mirroring the behavior of keep mode.

Users created within the database will:

  • Have the same username as the authenticated Teleport user.
  • 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
Database Username

When connecting to a database with user provisioning enabled, the Database Service expects your Teleport username will be used as the database username .

If using a GUI database client like pgAdmin, make sure to use your Teleport username as the database username. tsh db connect will default to your Teleport username automatically when connecting to a database with user provisioning enabled.

When connecting to a leaf cluster database with user provisioning enabled, the Database Service expects the database username to be remote-<your-teleport-username>-<root-cluster-name>.

To view the list of database roles that are allowed for each database, you can use the command tsh db ls -v. By default, all database roles will be assigned to your auto-provisioned database user. You can optionally select a subset of the database roles with --db-roles:

$ tsh db connect --db-name <database> --db-roles reader 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.

User does not have CONNECT privilege error​

You may encounter the following error when the admin user or the roles assigned to the auto-provisioned user do not have permission to connect to the target database:

$ tsh db connect --db-name <database> example
...
FATAL: permission denied for database "<database>"
DETAIL: User does not have CONNECT privilege.

Make sure CONNECT is granted to the admin user and the respective roles:

GRANT CONNECT ON DATABASE <database> to "teleport-admin";
GRANT CONNECT ON DATABASE <database> to "reader";

Cannot execute in a read-only transaction error​

You may encounter the following error when connecting to an AWS RDS Aurora reader endpoint:

$ tsh db connect --db-name <database> example
psql: error: connection to server at "localhost" (::1), port 12345 failed: Connection refused
...
ERROR: cannot execute CREATE ROLE in a read-only transaction (SQLSTATE 25006)

Database auto-user provisioning is not compatible with RDS Aurora reader endpoints. Please use auto-user provisioning on the primary endpoints.

No schema has been selected error​

You may encounter the following error when connecting if the admin user does not have USAGE permission on the schema:

$ tsh db connect --db-name <database> example
psql: error: connection to server at "localhost" (::1), port 12345 failed: Connection refused
...
ERROR: no schema has been selected to create in (SQLSTATE 3F000)

To fix this, make sure that the admin user is granted USAGE and CREATE on schema public in the target database:

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

Permission denied to grant role "rds_iam"​

You may encounter the following error when connecting to an RDS database:

$ tsh db connect --db-name <database> example
psql: error: connection to server at "localhost" (::1), port 12345 failed: Connection refused
...
ERROR: permission denied to grant role "rds_iam" (SQLSTATE 42501)

This happens when the admin user does not have permission to grant the "rds_iam" role to other users. To fix this, grant the "rds_iam" role with the ADMIN option to the admin user:

GRANT rds_iam TO "teleport-admin" WITH ADMIN OPTION;

Use your mapped remote username error​

You may encounter the following error when connecting to a database in a remote cluster:

> tsh db connect --db-name <database> example
ERROR: please use your mapped remote username ("remote-<your-teleport-username>-<root-cluster-name>") to connect instead of "<database-user>"

When you access resources in a remote cluster, the remote cluster will receive the name remote-<your-teleport-username>-<root-cluster-name> from the local cluster. This is to prevent any naming collisions with users in the remote cluster. Please use the username from the error message as the database username for when connecting through tsh or GUI clients.

Next steps​