Roles, Users, and Role Inheritance

Role

PostgreSQL's single access-control object. A role with LOGIN can open connections (a "user"); a role without LOGIN cannot connect but can hold privileges and be granted to other roles (a "group"). There is no structural difference between the two — LOGIN is just one of several boolean attributes a role can have.

INHERIT / NOINHERIT

Controls whether a role automatically uses the privileges of roles it is a member of. INHERIT (the default since PostgreSQL 16 for roles created without specifying it, and always the default for CREATE ROLE) applies them immediately on every query. NOINHERIT requires an explicit SET ROLE before those privileges become active for the session.

SET ROLE

Changes the effective role (current_user) for the rest of the session, or until RESET ROLE. Any role may SET ROLE to a role it is a member of; a superuser may SET ROLE to anything. It is the standard way to require an explicit, visible elevation step instead of granting broad privileges as the default state of a connection.

🔌 Connect as postgres

Connect as the postgres superuser. Creating and granting roles requires superuser (or CREATEROLE) privileges.

psql -U postgres

SET psql (18.4) Type "help" for help. postgres=#

📋 Survey the Baseline

Before adding anything, check what roles already exist on this cluster.

SELECT rolname, rolcanlogin, rolinherit, rolsuper FROM pg_roles WHERE rolname !~ '^pg_' ORDER BY rolname;

rolname | rolcanlogin | rolinherit | rolsuper ------------+-------------+------------+---------- beer_db | t | t | f postgres | t | t | t social_db | t | t | f tv_db | t | t | f weather_db | t | t | f (5 rows)

🏗️ Create the Three Group Roles

Create three NOLOGIN roles representing job functions: analyst (read-only), app_writer (DML only), and dba_admin (schema changes). None of these can connect on their own — they exist only to hold privileges.

CREATE ROLE analyst NOLOGIN;

CREATE ROLE app_writer NOLOGIN;

CREATE ROLE dba_admin NOLOGIN;

CREATE ROLE CREATE ROLE CREATE ROLE

👤 Create jane_analyst

Create a LOGIN role for an individual analyst and grant her membership in the analyst group.

CREATE ROLE jane_analyst LOGIN PASSWORD 'analyst_pw1' INHERIT;

GRANT analyst TO jane_analyst;

CREATE ROLE GRANT ROLE

✍️ Create mike_writer

Create a LOGIN role for an application service account and grant it membership in app_writer.

CREATE ROLE mike_writer LOGIN PASSWORD 'writer_pw1' INHERIT;

GRANT app_writer TO mike_writer;

CREATE ROLE GRANT ROLE

🛠️ Create sara_dba

Create a LOGIN role for an administrator and grant her membership in dba_admin.

CREATE ROLE sara_dba LOGIN PASSWORD 'dba_pw1' INHERIT;

GRANT dba_admin TO sara_dba;

CREATE ROLE GRANT ROLE

🕸️ Read the Membership Graph

Query pg_auth_members to see the full membership graph you just built, joined against pg_roles for readable names.

SELECT r.rolname AS member, g.rolname AS member_of FROM pg_auth_members m JOIN pg_roles r ON r.oid = m.member JOIN pg_roles g ON g.oid = m.roleid ORDER BY g.rolname;

postgres=# SELECT r.rolname AS member, g.rolname AS member_of postgres-# FROM pg_auth_members m postgres-# JOIN pg_roles r ON r.oid = m.member postgres-# JOIN pg_roles g ON g.oid = m.roleid postgres-# ORDER BY g.rolname; member | member_of --------------+---------------------- jane_analyst | analyst mike_writer | app_writer sara_dba | dba_admin pg_monitor | pg_read_all_settings pg_monitor | pg_read_all_stats pg_monitor | pg_stat_scan_tables (6 rows)

✅ Verify INHERIT: sara_dba Can Act Immediately

Grant CREATE on the public schema of beer_db to dba_admin, then connect as sara_dba and create a table directly — no SET ROLE needed, because sara_dba was created with INHERIT.

\q

psql -U postgres -d beer_db

GRANT CREATE ON SCHEMA public TO dba_admin;

\q

psql -U sara_dba -d beer_db

CREATE TABLE incident_log (id serial PRIMARY KEY, note text);

postgres=# \q student@lab:~$ psql -U postgres -d beer_db SET psql (18.4) Type "help" for help. beer_db=# GRANT CREATE ON SCHEMA public TO dba_admin; GRANT beer_db=# \q student@lab:~$ psql -U sara_dba -d beer_db SET psql (18.4) Type "help" for help. beer_db=> CREATE TABLE incident_log (id serial PRIMARY KEY, note text); CREATE TABLE

🚫 Verify Least Privilege: jane_analyst Cannot

Connect as jane_analyst and attempt the same CREATE TABLE. The analyst group has no CREATE grant, so it must fail.

\q

psql -U jane_analyst -d beer_db

CREATE TABLE analyst_scratch (id int);

beer_db=> \q student@lab:~$ psql -U jane_analyst -d beer_db SET psql (18.4) Type "help" for help. beer_db=> CREATE TABLE analyst_scratch (id int); ERROR: permission denied for schema public LINE 1: CREATE TABLE analyst_scratch (id int); ^

🔒 NOINHERIT: Membership Without Automatic Privilege

Create a NOINHERIT login role, grant it dba_admin membership, and try CREATE TABLE immediately — it must fail even though the role is a member, because NOINHERIT leaves the privilege dormant.

\q

psql -U postgres -d beer_db

CREATE ROLE tom_noinherit LOGIN PASSWORD 'noinherit_pw1' NOINHERIT;

GRANT dba_admin TO tom_noinherit;

\q

psql -U tom_noinherit -d beer_db

CREATE TABLE noinherit_test (id int);

beer_db=> \q student@lab:~$ psql -U postgres -d beer_db SET psql (18.4) Type "help" for help. beer_db=# CREATE ROLE tom_noinherit LOGIN PASSWORD 'noinherit_pw1' NOINHERIT; CREATE ROLE beer_db=# GRANT dba_admin TO tom_noinherit; GRANT ROLE beer_db=# \q student@lab:~$ psql -U tom_noinherit -d beer_db SET psql (18.4) Type "help" for help. beer_db=> CREATE TABLE noinherit_test (id int); ERROR: permission denied for schema public LINE 1: CREATE TABLE noinherit_test (id int); ^

🔓 SET ROLE: Explicit, Auditable Elevation

As tom_noinherit, run SET ROLE dba_admin to explicitly claim the group's privileges, retry the CREATE TABLE, then RESET ROLE to drop back down.

SET ROLE dba_admin;

CREATE TABLE noinherit_test (id int);

RESET ROLE;

SET CREATE TABLE RESET