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?
answer
- three booleans per membership
- use privileges versus become the role
- ownership transfers need SET
- pg_auth_members option columns
basics
~20 sINHERIT 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.
solid answer
~50 sSince PostgreSQL 16 each membership row carries three options. `INHERIT TRUE` lets the member use the role's privileges without switching; if unspecified it copies the member's `INHERIT` attribute, which is on by default. `SET TRUE`, the default, lets the member `SET ROLE` to the role, and that ability is also what `ALTER ... OWNER TO` and creating objects owned by the role require. `ADMIN`, false by default, lets the member grant and revoke membership in the role. For an HR SaaS I give the migration login `GRANT hr_owner TO hr_migrator WITH INHERIT FALSE, SET TRUE`: each migration starts with `SET ROLE hr_owner`, so every table is owned by `hr_owner`, while a plain `hr_migrator` session carries no owner rights. The app gets a privilege group with `INHERIT TRUE, SET FALSE`, which is safe only because that group owns nothing.
code
sql · 12 linesGRANT hr_owner TO hr_migrator WITH INHERIT FALSE, SET TRUE;
GRANT hr_rw TO hr_app WITH INHERIT TRUE, SET FALSE;
-- inside every migration run by hr_migrator
SET ROLE hr_owner;
CREATE TABLE hr.leave_requests (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
employee_id bigint NOT NULL,
starts_on date NOT NULL,
ends_on date NOT NULL
);
RESET ROLE;go deeper
Recall that a membership grant has three options, INHERIT, SET and ADMIN, and that SET ROLE switches the session to another role.
Explain the defaults, how inheritance stops at an INHERIT FALSE link, and why ALTER ... OWNER TO needs the SET option on the target role.
Design owner, migration and application roles with explicit options, and explain why inherit-without-SET is unsafe when the granted role owns objects.
Decide where SET ROLE is worth its blast radius across teams, and how ADMIN delegation and createrole_self_grant fit an organisation's role-administration model.
## A membership is a grant with three options In PostgreSQL a role becomes a member of another with `GRANT role TO member`. Since PostgreSQL 16 every such grant, one row in the `pg_auth_members` catalog, carries three booleans: `admin_option`, `inherit_option` and `set_option`. You set them with `WITH { ADMIN | INHERIT | SET } { TRUE | FALSE }` (`OPTION` is a synonym for `TRUE`, so `WITH ADMIN OPTION` still works). To change an existing membership, re-run the `GRANT` with new values (an omitted option keeps its current value) or use `REVOKE INHERIT OPTION FOR hr_owner FROM hr_migrator`. | Option | TRUE means | Default on a new grant | |---|---|---| | `INHERIT` | the member uses the role's privileges without switching | the member's `INHERIT` attribute (on unless the member is `NOINHERIT`) | | `SET` | the member may `SET ROLE` to the role | `TRUE` | | `ADMIN` | the member may grant and revoke membership in the role | `FALSE` | Before 16 there were no per-grant options for inheritance or `SET`: whether privileges flowed depended only on the member's `INHERIT` attribute. ## INHERIT: privileges without switching Privileges flow along a chain of grants only while every link has `INHERIT TRUE`. Suppose `hr_lead` holds `hr_payroll WITH INHERIT TRUE`, and `hr_payroll` holds `hr_owner WITH INHERIT FALSE`. A session of `hr_lead` uses its own privileges and those of `hr_payroll`, but not those of `hr_owner`, although it is an indirect member of it. Role attributes such as `CREATEDB` are never inherited, whatever this option says. ## SET: becoming the role, and why ownership needs it `SET ROLE hr_owner` swaps the session's privileges for `hr_owner`'s, and objects created afterwards are owned by `hr_owner`. For an indirect role, `SET ROLE` works only if every grant on the chain has `SET TRUE`. The same ability governs ownership: to create an object owned by another role, or to give an existing object to it with `ALTER ... OWNER TO`, you must be able to `SET ROLE` to that role, so with `SET FALSE` those commands fail. One caveat matters when hardening. `INHERIT TRUE, SET FALSE` is not a wall if the granted role owns objects: a member using the owner's privileges can redefine, for example, a function that role owns and turn it into a Trojan horse that runs as whoever calls it. The `GRANT` reference therefore advises that a role whose privileges should be inherited but not assumable should own no SQL objects. ## ADMIN: administering the membership `ADMIN` lets the member grant the role to others and revoke it. Superusers can always do this, and a role never holds `ADMIN` on itself. `CREATEROLE` is built on it: - a `CREATEROLE` role that is not a superuser can manage only roles it holds `ADMIN` on; - it receives an automatic `WITH ADMIN TRUE, SET FALSE, INHERIT FALSE` grant on every role it creates; - the `createrole_self_grant` setting (default empty) can add `set` and/or `inherit` to that automatic grant, which is a convenience: the creator could grant itself those options anyway. `GRANTED BY` records an explicit grantor, which must itself hold `ADMIN` on the role. ## A layout for an HR SaaS 1. `hr_owner` is `NOLOGIN` and owns schema `hr` and every table in it. 2. `hr_migrator` (`LOGIN`) gets `GRANT hr_owner TO hr_migrator WITH INHERIT FALSE, SET TRUE`. Every migration begins with `SET ROLE hr_owner`, so new objects are always owned by `hr_owner`, and an ordinary `hr_migrator` session uses no owner rights until it switches. 3. `hr_rw` (`NOLOGIN`) holds DML privileges on the `hr` tables and owns nothing. `hr_app` gets `GRANT hr_rw TO hr_app WITH INHERIT TRUE, SET FALSE`, since the application never needs to act as the group. 4. `hr_analysts` holds `SELECT`; analyst logins are members with `INHERIT TRUE, SET FALSE`. 5. Only the platform team's administrative login holds `ADMIN` on these roles. ## Inspecting memberships - psql's `\drg` lists every membership with its `ADMIN`, `INHERIT` and `SET` options and the grantor. - `pg_auth_members` holds the same data in `admin_option`, `inherit_option` and `set_option`. - `pg_has_role('hr_app', 'hr_rw', 'USAGE')` answers whether the privileges are usable without `SET ROLE`, and the `'SET'` mode whether switching is possible. The practical rule: grant `INHERIT` where a login should simply have the group's rights, grant `SET` only where the login must act as, or create objects for, the role, and keep `ADMIN` with the few people who administer memberships.
- In PostgreSQL 18, a CREATEROLE user created role hr_analyst_kim but cannot SET ROLE to it. Why, and what can it do about it?The automatic grant a non-superuser `CREATEROLE` creator receives is `ADMIN TRUE, SET FALSE, INHERIT FALSE`. Because it holds `ADMIN`, it can grant the role to itself again `WITH SET TRUE`; `createrole_self_grant` can make that automatic. The manual calls the default a safeguard against accidents, not a security feature.
- How do you change an existing PostgreSQL membership's options without revoking the membership?Re-run the grant with the new values, for example `GRANT hr_owner TO hr_migrator WITH INHERIT FALSE`; options you omit keep their current values. To clear a single option, use `REVOKE INHERIT OPTION FOR hr_owner FROM hr_migrator`. Check the result with psql's `\drg`.
INHERIT is your badge opening the doors the team's badge opens; SET is permission to put on the team's uniform and act as the team, so whatever you build is signed in its name; ADMIN is the right to issue team badges to others.
- INHERIT FALSE on a membership also stops the member from using SET ROLE.
- With SET FALSE a member can still ALTER a table's owner to that role.
- INHERIT TRUE with SET FALSE is a safe boundary even if the role owns functions.
- ADMIN option is granted by default so members can add colleagues.
- Privileges inherit through any chain of memberships regardless of each grant's options.
Assumes PostgreSQL 18; per-grant INHERIT and SET options arrived in 16, and before that inheritance followed only the member's INHERIT attribute.