Roles, membership and inheritance · PostgreSQL

PostgreSQL has no separate concept of a user and a group. There are only roles, and a role that can log in is what everyone calls a user. That uniformity is convenient and it means the distinctions that matter are elsewhere: in a role’s attributes, and in its memberships.

Attributes versus membership

Attributes are properties of the role itself: whether it can log in, create databases, create roles, bypass row-level security, or is a superuser.

Membership is a relationship between roles, and it is how privileges on tables and schemas are actually delivered.

Read-only / Safefive roles: three groups and two service accounts— Captured from a live 18.6 cluster. Only login distinguishes a group from a user.

$ psql -U postgres -c "SELECT rolname, rolcanlogin AS login, rolinherit AS inherit FROM pg_roles WHERE rolname LIKE 'app%' OR rolname LIKE 'svc%' ORDER BY rolname"

  rolname  | login | inherit
-----------+-------+---------
app_owner | f     | t
app_ro    | f     | t
app_rw    | f     | t
svc_rpt   | t     | f
svc_web   | t     | t
(5 rows)

app_owner, app_rw and app_ro have no LOGIN, so nothing can connect as them. They exist to hold privileges. svc_web and svc_rpt can log in and hold almost no privileges of their own.

Inheritance is a property of the grant

This is the part that surprises people. Whether a member automatically enjoys the privileges of a role it belongs to is recorded per grant, not on either role.

Read-only / Safetwo memberships, two different inherit options— svc_rpt was created NOINHERIT, and its grant records that. Captured live.

$ psql -U postgres -c 'SELECT r.rolname AS member, g.rolname AS member_of, m.admin_option, m.inherit_option, m.set_option 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 1'

 member  | member_of | admin_option | inherit_option | set_option
---------+-----------+--------------+----------------+------------
svc_rpt | app_ro    | f            | f              | t
svc_web | app_rw    | f            | t              | t
(2 rows)

Three independent options:

inherit_option — does the member get the privileges automatically? If false, it holds them only after SET ROLE.
set_option — may the member run SET ROLE to become the group at all?
admin_option — may the member grant this membership to others?

Since PostgreSQL 16 these are set per grant, so the same role can inherit one membership and not another:

GRANT app_ro TO svc_rpt WITH INHERIT FALSE;   -- must SET ROLE to use it
GRANT app_rw TO svc_web WITH INHERIT TRUE;    -- automatic
GRANT app_rw TO lead    WITH ADMIN OPTION;    -- may grant it onward

Diagnosing a NOINHERIT permission failure as a missing grant

An application reports permission denied for table orders. The obvious check is whether its role has the privilege, and the obvious query says no. So somebody grants it directly to the login role, the error stops, and the group structure has been quietly bypassed.

The membership was there all along. The role was NOINHERIT, so the privilege existed and was not active. The correct fixes are either to make the grant inheriting, or to have the application issue SET ROLE app_rw after connecting — which is a deliberate design in some estates, because it makes privilege use explicit and auditable.

The query that distinguishes the two cases asks what the role can actually do right now rather than what it is a member of:

SELECT has_table_privilege('svc_rpt', 'orders', 'SELECT') AS effective_now;

SELECT g.rolname AS member_of, m.inherit_option
  FROM pg_auth_members m
  JOIN pg_roles g ON g.oid = m.roleid
  JOIN pg_roles r ON r.oid = m.member
 WHERE r.rolname = 'svc_rpt';

A membership present with inherit_option = f and a false privilege check is the signature. Granting directly to the login role in that situation solves tonight and creates a privilege that no group structure describes, which is the thing that makes an estate’s permissions unreviewable over time.

A structure that survives change

The pattern that works, and the reasoning behind each layer:

-- 1. An owning role that holds the objects. No login, ever.
CREATE ROLE app_owner NOLOGIN;

-- 2. Access groups describing what may be done, not who may do it.
CREATE ROLE app_rw NOLOGIN;
CREATE ROLE app_ro NOLOGIN;

-- 3. Login roles for each service, holding no privileges of their own.
CREATE ROLE svc_web LOGIN PASSWORD 'set-a-real-secret';
CREATE ROLE svc_etl LOGIN PASSWORD 'set-a-real-secret';

-- 4. Membership connects them.
GRANT app_rw TO svc_web;
GRANT app_ro TO svc_etl;

Why each layer earns its place:

Objects owned by a role that cannot log in means no credential exists that owns the schema. Compromising a service account does not give the ability to drop tables, because ownership lives elsewhere.

Groups named for capability means privileges are granted once, to app_rw, rather than repeated per service. Adding a service is one GRANT, and removing one is one REVOKE with no chance of leaving stray privileges behind.

