Postgres – Databases, Schemas, Roles and Privileges

Almost every application starts life connecting to Postgres as a superuser, because that is what the container gives you. It is worth about twenty minutes to fix, and the twenty minutes are best spent before there is data worth losing.

This post covers the three-level namespace, what a role actually is, and the grants that give an application exactly the access it needs.

Database, schema, table

A connection belongs to one database and cannot see across to another. Inside a database, schemas are namespaces:

CREATE DATABASE stayhub;
CREATE SCHEMA billing;
CREATE TABLE billing.invoices (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY);

Most applications need exactly one schema and should use public. Reach for more when you have genuinely separate concerns in one database — a per-tenant schema, or an audit schema nobody may write to.

Unqualified names are resolved through the search path:

SHOW search_path;        -- "$user", public
SET search_path TO billing, public;

"$user" means "a schema named after the connecting role, if one exists". Setting the path per session is fine for exploring; for an application, set it on the role once so nothing has to remember — there is an example at the end of the next section.

A role is a user and a group

Postgres has one concept, ROLE, and it covers both. CREATE USER is literally an alias for CREATE ROLE ... LOGIN. A role with LOGIN is what you would call a user; a role without it is what you would call a group, and other roles are granted membership in it.

CREATE ROLE readonly NOLOGIN;                       -- a group
CREATE ROLE alice LOGIN PASSWORD 'secret';          -- a user
GRANT readonly TO alice;                            -- membership

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

Roles are cluster-wide, not per database. One readonly role covers every database on the server, which is usually what you want and occasionally a surprise.

Settings can be attached to a role, which is where the search path belongs:

ALTER ROLE alice SET search_path TO public;
ALTER ROLE alice SET statement_timeout TO '30s';

Both apply from that role's next connection onwards. The second one is worth doing for any human-facing role: a runaway analytics query then kills itself rather than holding resources until someone notices.

The three roles worth having

The split that keeps a production database safe, and it is not complicated:

The point of the first row: if the application's own credentials cannot execute DROP TABLE, then no bug, injection or mistyped script in the application can drop a table. Migrations get the dangerous role, and they run once, watched.

Setting it up

Every statement here runs against a real database; this is the whole sequence:

CREATE ROLE app_owner NOLOGIN;
CREATE ROLE stayhub_app LOGIN PASSWORD 'from-your-secret-store';
CREATE ROLE stayhub_readonly LOGIN PASSWORD 'also-from-there';

-- Close the default door first. Out of the box, EVERY role can connect to a new
-- database and create objects in its public schema.
REVOKE ALL ON DATABASE stayhub_lab FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM PUBLIC;

GRANT CONNECT ON DATABASE stayhub_lab TO stayhub_app, stayhub_readonly;
GRANT USAGE   ON SCHEMA public        TO stayhub_app, stayhub_readonly;

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES    IN SCHEMA public TO stayhub_app;
GRANT USAGE                          ON ALL SEQUENCES IN SCHEMA public TO stayhub_app;
GRANT SELECT                         ON ALL TABLES    IN SCHEMA public TO stayhub_readonly;

PUBLIC in those REVOKE statements is not the schema — it is the implicit group every role belongs to. Since Postgres 15 a new database is less permissive than it used to be, but revoking explicitly costs nothing and does not depend on which version someone provisioned.

USAGE on sequences is the grant everybody forgets. Without it, the application can INSERT into a table with a generated key and gets permission denied for sequence on the first row.

The grant that keeps working

GRANT ... ON ALL TABLES is a one-time operation. It grants on the tables that exist at that moment. The table your next migration creates has none of it, and the failure surfaces in production the day after a deploy, not during it.

ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO stayhub_app;

ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public
    GRANT SELECT ON TABLES TO stayhub_readonly;

ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public
    GRANT USAGE ON SEQUENCES TO stayhub_app;

Read FOR ROLE app_owner carefully, because it is the part that goes wrong: default privileges attach to the role that creates the object, not to the schema. If your migrations run as app_owner, that is the role to name. Run them as somebody else one day and the new tables arrive with no grants, exactly as before.

Two attributes worth setting on a login role

CREATE ROLE reporting LOGIN PASSWORD 'x'
    CONNECTION LIMIT 5
    VALID UNTIL '2027-01-01';

CONNECTION LIMIT stops one misconfigured analytics tool from consuming every backend the server has and locking your application out. It is a much better first line of defence than raising max_connections.

VALID UNTIL expires the password on a date. Excellent for the contractor's account you will otherwise forget; a genuine outage waiting to happen on a service account, because nothing warns you and the application simply stops authenticating one morning. Use it on humans, not on applications.

Passwords are stored hashed, and which hash is a server setting:

SHOW password_encryption;   -- scram-sha-256 on any modern install

If that says md5, you are on an old configuration; switch it and have every role set its password again, because changing the setting does not rehash the existing ones.

Checking what you did

SELECT grantee, privilege_type
FROM   information_schema.role_table_grants
WHERE  table_name = 'bookings'
ORDER  BY grantee, privilege_type;

Or \dp bookings in psql, which prints the same thing more compactly. The real test is the direct one — connect as the application role and try to do something it should not be able to do:

psql "postgresql://stayhub_app:...@localhost:5433/stayhub" -c 'DROP TABLE bookings;'
# ERROR:  must be owner of table bookings

That error message is the twenty minutes paying for itself.

Before any of this applies: pg_hba.conf

Grants decide what a role may do once it is connected. Whether it may connect at all is decided earlier, by pg_hba.conf — host-based authentication — which is a file of rules matched top to bottom on connection type, database, role and client address.

# TYPE  DATABASE  USER          ADDRESS        METHOD
local   all       all                          trust
host    stayhub   stayhub_app   10.0.0.0/8     scram-sha-256
host    all       all           0.0.0.0/0      reject

The first line is why docker exec ... psql needs no password, and the last is the one worth adding deliberately rather than relying on the absence of a rule. First match wins, so order matters. After editing it, SELECT pg_reload_conf(); — no restart needed.

What this does not cover

Row-level security, which restricts which rows a role may see rather than which tables, is a genuinely good feature for multi-tenant applications and a genuinely easy way to lock yourself out of your own data. Get the role split above in place first; it prevents the accidents that actually happen.