skip to content

When one database role is granted membership in another, do the member's sessions automatically get the granted role's privileges? Explain how role inheritance and role activation work.

level: middleimportance: should knowfreq 40%

answer

  1. membership = 'may act as'; inheritance = 'automatically does'
  2. PostgreSQL INHERIT default; NOINHERIT forces SET ROLE
  3. Oracle: default vs non-default roles, enable with SET ROLE
  4. attributes (superuser, CREATEROLE) are not inherited like grants
  5. SET ROLE is reversible - elevation, not sandboxing

basics

~20 s

Not always. With inheritance, a member automatically uses the granted role's privileges in every session. Without it, membership only grants the right to switch into that role explicitly before its privileges apply. Engines differ: PostgreSQL roles are inheriting by default unless created NOINHERIT; Oracle distinguishes default roles from ones that must be enabled.

solid answer

~50 s

Membership means 'may act as'; whether that happens automatically is a separate setting. - **Inheriting membership** - the member's sessions carry the granted role's privileges implicitly, transitively through the whole membership graph. This is PostgreSQL's default (`INHERIT`). - **Non-inheriting membership** - the member holds the right to assume the role but must do so explicitly (`SET ROLE`) before the privileges apply. PostgreSQL gives this with `NOINHERIT`; Oracle achieves something similar by making a role non-default so it must be enabled with `SET ROLE`. Why the distinction matters: non-inheriting membership implements privilege *elevation*. A DBA can run day-to-day with ordinary rights and step up deliberately for a destructive action, so an accidental `DROP` is not one typo away. It also gives audit a clear elevation event. One important exception in most engines: privileges that are role *attributes* rather than grants - superuser status, the ability to create roles - are not inherited the same way and require actually becoming the role.

code

sql · 10 lines
sql
CREATE ROLE schema_admin NOLOGIN;
GRANT ALL ON SCHEMA app TO schema_admin;

CREATE ROLE dana LOGIN NOINHERIT PASSWORD '...';
GRANT schema_admin TO dana;

-- dana's normal session cannot use schema_admin's rights:
SET ROLE schema_admin;   -- deliberate elevation, visible in the audit log
-- ... privileged work ...
RESET ROLE;

go deeper

for a junior

Know that membership means a role's privileges can apply to the member, and that some setups require an explicit switch before they do.

for a middle

Contrast inheriting and non-inheriting membership, name the mechanism (SET ROLE), and give the accident-prevention reason for making admin roles non-inheriting.

for a senior

Add what does not inherit (role attributes), the audit value of an explicit elevation event, session versus current role in audit records, and reset discipline on pooled connections.

for a principal

Design the hierarchy deliberately: which roles inherit, where elevation boundaries sit, how elevation events feed auditing and alerting, and how this interacts with automation and break-glass access.

## Membership is a graph Granting role B to role A creates an edge: A is a member of B. Membership is transitive - if A is a member of B and B of C, A effectively reaches C's privileges too (subject to inheritance rules). The graph is normally kept acyclic; engines reject cycles. The design question is what membership *does* at session time. Two models: ## Model 1: automatic inheritance The session's effective privilege set is the union of the login role's own privileges and everything reachable through membership. Nothing extra is needed - if `alice` is a member of `analyst` and `analyst` can read `orders`, alice reads `orders`. This is PostgreSQL's default: roles are created `INHERIT` unless stated otherwise. It is convenient and matches most people's intuition, and it is the right default for application and analyst roles. ## Model 2: explicit activation Membership grants only the *capability to assume* the role. Until the session issues `SET ROLE target`, the privileges are not in effect. PostgreSQL gets this by creating the member role `NOINHERIT`. Oracle's mechanism is different in shape but similar in spirit: granted roles may be default (enabled at login) or non-default (must be enabled explicitly with `SET ROLE` in the session); Oracle also has secure application roles that can only be enabled by a package that checks conditions first. Why anyone wants this: - **Accident prevention.** An operator whose everyday session lacks destructive rights cannot destroy anything by mistyping. Elevation is a deliberate act. - **Audit clarity.** The elevation itself is an event. 'At 02:14 this session assumed `schema_admin`' is a far better audit record than 'this session always had those rights'. - **Blast-radius reduction for automated jobs.** A job that assumes elevated rights for one step and drops back limits the window in which anything on that connection is privileged. ## What is not inherited A subtlety candidates miss: **role attributes are not object privileges**. Superuser status, `CREATEDB`, `CREATEROLE`, `BYPASSRLS` and similar are properties of a role, not grants on objects, and being a member of a role that has them does not give you them implicitly - you must actually become that role (`SET ROLE`) for them to take effect, and in some cases they do not transfer at all. So 'I inherited from a superuser role, therefore I am a superuser' is wrong in the general case. Ownership is another case that behaves specially: members of an owning role can typically exercise owner rights (including DDL) on the owned objects because ownership checks consider role membership - which is exactly why granting membership in an owner role to an application account is a large, quiet privilege expansion. ## SET ROLE and reversibility `SET ROLE` changes the current role for privilege checks; `RESET ROLE` (or `SET ROLE NONE`) returns to the login identity. Critically, it is **reversible**: switching to a lower-privileged role is not a sandbox, because the session can switch back at will. Most engines also distinguish the *session* identity (who authenticated) from the *current* identity (who privileges are checked against) and expose both, which matters for audit triggers - recording only the current role hides who really connected. ## Practical consequences - On a pooled connection, any `SET ROLE` must be reset before the connection returns to the pool, or the next borrower inherits the elevated identity. - Debugging 'permission denied' should start with the effective privilege path: is the role granted, does the member inherit, and has the role been activated in this session? Engines expose helper functions and catalog views for exactly this. - When designing a hierarchy, be explicit about which roles are inheriting. A common shape is: everyday roles inherit; administrative and destructive roles are non-inheriting so they must be assumed. ## Version and engine notes PostgreSQL 16 made membership grants themselves carry options (`WITH INHERIT`, `WITH SET`, `WITH ADMIN`), so inheritance can be decided per grant rather than only as an attribute of the member role - useful when one role should inherit from one parent but only be able to assume another. Oracle's default-role list and secure application roles cover comparable ground with different syntax. Always state which engine's semantics you are describing.

  • An account is a member of a superuser role but is not itself a superuser. Does it act with superuser rights?
    Not implicitly. Superuser is a role attribute, not an object privilege, so it does not flow through inheritance the way a GRANT does - the session must actually become that role with SET ROLE for the attribute to apply. This is why 'member of a privileged role' and 'has that role's attributes' are different statements, though the membership is still a serious escalation path and should be treated as such.
  • Why is SET ROLE not a safe way to sandbox untrusted SQL?
    Because it is reversible: the session's authenticated login identity is unchanged, so anything that can execute SQL on that session can issue RESET ROLE and regain the original privileges. It is a mechanism for deliberate scoping and elevation by cooperating code, not a containment boundary against hostile statements - for that you need a genuinely less-privileged login or a separate connection.

saying these in an interview costs you the question

  • Assuming that being granted a role always activates its privileges - engines and role attributes differ.
  • Believing role attributes such as superuser or CREATEROLE are inherited exactly like object grants.
  • Treating SET ROLE to a weaker role as a sandbox, when the session can simply switch back.
  • Forgetting to RESET ROLE before a pooled connection is returned, leaving the next request elevated.

context

resolving links…