3. Role Membership

Role membership allows organizing roles into hierarchies where child roles can inherit privileges from parent roles.

3.1. GRANT Role Membership

Grants membership in a role to another role.

Syntax:

GRANT role_name [, ...] TO role_name [, ...]
  [ WITH ADMIN OPTION ]
  [ WITH INHERIT OPTION ]
  [ WITH SET OPTION ]
  [ GRANTED BY grantor_role ]

Examples:

-- Basic membership grant
GRANT developers TO alice;

-- Grant multiple roles to multiple members
GRANT readers, writers TO alice, bob, charlie;

-- Grant with admin option (member can grant this role to others)
GRANT team_lead TO alice WITH ADMIN OPTION;

-- Grant with all options (PostgreSQL 16+ style)
GRANT senior_developer TO alice
  WITH ADMIN OPTION
  WITH INHERIT OPTION
  WITH SET OPTION;

-- Grant without inheritance (member must SET ROLE to use privileges)
GRANT admin_role TO alice WITH INHERIT FALSE;

-- Grant without SET option (member cannot SET ROLE to this role)
GRANT restricted_role TO alice WITH SET FALSE;

3.2. REVOKE Role Membership

Removes membership in a role from another role.

Syntax:

REVOKE [ ADMIN OPTION FOR ] role_name [, ...] FROM role_name [, ...]
  [ GRANTED BY grantor_role ]
  [ CASCADE | RESTRICT ]

Examples:

-- Basic membership revoke
REVOKE developers FROM alice;

-- Revoke multiple roles from multiple members
REVOKE readers, writers FROM alice, bob;

-- Revoke only the admin option (keep membership)
REVOKE ADMIN OPTION FOR team_lead FROM alice;

-- Revoke with cascade (also revokes from roles that got it via alice)
REVOKE developers FROM alice CASCADE;

3.3. Membership Options

Example: Options in Action

-- Create role hierarchy
CREATE ROLE base_privileges;
CREATE ROLE extended_privileges;
CREATE USER alice LOGIN PASSWORD 'password';

-- Grant base privileges with inheritance (automatic)
GRANT base_privileges TO alice;  -- alice inherits immediately

-- Grant extended privileges without inheritance
GRANT extended_privileges TO alice WITH INHERIT FALSE;
-- alice must do: SET ROLE extended_privileges; to use these

-- Grant without SET option
CREATE ROLE audit_role;
GRANT audit_role TO alice WITH SET FALSE;
-- alice inherits but CANNOT do: SET ROLE audit_role;

3.4. Inheritance Behavior

When INHERIT is enabled (default), a role automatically receives all privileges of its member roles without needing to use SET ROLE.

-- Setup
CREATE ROLE read_access;
CREATE ROLE write_access;
CREATE USER alice INHERIT PASSWORD 'pass';  -- INHERIT is default

GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_access;
GRANT INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO write_access;

-- alice is member of both roles
GRANT read_access TO alice;
GRANT write_access TO alice;

-- alice can now SELECT, INSERT, UPDATE without SET ROLE
-- because INHERIT is enabled
SELECT * FROM some_table;  -- Works
INSERT INTO some_table VALUES (...);  -- Works

Without Inheritance:

CREATE USER bob NOINHERIT PASSWORD 'pass';
GRANT read_access TO bob;

-- bob must explicitly switch roles
SET ROLE read_access;  -- Now bob can SELECT
SELECT * FROM some_table;  -- Works
RESET ROLE;  -- Back to bob's own privileges

---