Databases and roles

A service holds multiple logical databases at no extra charge. We do not charge per database.

Databases and roles are created through different paths on purpose: databases through the platform, roles by you in SQL. The reason is in the words we are allowed to use about each.

Your service's database #

A new service is created with one logical database, appdb. It is what the connection examples throughout this documentation use.

Your managed elevated role does not hold CREATEDB, so CREATE DATABASE is refused:

ERROR:  permission denied to create database

That is a deliberate design decision with one purpose: it makes the published database limit a real limit rather than a claim. A limit enforced at the platform boundary is a limit. A limit that is documented and not enforced is a guideline that will be exceeded, discovered during an incident, and argued about.

Your plan's database count is on plan limits.

Create a role #

Roles are yours. Your managed elevated role holds CREATEROLE, so you create and manage them with ordinary SQL:

CREATE ROLE app_user LOGIN PASSWORD 'REDACTED-EXAMPLE-ONLY';
GRANT CONNECT ON DATABASE appdb TO app_user;

The role count is a guideline, not a limit. Because CREATEROLE is granted we cannot enforce a ceiling, and we are not going to print a number and call it enforced when it is not. Your CREATE ROLE will not silently fail.

The shape that works #

One database per application. One role per component that connects, rather than one shared role:

Separate roles mean a leaked application credential cannot drop a table, and an audit of who did what has an answer.

The grants themselves, including the default-privileges step that people forget, are on privileges.

What you will see #

SELECT rolname, rolcanlogin, rolcreatedb, rolcreaterole, rolsuper
FROM pg_roles
WHERE rolname NOT LIKE 'pg\_%'
ORDER BY rolname;

Your managed elevated role reads rolcreaterole true and rolcreatedb and rolsuper false. Roles you create for your application should read false on all three.

What you do not get #

You do not get superuser, and you do not get host access. See the privilege model for what the managed elevated role can and cannot do, and why each boundary is where it is.

Troubleshooting #

permission denied to create database. Expected. CREATEDB is withheld so the database count is enforceable. Use appdb.

must have admin option on role when granting. CREATEROLE from PostgreSQL 16 onward lets a role administer only the roles it created. Grant from the role that created the target, or from the managed elevated role.

A role you created cannot see a table. It has CONNECT but not USAGE on the schema, or not the object privilege. Check both, then check default privileges: grants on existing tables do not cover tables created later.

Related #

• Privileges
• Privilege model
• Plan limits