Troubleshooting Database Access
Common issues and resolution steps.
Connection attempts fail
Certificate expired or is not yet valid
Attempts to connect to the database fail, and the error message returned is similar to: "Database service could not validate database’s certificate: certificate expired.".
Solution: Renew the database certificate.
The Teleport Database Service uses a Teleport-issued certificate to authenticate with the Database. This error happens when Teleport cannot authenticate, often due to expired certificates.
The command used to generate a new certificate is tctl auth sign. For example,
to create a certificate for PostgreSQL, the command looks like this:
# Export Teleport's certificate authority and a generate certificate/key pair
# for host db.example.com with a 3-month validity period.
$ tctl auth sign --format=db --host=db.example.com --out=server --ttl=2190h
In this example, db.example.com is the hostname where the Teleport Database
Service can reach the PostgreSQL server.
Each database uses a different format. You can check your database guide for more details and examples:
After the new certificate is issued, update your database to make it take effect.
Access to db denied
Attempts to connect to the database fail with an error message similar to: "access to db denied".
Solution: Configure the database instance and Teleport role-based access control (RBAC) to allow the user access.
Check that the database user and database name (aka schema) you are trying to access via tsh exist in the database instance.
When referring to "database name" or db_names, this is not the same as the name field in the db_service section of your Database Service configuration;
this is referring to database names or schemas within a particular database instance.
Once your database instance is configured, Teleport RBAC must be configured to allow access.
Assign the Teleport user a role that allows db_users, db_names, and db_labels matching the
database user, database name, and Teleport static or dynamic labels for the database. Additionally, check that
the user does not have role(s) that deny the corresponding db_users, db_names, or db_labels.
Database names are only enforced for PostgreSQL and MongoDB databases.
When connecting to a PostgreSQL or MongoDB instance, be sure to specify --db-name:
$ tsh db connect --db-user=exampleuser --db-name=exampledb
For example, the following user and role resources allow the Teleport user [email protected] to
access any database name or database user
within a production database except for the admin database user or
the postgres database name:
kind: role
version: v5
metadata:
name: db-developer
description: "Example role that grants restricted access to production databases"
spec:
allow:
db_labels:
environment: ["prod"] # for this example, assume production databases have this label in your Teleport cluster
db_users: ["{{internal.db_users}}"]
db_names: ["{{internal.db_names}}"]
deny:
db_users: ["admin"]
db_names: ["postgres"]
---
kind: user
metadata:
name: [email protected]
spec:
roles: ["db-developer"]
traits:
db_users: ["*"]
db_names: ["*"]
The internal.db_users and internal.db_names traits are
replaced with values from the Teleport local user database. For full details on
how traits work in Teleport roles, see the Teleport Access
Controls Reference.
Now suppose we want to grant Alice more permissive access.
To keep this example simple, let's just assign Alice a different role.
Update Alice's roles to include just the default Teleport role access, which allows access to all resources.
We can update a user's roles from the command-line by using either tctl users update or tctl create:
- tctl users update
- tctl create
$ tctl users update [email protected] --set-roles=access
First save Alice's user resource to a local file:
$ tctl get users/[email protected] > alice.yaml
Then modify alice.yaml to assign the access role:
kind: user
metadata:
name: [email protected]
spec:
roles: ["access"]
traits:
db_users: ["*"]
db_names: ["*"]
Finally, use tctl create --force to update the user
(--force is required to overwrite an already existing resource).
$ tctl create --force alice.yaml
Now Alice can connect to any database in the Teleport cluster using any database user or database name.
This example is intentionally simple; we could have configured Alice's permissions using more fine-grained control. For more detailed information about database access controls and how to restrict access see the RBAC documentation.
Connection to MySQL database results in "Unknown system variable 'query_cache_size'" error
When TLS Routing is disable by default, the Teleport Proxy Service returns 8.0.0-Teleport as the MySQL server version. In some cases, like connecting with a GUI Client, this can result in obtaining an Unknown system variable 'query_cache_size' error that indicates that MySQL capabilities were not properly negotiated between the MySQL client and server.
One way to solve this issue is to use the TLS Routing feature, where the Teleport Proxy Service propagates the correct MySQL server version via TLS Routing extensions.
If migration to TLS Routing is not possible, another way to bypass this error is to use the Teleport local proxy command, which allows you to establish a TLS Routing connection to the Teleport Proxy Service even if TLS Routing was not enabled on the Teleport cluster.
proxy_service:
mysql_server_version: "8.0.4"