skip to content

Role Attributes & Grants

In PostgreSQL every user is a role with LOGIN; attributes, membership options, predefined roles and ownership decide what it may do. Interviewers probe search_path traps and default privileges.

part ofPostgreSQLread it →
on this pageshow

explore

questions

6

In PostgreSQL, what do the LOGIN, SUPERUSER, CREATEROLE and REPLICATION role attributes allow, and why doesn't role membership pass them on?

level: juniorimportance: must knowfreq 64%

answer

  1. one cluster-wide kind of principal
  2. CREATE USER differs by one default
  3. flags on the role, not ACL entries
  4. SET ROLE to use an attribute
  5. CREATEROLE needs ADMIN on its target

basics

~20 s

LOGIN lets a role open a session, SUPERUSER skips every permission check except login, CREATEROLE creates and administers roles it holds ADMIN on, and REPLICATION opens WAL-sender connections. Attributes belong to the role itself, so members never inherit them.

solid answer

~50 s

In PostgreSQL every user is a role, and `CREATE USER` is simply `CREATE ROLE ... LOGIN`. `LOGIN` decides whether the role can be named when connecting. `SUPERUSER` bypasses all permission checks except the right to log in, and only a superuser can create one. `CREATEROLE` lets a role create roles and alter, drop or grant membership in roles on which it holds the `ADMIN` option; it receives that option automatically for roles it creates, but it can never create superusers or hand out `REPLICATION` or `BYPASSRLS`. `REPLICATION` together with `LOGIN` is what a streaming standby or `pg_basebackup` connects with. These are attributes stored on the role in `pg_authid`, not privileges in an ACL, so membership does not pass them on: a member must `SET ROLE` to use `SUPERUSER`, `CREATEDB` or `CREATEROLE`, and `LOGIN` and `REPLICATION` are checked on the role that actually connects.

code

sql · 9 lines
sql
CREATE ROLE hr_owner NOLOGIN;
CREATE ROLE hr_analysts NOLOGIN;
CREATE USER hr_app CONNECTION LIMIT 40;   -- CREATE USER implies LOGIN
CREATE ROLE hr_replicator LOGIN REPLICATION;

SELECT rolname, rolcanlogin, rolsuper, rolcreaterole, rolreplication
FROM pg_roles
WHERE rolname LIKE 'hr\_%'
ORDER BY rolname;

go deeper

for a junior

Know that users and groups are both roles, that CREATE USER only adds LOGIN, and what SUPERUSER, CREATEDB, CREATEROLE and REPLICATION each permit.

for a middle

Explain why attributes are not inherited through membership, how SET ROLE makes one usable, and that LOGIN and REPLICATION are checked on the connecting role.

for a senior

Show the bounds of CREATEROLE: ADMIN on created roles, no superuser, REPLICATION or BYPASSRLS grants, and how that lets a team manage its logins without superuser.

for a principal

Frame which few logins may hold SUPERUSER or CREATEROLE across a fleet, and how delegated role administration keeps day-to-day changes away from superuser sessions.

