7. Membership mechanics

The core Acme story needed only one membership edge and one migration recipe. Now take the edge itself apart: nested roles, automatic privilege flow, deliberate role switching, and delegation.

Follow one directed graph

bob (LOGIN)
      │ member of
      ▼
analyst (NOLOGIN)
      │ member of
      ▼
orders_reader (NOLOGIN) ──> schema USAGE + table SELECT

Read GRANT orders_reader TO analyst as analyst becomes a member of orders_reader. Reversing the names reverses the privilege flow—the lab lets you make that mistake and watch the chain break.

Chapter 7 · Membership mechanics

PostgreSQL 18.3 · PGlite ↗

Inside the membership edge

Nest Bob behind an analyst job role, then take one membership edge apart: automatic inheritance, deliberate SET ROLE, and delegated administration.

Chapter 7 · Membership mechanics

PG 18.3

Step 1 of 8

Every step seeds its own database — jump anywhere

Step 1

Nest the reporting membership

Acme now has several reader capabilities, so it introduces an analyst job role between the person and the capability. Membership is transitive: privileges can travel more than one edge.

Insert analyst between Bob and orders_reader, and remove Bob’s direct edge.

Run as

GRANT orders_reader TO analyst;
GRANT analyst TO bob;
REVOKE orders_reader FROM bob;

Editable SQL · disposable browser database · changes are discarded after the run

PostgreSQL output

PostgreSQL’s rows, command tags, or exact error will appear here.

How the browser lab models roles

The selector sets session authorization inside an isolated PGlite database. Statements run one at a time and autocommit, like psql: execution stops at the first error, and earlier statements keep their effect. PostgreSQL performs ordinary role, ownership, schema, and object checks; the diagram comes from catalog privilege queries after your SQL. This is not a password, CONNECT, pg_hba.conf, or concurrent-session test.

Declare the edges

pgroles.yamlpolicy schema

SectionFieldRole / profile / settingObjectPrivilege / typeUnknown

roles:
  - name: bob
    login: true
  - name: dana
    login: true
  - name: team_lead
    login: true
  - name: analyst
  - name: orders_reader

memberships:
  - role: orders_reader
    members:
      - name: analyst
  - role: analyst
    members:
      - name: bob
        inherit: false
      - name: dana
      - name: team_lead
        inherit: false
        admin: true

INHERIT answers whether ordinary privileges flow automatically. SET answers whether the member may become the granted role. ADMIN answers whether the member may grant or revoke that membership for others. These are three separate facts.

SET is outside the pgroles model

PostgreSQL 16 and later stores INHERIT, SET, and ADMIN per membership. pgroles manages inherit and admin, but does not inspect or converge SET. A managed edge receives PostgreSQL’s default SET TRUE; do not rely on SET FALSE remaining a security boundary on that edge.

Delegated administration and desired-state reconciliation also answer different questions. When the team lead grants analyst to Dana in PostgreSQL, the access is real immediately—but if that edge is absent from policy, the next authoritative pgroles plan treats it as drift. Durable delegation needs a workflow that writes the approved membership back to policy.

Guide

Continue: security review

Audit PUBLIC, SECURITY DEFINER, and delegated grant options.

Open guide

Guide

Memberships reference

See the complete policy and version behavior.

Open guide