Managing Role Membership

1. Granting Role to User

GRANT app_reader TO alice;

GRANT app_reader, app_writer TO alice, bob;

GRANT app_reader TO ops_lead WITH ADMIN OPTION;

Note: ADMIN OPTION lets the grantee grant/revoke the role to/from others.

REVOKE app_writer FROM alice;
REVOKE ADMIN OPTION FOR app_reader FROM ops_lead;

CREATE ROLE alice INHERIT;       -- picks up privileges automatically
CREATE ROLE bob   NOINHERIT;     -- must SET ROLE explicitly

SET ROLE app_writer;
-- subsequent commands run with app_writer's privileges

RESET ROLE;          -- back to session user

SELECT current_user, session_user, current_role;

Function	Meaning
session_user	Login role (immutable)
current_user	Effective role (SET ROLE changes this)

SELECT r.rolname, m.rolname AS member
  FROM pg_auth_members am
  JOIN pg_roles r ON r.oid = am.roleid
  JOIN pg_roles m ON m.oid = am.member;

CREATE ROLE reports_team NOLOGIN;
GRANT SELECT ON ALL TABLES IN SCHEMA reporting TO reports_team;
GRANT reports_team TO alice, bob, carol;