Database User Privileges

This page explains which permissions you get on a managed PostgreSQL or MySQL
database, what is never granted, and what to do when a query fails with a
permission error such as Access denied, permission denied for schema, or
you need (at least one of) the SUPER privilege(s).

Two kinds of database user

Every managed database has two kinds of user, and they are set up differently.

Keep the two separate. Do not point an application at a Teleport login, and do
not grant platform roles to an application user — see
PostgreSQL Login Fails with PAM Authentication Failure
for what goes wrong when you do.

To see which users exist on a database, open Health check, select the
database, and look at the Users tab.

What a Teleport access role gives you

When you request database access, the ACCESS ROLE you pick decides what you
can do once connected:

The roles offered depend on the database engine and how your organisation's
roles are configured, so the list you see may differ.

After you log in to Teleport, run tsh db ls. The Allowed Users column
shows which --db-user values your approved role lets you connect as. Use that
value in tsh db connect.

Time-limited access

Every database role is temporary. When you raise the request you choose an
EXPIRATION TIME (TTL) from the durations offered for that role, for example
2 hours, 8 hours or 3 days. When it expires the access ends; raise a new request
to continue. Ask for the shortest duration that covers the job, especially for
superuser roles on production.

What is never granted

The superuser roles are the most powerful level the cloud provider allows, but
they are not a full database superuser. Amazon RDS and Google Cloud SQL are
managed services: the provider keeps a few privileges for itself so it can run
backups, replication, failover and patching safely. Nobody on the account can
hold them, including the database's own administrator user, so we cannot grant
them either.

On MySQL and MariaDB, this means:

• no SUPER privilege
• no SET GLOBAL for server-wide settings such as max_connections — see Database Refuses Connections to have the limit raised
• no creating stored functions or triggers while binary logging is on, unless
  the database is configured to allow it (see below)

On PostgreSQL, the superuser role maps to the provider's administrator role
(rds_superuser on AWS, cloudsqlsuperuser on GCP). It can do almost all
day-to-day administration, but not ALTER SYSTEM, file-system access, or
installing extensions the provider does not support.

Permission errors and what to do

SET GLOBAL is refused on MySQL

Access denied; you need (at least one of) the SUPER, CONNECTION ADMIN privilege(s) for this operation

Server-wide settings are changed in the database's configuration, not through
SQL. You can see the current values under Health check → your database →
Parameters. To change one, raise a request with the database name, the
parameter, and the value you need. Some parameters only apply after a restart;
we will tell you if yours does.

Creating or dropping a stored function fails on MySQL

You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

Binary logging is on because backups and point-in-time restore depend on it.
Raise a request to set the parameter log_bin_trust_function_creators to 1 on
that database. After that, users without SUPER can create, alter and drop
functions, procedures and triggers.

If the error mentions DEFINER, the routine was created with a different user
as its definer. Recreate it with DEFINER=CURRENT_USER, or without a DEFINER
clause.

A migration fails with permission denied for schema on PostgreSQL

Your application user does not have rights on the schema it is writing to. This
often appears after someone creates a new schema from a Teleport session: the
schema is owned by the Teleport user, not by your application.

Connect with a superuser role and grant the application user what it needs:

GRANT USAGE, CREATE ON SCHEMA my_schema TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA my_schema TO app_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA my_schema
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;

If the migration needs to alter existing tables, it must own them. Transfer
ownership with ALTER TABLE ... OWNER TO app_user, or better, let your
migration tool create the schema and tables as the application user from the
start. On PostgreSQL 15 and later, the public schema no longer allows every
user to create objects, so a new database may need the same GRANT on public.

You need a new database, schema or application user

With a superuser role you can create schemas and grant permissions yourself, as
above. For a new application user whose credentials should be injected into your
pods, raise a request with the database name, the user name, and the databases
or schemas it needs. The reply tells you whether the user and its grants are
created for you or whether you run the GRANT statements yourself with your
superuser role.

Asking for more

If a role or permission you need is not offered, raise a request in your
dedicated Slack channel with:

• the database name as SVIEW shows it
• the user you connect as
• the exact SQL statement and the full error text
• what you are trying to achieve, so we can suggest a route that works within
  what the cloud provider allows

Related articles

• Connect to a Database — logging in through Teleport
• Access Requests — raising and approving requests
• Attach a Database to Your Application — application users and injected credentials
• PostgreSQL Login Fails with PAM Authentication Failure — why platform roles must not be granted to other users