Roles, Membership, Ownership, SET ROLE, and Privilege Inheritance

Learning outcomes

PostgreSQL does not have separate “user” and “group” object types. It has roles. A role can log in, own objects, receive privileges, be a member of other roles, or combine those properties. This unified model is powerful, but it becomes dangerous when teams collapse three different questions into one: who authenticated?, whose privileges are currently active?, and who owns this object?

ServiceHub already separates servicehub_owner (NOLOGIN) from servicehub_app (LOGIN). This lesson deepens that design using PostgreSQL 18 membership semantics. Since PostgreSQL 16, membership grants can independently control automatic inheritance, ability to SET ROLE, and ability to administer membership. Older tutorials that describe INHERIT only as one role-wide runtime switch are incomplete for current PostgreSQL.

1. One role model, several responsibilities

Do not equate “role is a member of owner role” with “role automatically owns everything.” Membership controls how privileges and role transitions are available. Ownership remains attached to the owning role.

2. Observe role attributes and memberships

\du\drg

In PostgreSQL 18, \drg displays granted-role memberships including membership options. For structured evidence, query publicly readable role/membership metadata:

SELECT rolname, rolcanlogin, rolinherit, rolsuper,       rolcreatedb, rolcreaterole, rolreplication, rolbypassrlsFROM pg_catalog.pg_rolesWHERE rolname LIKE 'servicehub%'ORDER BY rolname;SELECT am.roleid::regrole AS granted_role,       am.member::regrole AS member_role,       am.grantor::regrole AS grantor,       am.admin_option,       am.inherit_option,       am.set_optionFROM pg_catalog.pg_auth_members AS amWHERE am.roleid::regrole::text LIKE 'servicehub%'   OR am.member::regrole::text LIKE 'servicehub%'ORDER BY 1, 2;

Role identities and memberships are cluster-wide, not per-database. Object privileges and objects themselves are usually database-local. This is another reason database/schema/role boundaries must be documented separately.

3. session_user and current_user answer different questions

session_user identifies the role that authenticated the session (unless changed through the separate session-authorization mechanism). current_user is the effective SQL authorization identity used for most permission checks. Normally they are the same. SET ROLE changes current_user while retaining session_user.

Create a disposable group-like role and login for this demonstration:

CREATE ROLE ch03_writer NOLOGIN;CREATE ROLE ch03_operator LOGIN;GRANT CONNECT ON DATABASE servicehub_lab TO ch03_operator;GRANT ch03_writer TO ch03_operatorWITH INHERIT FALSE, SET TRUE;GRANT USAGE ON SCHEMA app TO ch03_writer;GRANT SELECT, INSERT, UPDATE ON app.work_orders TO ch03_writer;

Connect as ch03_operator using a disposable password set interactively with \password ch03_operator. Then observe:

SELECT session_user, current_user;SELECT has_table_privilege(current_user, 'app.work_orders', 'INSERT');SET ROLE ch03_writer;SELECT session_user, current_user;SELECT has_table_privilege(current_user, 'app.work_orders', 'INSERT');RESET ROLE;SELECT session_user, current_user;

Because this membership used INHERIT FALSE, the login does not automatically exercise ch03_writer's object privileges. Because it used SET TRUE, it can explicitly become that role. After SET ROLE, permission checks use the target role's privileges; privileges belonging only to the login role are not simply added on top.

4. INHERIT, SET, and ADMIN are independent membership controls

A PostgreSQL 18 role membership can carry three important options:

The role-level INHERIT/NOINHERIT attribute still exists, but on current PostgreSQL it acts as a default inheritance status for new memberships and role-creation clauses. The runtime membership decision is represented by each membership's inherit_option. This is a version-sensitive change from PostgreSQL before 16.

5. Role attributes are not ordinary inherited object privileges

Attributes such as CREATEDB, CREATEROLE, LOGIN, and SUPERUSER do not become usable merely because object privileges are inherited. If a member needs an attribute of another role and its membership allows SET, it must actually SET ROLE to the role that has that attribute.

This matters because “put the app in an admin group” is not a safe shortcut. A runtime login should not receive unnecessary membership chains that provide either inherited broad privileges or a SET path into high-privilege roles.

6. Ownership is not just another GRANT

The owner of an object has inherent authority to alter or drop it and to grant/revoke privileges on it. You cannot take that ownership power away with a normal REVOKE while leaving the same role as owner. This is why Chapter 01's servicehub_app can use application tables but does not own them.

