2. Capability roles - pgroles docs

Acme launches an automated reporting service. It needs exactly the access Alice already has, but copying Alice’s grants onto another login will make every team change harder to audit. And this time nobody suggests reusing the admin credentials—reporting_app becomes the first login at Acme whose access is actually designed.

Chapter 2 · Capability roles

PostgreSQL 18.3 · PGlite ↗

A reporting application joins Alice

A second consumer needs the same access. Name the capability once instead of copying grants onto every login.

Chapter 2 · Capability roles

PG 18.3

Step 1 of 3

Every step seeds its own database — jump anywhere

Step 1

Create orders_reader

The permission bundle is a job, not a person. A NOLOGIN role gives that job a durable name and keeps object ACLs independent from staff changes. Notice what this migration does not do: nobody thinks to revoke Alice’s Chapter 1 grants — teams rarely do.

Move the reusable grants onto orders_reader and make both consumers members.

Run as

CREATE ROLE orders_reader;
GRANT USAGE ON SCHEMA app TO orders_reader;
GRANT SELECT ON app.orders TO orders_reader;
GRANT orders_reader TO alice, reporting_app;

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.

Put privileges on jobs, not people

orders_reader cannot log in. It names one capability: reach app, then read app.orders. Alice and reporting_app receive that capability through membership.

pgroles.yamlpolicy schema

SectionFieldRole / profile / settingObjectPrivilege / typeUnknown

roles:
  - name: alice
    login: true
  - name: reporting_app
    login: true
  - name: orders_reader

grants:
  - role: orders_reader
    privileges: [USAGE]
    object: { type: schema, name: app }
  - role: orders_reader
    privileges: [SELECT]
    object: { type: table, schema: app, name: orders }

memberships:
  - role: orders_reader
    members:
      - name: alice
      - name: reporting_app

The lesson deliberately left Alice’s original direct grants in the database. The desired policy no longer declares them. That difference becomes the bug in the next chapter—and the reason a declarative plan is more useful than a pile of successful GRANT statements.

A membership adds a path; it does not erase any path that already exists.

Guide

Continue: drift

Change the team and watch an old direct grant defeat the intended offboarding.

Open guide

Guide

Memberships reference

See pgroles membership syntax and reconciliation behavior.

Open guide