# Troubleshooting Database Access

Common issues and resolution steps.

## Connection attempts fail

### Timeout errors

Attempts to connect to the database fail with a message similar to **"dial tcp ... i/o timeout"**.

The Teleport Database Service requires connectivity to your database endpoints.

Check that firewall rules (e.g., AWS security groups) allow connectivity between the Teleport Database Service and the database endpoint.

- Inbound firewall rules for the database must allow connections from the Teleport Database Service.
- Outbound firewall rules for the Teleport Database Service must allow connections to the database endpoint.

---

TIP

On the same host as the Teleport Database Service, try running `nc` to check if it can reach the database port.

- Database host: database-host
- Database port: database-port

```
$ nc -zv database-host database-port
Connection to postgres-instance-1.sadas.us-east-1.rds.amazonaws.com (172.31.24.172) 5432 port [tcp/postgresql] succeeded!
```

---

Debugging connection timeout errors in AWS

For deployments in AWS, it may be helpful to use [AWS Reachability Analyzer](https://docs.aws.amazon.com/vpc/latest/reachability/what-is-reachability-analyzer.html) to analyze the network path between the Teleport Database Service and the database.

1. Identify the Elastic Network Interface (ENI) associated with the Teleport Database Service host. This can be found in the [EC2 console](https://console.aws.amazon.com/ec2/home?NIC).

2. Identify the private IP address of the database.

3. Create and analyze a network path:

   - Set the path source to the ENI associated with the Teleport Database Service host.
   - Set the path destination to the database IP.

4. Check the analysis results to identify reachability issues.

If your database is registered dynamically or via auto-discovery, repeat the above connectivity test for *every*\* Teleport Database Service instance that proxies this database. To list all Teleport Database Service instances associated with a given database, run the `tctl get db_server/<db_name>` command. For example:

```
$ tctl get db_server/postgres-instance-1 --format json | jq '.[] | {hostname: .spec.hostname, host_id: .spec.host_id, version: .spec.version, target_health: .status.target_health}'
{
  "hostname": "ip-10-0-0-111.ca-central-1.compute.internal",
  "host_id": "e5e670ac-a7b8-44ef-b373-6296d87f50e8",
  "version": "18.3.0",
  "target_health": {
    "status": "unhealthy",
    ...
  }
}
{
  "hostname": "ip-10-0-0-222.ca-central-1.compute.internal",
  ...
}

```

If any of the Database Service instances listed here **should not** proxy the database, (for example, a Database Service instance in a different VPC or AWS region without connectivity), locate and update their configurations so they only receive or discover databases they can reach. In most cases, you can achieve this by refining your tag filters, such as adding the `vpc-id` label.

### 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 generated 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:

- [PostgreSQL](https://goteleport.com/docs/enroll-resources/database-access/enrollment/self-hosted/postgres-self-hosted.md#step-25-create-a-certificatekey-pair)
- [MySQL/MariaDB](https://goteleport.com/docs/enroll-resources/database-access/enrollment/self-hosted/mysql-self-hosted.md#step-24-create-a-certificatekey-pair)
- [MongoDB](https://goteleport.com/docs/enroll-resources/database-access/enrollment/self-hosted/mongodb-self-hosted.md#set-up-mutual-tls)
- [CockroachDB](https://goteleport.com/docs/enroll-resources/database-access/enrollment/self-hosted/cockroachdb-self-hosted.md#set-up-mutual-tls)
- [Redis](https://goteleport.com/docs/enroll-resources/database-access/enrollment/self-hosted/redis.md#step-45-set-up-mutual-tls)
- [Redis Cluster](https://goteleport.com/docs/enroll-resources/database-access/enrollment/self-hosted/redis-cluster.md#step-46-set-up-mutual-tls)

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`.

---

WARNING

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 `alice@example.com` 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: alice@example.com
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 [Access Controls Reference](https://goteleport.com/docs/reference/access-controls/roles.md).

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 users update alice@example.com --set-roles=access

```

**tctl create**

Open Alice's user resource in your text editor:

```
$ tctl edit users/alice@example.com
```

Then modify the resource to assign the `access` role:

```
kind: user
metadata:
  name: alice@example.com
spec:
  roles: ["access"]
  traits:
    db_users: ["*"]
    db_names: ["*"]

```

Save and close the file to apply your changes.

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](https://goteleport.com/docs/enroll-resources/database-access/rbac.md) 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](https://goteleport.com/docs/zero-trust-access/deploy-a-cluster/tls-routing.md), 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](https://goteleport.com/docs/connect-your-client/third-party/gui-clients.md#how-gui-clients-access-teleport-protected-databases), 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"

```