skip to content

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%

answer

  1. three booleans per membership
  2. use privileges versus become the role
  3. ownership transfers need SET
  4. pg_auth_members option columns

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.

solid answer

~50 s

Since 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 lines
sql
GRANT 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

for a junior

Recall that a membership grant has three options, INHERIT, SET and ADMIN, and that SET ROLE switches the session to another role.

for a middle

Explain the defaults, how inheritance stops at an INHERIT FALSE link, and why ALTER ... OWNER TO needs the SET option on the target role.

for a senior

Design owner, migration and application roles with explicit options, and explain why inherit-without-SET is unsafe when the granted role owns objects.

for a principal

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.