Roles and Privileges

Postgres has one kind of principal and it is called a role. Some roles carry LOGIN and are what a connection string names; the rest exist to be granted to other roles, which is what every other system calls a group. CREATE USER is CREATE ROLE with LOGIN implied, the same catalogue object spelled differently. Every access-control design in this chapter is built from that one thing plus membership between instances of it.

Cartwheel has four roles on paper and uses two of them. cartwheel_migrator owns all nine tables in public, because Chapter 3 put it there so the migrations would have a stable owner, and it is also the role in the deployment secret that app-01 and app-02 connect with. cartwheel_analytics has never been used, because somebody pasted cartwheel_admin's password into a Slack channel in 2024 and the dashboard has authenticated with it ever since. Two consequences follow, and neither is hypothetical: an injection in the checkout path reaches DROP TABLE orders rather than a permission error, and nothing in the logs distinguishes a scheduled report from a person at a keyboard.

Roles Are Cluster-Wide

A role created without LOGIN is a group; with it, a login. Membership is a grant, and by default it is inherited, so a member holds everything the group holds from the moment it connects, without asking. NOINHERIT reverses that and forces an explicit SET ROLE before the group's privileges apply, which is worth having on the human roles that can reach administrative groups: the SET ROLE appears in the log and the ambient version does not.

CREATE ROLE cartwheel_read NOLOGIN;              -- a group, cannot connect
GRANT USAGE  ON SCHEMA analytics TO cartwheel_read;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO cartwheel_read;

GRANT cartwheel_read TO cartwheel_analytics;     -- the dashboard
GRANT cartwheel_read TO nadia;                   -- and one human, individually

Roles are not database objects. They live in a shared catalogue beside databases, tablespaces and replication slots, so cartwheel_read created while connected to cartwheel is visible from every database on pg-primary, and \du prints the same list wherever it runs. Privileges are the opposite: a grant on a table is recorded with that table, in that database. The split is why a role can exist everywhere and still read nothing anywhere, and why the answer to "the analytics team can connect but sees no tables" is never to create the role again.

Ownership Is Not a Privilege

The owner of a table can drop it, rewrite it, change its column types and hand it to somebody else. None of that is a privilege in the GRANT sense: the manual is explicit that the right to modify or destroy an object is inherent in being its owner and cannot be granted or revoked in itself. There is no REVOKE DROP. The only way to stop the role in the application's connection string from dropping orders is for that role not to own orders.

-- run as cartwheel_migrator, which owns the nine tables in public
GRANT USAGE ON SCHEMA public TO cartwheel_app;
GRANT SELECT, INSERT, UPDATE, DELETE
  ON ALL TABLES IN SCHEMA public TO cartwheel_app;
GRANT USAGE ON SEQUENCE orders_id_seq TO cartwheel_app;

That is four verbs and one sequence, with the schema itself out of reach. cartwheel_app gets no TRUNCATE, no TRIGGER, no REFERENCES, and no route to DDL of any kind. The sequence grant is the line people leave out and then debug for an hour: a column whose default calls nextval() needs USAGE on that sequence, so without it every insert into orders fails with a permission error naming an object the statement never mentioned. Making the change at Cartwheel is one value in a secret and a rolling restart of app-01 and app-02: the design takes fifteen minutes, and the deployment is the part that needs a window.

The Grant Chain People Forget

Most permission tickets fall into one of two gaps. The first is that reaching a table needs USAGE on its schema as well as a privilege on the table, so a perfectly correct GRANT SELECT still answers "permission denied" when the schema grant is missing. The second is larger. GRANT … ON ALL TABLES IN SCHEMA is not a standing rule; it is a loop over the tables that exist at that instant, and every table a later migration creates arrives with no grants on it at all.

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

ALTER DEFAULT PRIVILEGES FOR ROLE cartwheel_migrator IN SCHEMA analytics
  GRANT SELECT ON TABLES TO cartwheel_read;

FOR ROLE is the load-bearing clause. Default privileges attach to the role that creates the object, not to the schema in general, so those two statements fire only for objects cartwheel_migrator makes; a table created by cartwheel_admin during an incident gets nothing and the ticket comes back a week later. They are also not inherited through membership: at creation time only the current role's own defaults apply. Both facts point the same way: this belongs in the migration tooling, which always runs as one known role, rather than in a runbook somebody follows by hand. The same statement shape covers sequences, functions, types and schemas.