## One kind of principal: the role PostgreSQL has no separate user and group objects. Every principal is a **role**, stored once for the whole cluster in the `pg_authid` catalog and readable through the `pg_roles` view, so a role exists in every database of the cluster at once. Whether a role acts as a login, a group or both depends on its **attributes** and its **memberships**. `CREATE USER name` is exactly `CREATE ROLE name LOGIN`; the two commands differ only in that one default. Attributes are flags on the role itself, not entries in an object's access-control list. That single fact explains most of the surprises around them: they are set with `CREATE ROLE` or `ALTER ROLE`, never with `GRANT`, and they do not travel through membership. ## The attributes, one by one | Attribute | What it allows | Default | |---|---|---| | `LOGIN` | the role can be named as the user of a new connection | off for `CREATE ROLE`, on for `CREATE USER` | | `SUPERUSER` | bypasses every permission check except the right to log in | off | | `CREATEDB` | create databases | off | | `CREATEROLE` | create roles; alter, drop and manage membership of roles it holds `ADMIN` on | off | | `REPLICATION` | open a replication connection (a WAL sender), as a standby or `pg_basebackup` does | off | | `INHERIT` | the default `INHERIT` option for memberships granted to this role | on | | `CONNECTION LIMIT` | caps the role's concurrent sessions | `-1`, no limit | `BYPASSRLS` is an attribute too; what it does belongs with row-level policies. Some practical consequences: - A role without `LOGIN` is refused at connection time with `role "..." is not permitted to log in`, even after authentication succeeded. Group roles such as `hr_analysts` are normally `NOLOGIN`. - `SUPERUSER` is the only attribute that skips privilege checks wholesale, and only an existing superuser can create another one. - `REPLICATION` must sit on the login role together with `LOGIN`; a replication connection from a role without it fails with `permission denied to start WAL sender`. - `CONNECTION LIMIT` is enforced only approximately, so treat it as a guard rail rather than a quota. ## Why attributes do not flow through membership Privileges on objects reach members through the `INHERIT` option of a membership grant. Attributes do not: the `CREATE ROLE` reference states that the attributes it defines are non-inheritable, even when the grant has the `INHERIT` option. To use one, the session has to become the role: 1. `ana_kim` is a member of `db_creators`, which has `CREATEDB`. 2. `CREATE DATABASE` run as `ana_kim` fails with a permission error. 3. After `SET ROLE db_creators` (allowed when the membership carries the `SET` option) the same command succeeds, and the new database is owned by `db_creators`. `LOGIN` and `REPLICATION` are checked on the role that connects, before any `SET ROLE` is possible, so membership never helps with them at all. ## CREATEROLE is a bounded, delegated power In PostgreSQL 18, `CREATEROLE` is deliberately limited: - it can alter and drop only roles on which it holds the `ADMIN` option; when a `CREATEROLE` role that is not a superuser creates a role, the server grants it that role `WITH ADMIN TRUE, SET FALSE, INHERIT FALSE`, recorded as granted by the bootstrap superuser so the creator cannot remove it; - "alter" covers most of `ALTER ROLE`, including changing passwords; - it cannot create `SUPERUSER` roles or gain any power over existing superusers; - it cannot create `REPLICATION` roles, grant or revoke `REPLICATION`, or grant `BYPASSRLS`. That is what lets a platform team manage the logins of an HR product without holding superuser. ## Inspecting and setting attributes psql's `\du` lists roles with their attributes; scripts query `pg_roles` (`rolcanlogin`, `rolsuper`, `rolcreatedb`, `rolcreaterole`, `rolreplication`, `rolconnlimit`). Attributes change with `ALTER ROLE hr_app NOCREATEDB` and the like. `ALTER ROLE ... SET parameter` stores a per-role setting default instead, which only matters for roles that log in. For an HR SaaS a common layout is: - a `NOLOGIN` owner role for the `hr` schema; - `LOGIN` roles for the migration tool and the application, holding no attribute beyond `LOGIN`; - a `NOLOGIN` analysts group whose members are individual analyst logins; - a separate `LOGIN REPLICATION` role used only by standbys and base backups. Nothing that carries application traffic should hold `SUPERUSER` or `CREATEROLE`; those belong to a small number of administrative logins.

  • A PostgreSQL login is a member of a role that has CREATEDB, yet CREATE DATABASE fails. Why, and how does it succeed?
    Role attributes are never inherited, whatever the membership's `INHERIT` option says. The login must first run `SET ROLE` to the role that holds `CREATEDB`, which requires the membership to carry the `SET` option. The database it then creates is owned by that role, not by the login.
  • In PostgreSQL 18, what can a role with CREATEROLE but not SUPERUSER still not do?
    It cannot create superusers or alter existing ones, cannot create `REPLICATION` roles or grant or revoke `REPLICATION`, and cannot grant `BYPASSRLS`. It can alter and drop only roles on which it holds `ADMIN`; it gets that automatically, with `SET FALSE` and `INHERIT FALSE`, on roles it creates.

  • CREATE USER and CREATE ROLE create two different kinds of object.
  • Granting a superuser role to a login makes that login a superuser.
  • A role with CREATEROLE can create superusers or grant REPLICATION.
  • Roles are defined per database, so each database needs its own login.
  • A NOLOGIN group role can still be used as the user in a connection string.
open as a page

In PostgreSQL, why do tables created by a migration login stay unreadable to analysts after an admin ran ALTER DEFAULT PRIVILEGES GRANT SELECT ON TABLES?

level: middleimportance: must knowfreq 58%

basics

~20 s

ALTER DEFAULT PRIVILEGES applies only to objects later created by the role named in FOR ROLE, which defaults to whoever ran it. Tables the migration login creates use that login's own entries, so the admin's entry never fires.

open as a page

In PostgreSQL 16 and later, what do the INHERIT, SET and ADMIN options on a role membership control, and which combination fits a migration login?

level: middleimportance: should knowfreq 42%

basics

~20 s

INHERIT makes the granted role's privileges usable without switching, SET allows SET ROLE to it and making it an object's owner, and ADMIN allows granting membership onward. A migration login fits SET TRUE, INHERIT FALSE, ADMIN FALSE on the owner role.

open as a page

In PostgreSQL 18, what do the predefined roles pg_read_all_data and pg_maintain grant, and what is risky about giving them to analysts?

level: middleimportance: should knowfreq 37%

basics

~20 s

pg_read_all_data acts as SELECT on every table, view and sequence plus USAGE on every schema; pg_maintain (17+) allows VACUUM, ANALYZE, CLUSTER, REINDEX, REFRESH MATERIALIZED VIEW and LOCK TABLE on every relation. Both are cluster-wide and cannot be narrowed per table.

open as a page

In PostgreSQL, why is CREATE on the public schema combined with the default search_path a privilege-escalation risk, and what changed in PostgreSQL 15?

level: seniorimportance: should knowfreq 46%

basics

~20 s

With search_path "$user", public, anyone who can create objects in public can plant a function or operator that a privileged session's unqualified call resolves to. Since 15, new databases give PUBLIC only USAGE on public; upgraded databases keep CREATE until revoked.

open as a page

In PostgreSQL, DROP ROLE for a departed contractor fails because some objects depend on it; how do you remove the role safely across the cluster?

level: seniorimportance: should knowfreq 31%

basics

~20 s

In every database holding its objects, run REASSIGN OWNED BY the role TO a successor, then DROP OWNED BY the role to revoke its remaining grants and default-privilege entries; only then DROP ROLE, which is cluster-wide.

open as a page