![Fork me on GitHub](/docs/_next/image/?url=%2Fdocs%2F_next%2Fstatic%2Fmedia%2Fforkme.b9234619.webp&w=256&q=75)
Teleport
Amazon Redshift Automatic User Provisioning
- Version 17.x
- Version 16.x
- Version 15.x
- Version 14.x
- Older Versions
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 v14.1.3 or higher with a configured Amazon Redshift database.
- Ability to connect to and create user accounts in the target database.
Automatic user provisioning is not compatible with Redshift Serverless.
Step 1/3. Configure database admin
Teleport uses the same authentication mechanism (IAM authentication) when connecting as an admin user as for regular user connections.
The admin user must have privileges within the database to create users and grant them privileges. The admin user must also have privileges to monitor user processes and role assignments:
CREATE USER "teleport-admin" WITH PASSWORD DISABLE;
GRANT ROLE "sys:superuser" TO "teleport-admin";
Users created by Teleport will be assigned the teleport-auto-user
role 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: "redshift-cluster-1.abcdefghijklm.us-east-1.redshift.amazonaws.com:5439"
admin_user:
name: "teleport-admin"
kind: db
version: v3
metadata:
name: example
spec:
protocol: "postgres"
uri: "redshift-cluster-1.abcdefghijklm.us-east-1.redshift.amazonaws.com:5439"
admin_user:
name: "teleport-admin"
For auto-discovered cloud databases, the name of the admin user is taken from
the teleport.dev/db-admin
label.
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 ofkeep
mode.
Users created within the database will:
- Have the same username as the authenticated Teleport user.
- Be assigned 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.
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.comtsh db connect --db-name <database> example
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
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> exampleERROR: 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.
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
then tsh db connect
will automatically start a local proxy for every
connection.
Alternatively, you can connect via
Teleport Connect
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,starttime,duration,trim(user_name) AS user,trim(query) AS query FROM stv_recents WHERE status = 'Running';
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(<PID>);
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(<PID>);
See the PostgreSQL documentation on
admin functions
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 postgrespsql: 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.
Next steps
- Connect using your GUI database client.
- Learn about role templating.
- Read automatic user provisioning RFD.