Predefined Roles Instead of Superuser

Every predefined role exists to remove one specific reason people had for granting superuser. pg_monitor is the one to know: it is a member of pg_read_all_settings, pg_read_all_stats and pg_stat_scan_tables, which together are exactly what a metrics exporter or a human debugging a slow Saturday needs and nothing more. pg_read_all_data and pg_write_all_data grant reads or writes across every table as though the privilege had been granted individually, plus USAGE on every schema, and neither of them bypasses row-level security, which becomes the point of the topic two pages from here.

GRANT pg_monitor TO nadia;      -- every setting, every pg_stat_* view

GRANT MAINTAIN ON analytics.daily_orders TO cartwheel_analytics;

The second line is newer than most role setups. Since 17 there is a MAINTAIN privilege, and a pg_maintain role that carries it across every relation, covering VACUUM, ANALYZE, CLUSTER, REINDEX, REFRESH MATERIALIZED VIEW and LOCK TABLE. Before that, refreshing a materialized view required owning it, which is how nightly refresh jobs ended up running as the schema owner or as something worse. The metrics exporter gets its own login role holding pg_monitor and nothing else; pg_signal_backend is the other habit-breaker, letting a role cancel or terminate somebody else's session without any further administrative power, and deliberately stopping short of a superuser's backend.

PUBLIC, and What 15 Changed

PUBLIC is not a role you can drop. Every role is implicitly a member of it, and Postgres grants a small fixed set of privileges to it whenever an object is created: CONNECT and TEMPORARY on databases, EXECUTE on functions and procedures, USAGE on languages and data types. Tables, columns, sequences, schemas, tablespaces and configuration parameters get nothing by default, so these grants stay invisible until one of the three that do exist causes a problem.

REVOKE CONNECT ON DATABASE cartwheel FROM PUBLIC;
GRANT  CONNECT ON DATABASE cartwheel
    TO cartwheel_app, cartwheel_analytics, cartwheel_migrator;

REVOKE CREATE ON SCHEMA public FROM PUBLIC;   -- default since 15; check anyway

The function default is the sharpest of the three. A SECURITY DEFINER function created by cartwheel_admin is callable by every role in the cluster the moment it commits, so the matching REVOKE EXECUTE belongs in the migration that creates it, not in an audit eighteen months later. The database default is worth closing at setup, so that reaching cartwheel at all is a list of three roles rather than everyone who can authenticate. CREATE on the public schema was the fourth and stopped being granted by default in 15 — but a cluster upgraded from 14 or earlier still carries it, and the last statement is what removes it.

Dropping a Role

A role that owns anything cannot be dropped, and neither can one that still holds a privilege somewhere. That is the whole reason departed staff and retired integrations accumulate in the catalogue for years: the first DROP ROLE fails with a dependency message, the ten spare minutes never arrive, and the login stays valid. The sequence that works is two statements before the drop, repeated in every database that contains any of the role's objects.

-- connected to cartwheel; repeat in every database it owns objects in
REASSIGN OWNED BY cartwheel_report TO cartwheel_migrator;
DROP OWNED BY cartwheel_report;   -- privileges and default privileges too
DROP ROLE cartwheel_report;

REASSIGN OWNED moves ownership of the role's objects in the current database, plus the shared ones, meaning databases and tablespaces, to the new owner. It deliberately does not touch privileges the role was granted on objects it never owned, nor the default privileges it created, and DROP OWNED is what clears those. Miss a database and the final DROP ROLE fails pointing at a dependency in a database the session was never connected to, which is exactly the failure that ended the first attempt.

Password policy is the piece Postgres does not have. VALID UNTIL sets an expiry on the password and not on the role itself, and it is not enforced at all when the login uses a non-password method; there is no history, no complexity rule, no forced rotation. On a self-managed cluster that gap is closed by pushing authentication outward, to certificates or Kerberos or an identity provider, which is where the next topic begins, with the trust line still sitting in Cartwheel's pg_hba.conf.