# 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 with a configured [self-hosted PostgreSQL](https://goteleport.com/docs/enroll-resources/database-access/enrollment/self-hosted/postgres-self-hosted.md) or [RDS PostgreSQL](https://goteleport.com/docs/enroll-resources/database-access/enrollment/aws/rds/mysql-postgres-mariadb.md) database. To configure permissions for database objects like tables, your cluster must be on version v15.2 or above. - Ability to connect to and create user accounts in the target database. --- 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, either for roles or concrete database objects. **RDS PostgreSQL** 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](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/UsingWithRDS.IAMDBAuth.DBAccounts.html) to make sure you are using the `rds_iam` role correctly. for more information. If the admin user needs to grant the `rds_superuser` role to auto-provisioned users, the admin user must also be a `rds_superuser`: ``` GRANT rds_superuser TO "teleport-admin"; ``` For PostgreSQL 16+, you must grant the `ADMIN` option to the admin user for each PostgreSQL role that Teleport will assign to your Teleport user. For example, to allow the admin user to grant and revoke role `reader`: ``` GRANT reader TO "teleport-admin" WITH ADMIN OPTION; ``` **Self-hosted PostgreSQL** The self-hosted PostgreSQL admin user must have X.509 authentication configured. ``` CREATE USER "teleport-admin" login createrole; ``` For PostgreSQL 16+, you must grant the `ADMIN` option to the admin user for each PostgreSQL role that Teleport will assign to your Teleport user. For example, to allow the admin user to grant and revoke role `reader`: ``` GRANT reader TO "teleport-admin" WITH ADMIN OPTION; ``` Note that the database must be configured to accept client certificate auth for the admin user by having the following entries in `pg_hba.conf`: ``` hostssl all all ::/0 cert hostssl all all 0.0.0.0/0 cert ``` Refer to the [self-hosted PostgreSQL guide](https://goteleport.com/docs/enroll-resources/database-access/enrollment/self-hosted/postgres-self-hosted.md#step-35-configure-your-postgresql-server) to ensure that your configuration is correct. --- DATABASE ACCESS CONTROLS FOR \`TELEPORT-ADMIN\` When [Database Access Controls](https://goteleport.com/docs/enroll-resources/database-access/rbac.md) feature is in use, the `teleport-admin` should have permissions to relevant database objects. You can grant `teleport-admin` the `SUPERUSER` option for self-hosted databases, or the `rds_superuser` role for RDS databases. For improved security through the principle of least privilege, you can also assign permissions directly to specific database objects. For example: ``` GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA schema1, schema2, schema3 TO "teleport-admin"; ``` --- 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: ``` 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 ``` This example assumes that you have configured the database as a dynamic resource. If you have configured your database using a static Teleport Database Service configuration, edit the entry in your `db_service.databases` configuration. 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. ## Step 2/3. Configure a Teleport role Database permissions are associated with a Teleport role, which can either allocate predefined database roles (configured in each database) or define specific database object permissions directly. Teleport grants these permissions for the duration of the connection. 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. **Database roles** 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}}" ``` The provisioned database user will 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](https://www.postgresql.org/docs/current/sql-createrole.html) for information on how to create database roles. **Database Access Controls** Required Teleport version: v15.2 or above. Use `spec.allow.db_permissions` section in a user role to specify object permissions given user should have. ``` kind: role metadata: name: read_all_tables spec: options: # create_db_user_mode enables automatic user provisioning for matching databases create_db_user_mode: keep allow: db_labels: '*': '*' db_names: - '*' db_permissions: # grant `SELECT` on all tables in `public` schema. - match: # object labels to match object_kind: table schema: public permissions: - SELECT version: v7 ``` You can define your own labels for database objects, applying them based on customizable import rules. These custom labels, such as `owner` or `environment`, can then be utilized when granting permissions. For additional information, refer to the [Database Access Controls](https://goteleport.com/docs/enroll-resources/database-access/rbac.md) page. Users created within the database will: - Have the same username as the authenticated Teleport user. - Be a part of the `teleport-auto-user` role. - Will be assigned permissions according to the chosen mechanism. 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 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--`. --- 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 --db-roles reader example ``` --- TIP You can also [access your PostgreSQL databases using the Web UI.](https://goteleport.com/docs/connect-your-client/teleport-clients/web-ui.md#starting-a-database-session) --- ## Troubleshooting ### 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 example ... FATAL: permission denied for 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 to "teleport-admin"; GRANT CONNECT ON DATABASE to "reader"; ``` ### Cannot execute in a read-only transaction error You may encounter the following error when connecting to an Amazon RDS Aurora reader endpoint: ``` $ tsh db connect --db-name 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 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 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 example ERROR: please use your mapped remote username ("remote--") to connect instead of "" ``` When you access resources in a remote cluster, the remote cluster will receive the name `remote--` 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. ### Unable to cancel a query If you use a PostgreSQL cli client like `psql`, and you try to cancel a query with `Ctrl+C`, but it doesn't cancel the query, then you need to connect using a tsh local proxy instead. When `psql` cancels a query, it establishes a new connection without TLS certificates, however Teleport requires TLS certificates not only for authentication, but also to route database connections. If you [enable TLS Routing in Teleport](https://goteleport.com/docs/zero-trust-access/deploy-a-cluster/tls-routing.md) then `tsh db connect` will automatically start a local proxy for every connection. Alternatively, you can connect via [Teleport Connect](https://goteleport.com/docs/connect-your-client/teleport-clients/teleport-connect.md) which also uses a local proxy. Otherwise, you need to start a tsh local proxy manually using `tsh proxy db` and connect via the local proxy. If you have already started a long-running query in a `psql` session that you cannot cancel with `Ctrl+C`, you can start a new client session to cancel that query manually: First, find the query's process identifier (PID): ``` SELECT pid,usename,backend_start,query FROM pg_stat_activity WHERE state = 'active'; ``` Next, gracefully cancel the query using its PID. This will send a SIGINT signal to the postgres backend process for that query: ``` SELECT pg_cancel_backend(); ``` You should always try to gracefully terminate a query first, but if graceful cancellation is taking too long, then you can forcefully terminate the query instead. This will send a SIGTERM signal to the postgres backend process for that query: ``` SELECT pg_terminate_backend(); ``` See the PostgreSQL documentation on [admin functions](https://www.postgresql.org/docs/current/functions-admin.html#functions-admin-signal) for more information about the `pg_cancel_backend` and `pg_terminate_backend` functions. ### SSL SYSCALL error You may encounter the following error when your local `psql` is not compatible with newer versions of OpenSSL: ``` $ tsh db connect --db-user postgres --db-name postgres postgres psql: error: connection to server at "localhost" (::1), port 12345 failed: Connection refused Is the server running on that host and accepting TCP/IP connections? connection to server at "localhost" (127.0.0.1), port 12345 failed: SSL SYSCALL error: Undefined error: 0 ``` Please upgrade your local `psql` to the latest version. ### Amazon RDS Blue/Green deployment enters a state of "Replication degraded" Amazon RDS Blue/Green deployment may enter a state of "Replication degraded" when auto-user provisioning is used to connect to the database. This occurs due to a [limitation in PostgreSQL logical replication](https://docs.aws.amazon.com/amazonrds/latest/userguide/blue-green-deployments-overview.html#blue-green-deployments-limitations-postgres) for Blue/Green deployments as Data Definition Language (DDL) statements cannot be replicated. Consequently, Amazon RDS will enter the "Replication degraded" state when a DDL change is detected. It is recommended to disable database auto-user provisioning before starting the Blue/Green deployment. If the database is auto-discovered by Teleport, you can temporarily remove the `teleport.dev/db-admin` AWS resource tag. For a database registered using either static config or a dynamic `db` resource, you can temporarily remove the `admin_user` setting. Once auto-user provisioning is disabled, you can still connect as the database admin user through Teleport. ## Next steps - Connect using your [GUI database client](https://goteleport.com/docs/connect-your-client/third-party/gui-clients.md). - Learn about [role templating](https://goteleport.com/docs/zero-trust-access/rbac-get-started/role-templates.md). - Read automatic user provisioning [RFD](https://github.com/gravitational/teleport/blob/master/rfd/0113-automatic-database-users.md). - Read database permission management [RFD](https://github.com/gravitational/teleport/blob/master/rfd/0151-database-permission-management.md). - 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](https://goteleport.com/docs/reference/access-controls/roles.md).