Login roles that hold nothing directly means the review question is “which groups is this member of”, which is answerable, rather than “what has been granted to this role across every schema”, which is not.

The test of the structure is a personnel or service change. If adding a new reporting service requires granting privileges on tables, the structure has been bypassed somewhere.

Predefined roles instead of superuser

PostgreSQL ships roles that grant specific administrative capabilities. Using them is how you avoid handing out superuser for a narrow need.

Read-only / Safethe predefined roles in PostgreSQL 18— EXECUTION VALIDATED on 18.6: SELECT rolname FROM pg_roles WHERE rolname LIKE 'pg\_%' returned these sixteen.

$ psql -U postgres -tAc "SELECT string_agg(rolname, ', ' ORDER BY rolname) FROM pg_roles WHERE rolname LIKE 'pg\_%'"

pg_checkpoint, pg_create_subscription, pg_database_owner,
pg_execute_server_program, pg_maintain, pg_monitor, pg_read_all_data,
pg_read_all_settings, pg_read_all_stats, pg_read_server_files,
pg_signal_autovacuum_worker, pg_signal_backend, pg_stat_scan_tables,
pg_use_reserved_connections, pg_write_all_data, pg_write_server_files

The ones that replace a superuser grant most often:

Role	Grants	Instead of
`pg_monitor`	Read every statistics view	Superuser for monitoring
`pg_read_all_data`	`SELECT` on everything	Superuser for a reporting tool
`pg_signal_backend`	Cancel and terminate other sessions	Superuser for an on-call role
`pg_maintain`	`VACUUM`, `ANALYZE`, `REINDEX`, `CLUSTER` on any relation	Superuser for a maintenance job
`pg_use_reserved_connections`	Use the reserved slots	Superuser for emergency access
`pg_checkpoint`	Run `CHECKPOINT`	Superuser for a backup script

An on-call role built from these covers most incident work:

CREATE ROLE oncall LOGIN PASSWORD 'set-a-real-secret';
GRANT pg_monitor, pg_signal_backend, pg_use_reserved_connections TO oncall;

That role can read every statistic, terminate a blocking session and connect when the cluster is saturated — the three things Part IV established as the incident-response minimum — and it cannot read application data, cannot change configuration, and cannot drop anything.

Three of those roles deserve a caution. pg_read_all_data and pg_write_all_data grant access to every table in every database in the cluster, which is broader than most reporting needs. pg_execute_server_program, pg_read_server_files and pg_write_server_files are effectively host access and are covered in the next lessons as superuser-equivalent.

Why SET ROLE exists, and what it changes about auditing

SET ROLE switches the privileges the session uses without changing who authenticated. Two identity functions expose the difference, and the distinction is the basis of database auditing.

session_user is the role that authenticated and never changes. current_user is the role whose privileges are in effect and follows SET ROLE.

SELECT session_user, current_user;   -- svc_rpt | svc_rpt
SET ROLE app_ro;
SELECT session_user, current_user;   -- svc_rpt | app_ro

Objects created after SET ROLE are owned by current_user, which is the mechanism behind the ownership pattern above: a deployment role can SET ROLE app_owner and create tables that belong to app_owner rather than to itself.

For auditing, the useful consequence is that session_user survives the switch, so a log or trigger recording both answers “who did this” and “under what authority” as separate questions. A design that relies solely on current_user loses the first, which is exactly the information an investigation needs.

The set_option on a membership controls whether SET ROLE is permitted at all, so a grant can deliver privileges automatically (INHERIT TRUE) while forbidding the member from deliberately assuming the group identity (SET FALSE), or the reverse. Those two options being independent is what allows a NOINHERIT design where every privilege use is an explicit, logged act.

Production discipline

Own objects with a NOLOGIN role. No credential should exist that can drop the schema.
Grant privileges to groups, never to login roles. The review question becomes answerable.
Check inherit_option before concluding a privilege is missing. A NOINHERIT membership looks identical to no membership until you look.
Use the predefined roles rather than superuser. pg_monitor, pg_signal_backend and pg_use_reserved_connections cover incident response between them.
Treat pg_read_all_data and the server-file roles as broad. The first reads every table in the cluster; the others are host access.
Record session_user as well as current_user in any audit trail. SET ROLE changes one and not the other, and the investigation needs both.

Cross-course references

Secrets, PKI & Certificate Management — Part XII (Secret management platforms) covers least-privilege policy design, and Part I (Foundations) covers computing blast radius, which is the same reasoning applied to a role.
Linux for Production Sysadmins — Part IV (Users) and Part V (Sudo) cover the host equivalents of groups and privilege escalation.
Kubernetes for Production Sysadmins — Part LVIII (RBAC) covers the same group-based structure in a cluster authorisation model.