In PostgreSQL, what do the LOGIN, SUPERUSER, CREATEROLE and REPLICATION role attributes allow, and why doesn't role membership pass them on?
answer
- one cluster-wide kind of principal
- CREATE USER differs by one default
- flags on the role, not ACL entries
- SET ROLE to use an attribute
- CREATEROLE needs ADMIN on its target
basics
~20 sLOGIN 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 sIn 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 linesCREATE 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
Know that users and groups are both roles, that CREATE USER only adds LOGIN, and what SUPERUSER, CREATEDB, CREATEROLE and REPLICATION each permit.
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.
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.
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.