SELECT n.nspname AS schema_name,       c.relname,       c.relkind,       c.relowner::regrole AS ownerFROM pg_catalog.pg_class AS cJOIN pg_catalog.pg_namespace AS n ON n.oid = c.relnamespaceWHERE n.nspname = 'app'  AND c.relname IN ('customers','technicians','work_orders')ORDER BY c.relname;SELECT has_table_privilege('servicehub_app', 'app.work_orders', 'SELECT') AS can_select,       has_table_privilege('servicehub_app', 'app.work_orders', 'INSERT') AS can_insert;

The application can have DML privileges while ownership remains servicehub_owner. If the application login owned the table, a compromised application connection could alter/drop it even if you attempted to revoke ordinary table privileges.

7. Deliberately wrong approach: make the runtime login the owner

A common small-project shortcut is CREATE DATABASE ... OWNER app_user followed by schema/table creation as that same login. It “works” because the application can do everything. It also means SQL injection or credential compromise potentially gains DDL ownership power.

The safer pattern is role separation:

• NOLOGIN owner roles own databases/schemas/tables.
• Runtime LOGIN roles receive only required object/schema/database privileges.
• Migration/administration logins receive an explicit, audited SET path to owner roles when necessary.
• Owner roles are not used for routine application connections.

This does not eliminate all risk—application DML privileges can still damage business data—but it narrows the control plane exposed through runtime credentials.

8. Negative test: SET FALSE means “membership but no role switch”

Create another teaching role that is inherited automatically but cannot be selected via SET ROLE:

CREATE ROLE ch03_readers NOLOGIN;GRANT SELECT ON app.customers TO ch03_readers;GRANT USAGE ON SCHEMA app TO ch03_readers;GRANT ch03_readers TO ch03_operatorWITH INHERIT TRUE, SET FALSE;

Reconnect as ch03_operator so your demonstration starts from a clean state. The inherited SELECT privilege should be available without changing current_user. But SET ROLE ch03_readers should fail because the membership explicitly disables SET. This proves that “member of” does not describe one single privilege behavior.

9. Hands-on lab: build a privilege-transition matrix

1. Create the two teaching roles/memberships above.
2. Record \drg output and the matching pg_auth_members rows.
3. As ch03_operator, test SELECT/INSERT before any SET ROLE.
4. SET ROLE ch03_writer, re-run the tests, and record session_user/current_user.
5. RESET ROLE, then prove the automatically inherited reader privilege remains available.
6. Attempt SET ROLE ch03_readers and capture the expected error.
7. As administrator, revoke memberships and drop the teaching roles after verification.

SELECT session_user, current_user,       pg_has_role(session_user, 'ch03_writer', 'MEMBER') AS member_writer,       pg_has_role(session_user, 'ch03_writer', 'USAGE') AS can_use_writer,       has_table_privilege(current_user, 'app.work_orders', 'INSERT') AS insert_work_orders,       has_table_privilege(current_user, 'app.customers', 'SELECT') AS select_customers;

The exact interpretation of pg_has_role modes is documented and can involve inheritance/SET semantics; do not replace direct positive/negative operation tests with one boolean and call authorization “proven.”

REVOKE ch03_writer FROM ch03_operator;REVOKE ch03_readers FROM ch03_operator;REVOKE CONNECT ON DATABASE servicehub_lab FROM ch03_operator;REVOKE SELECT, INSERT, UPDATE ON app.work_orders FROM ch03_writer;REVOKE USAGE ON SCHEMA app FROM ch03_writer;REVOKE SELECT ON app.customers FROM ch03_readers;REVOKE USAGE ON SCHEMA app FROM ch03_readers;DROP ROLE ch03_operator;DROP ROLE ch03_writer;DROP ROLE ch03_readers;

10. Production judgment and next bridge

Model role graphs as security architecture, not convenience. Keep owner roles non-login, minimize SET paths, minimize ADMIN options, audit indirect membership, and verify privileges using both metadata and actual allowed/denied operations. Record the PostgreSQL major because membership semantics changed materially in PostgreSQL 16.

Lesson 3 turns to PostgreSQL metadata itself: how psql, information_schema, pg_catalog, OIDs, and reg* identifier types answer questions about the objects and role model you have just built.

Authoritative references

• PostgreSQL 18 — Database Roles
• PostgreSQL 18 — Role Membership
• PostgreSQL 18 — SET ROLE
• PostgreSQL 18 — Privileges and Ownership
• PostgreSQL 18 — pg_roles
• PostgreSQL 18 — pg_auth_members
• PostgreSQL 18 — psql role introspection