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. The service uses reporting_app, a dedicated, scoped application login.

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 Run starts from this step’s prepared database. Changes from previous runs are discarded.

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.

PostgreSQL also supplies predefined capabilities, but they serve different jobs. pg_read_all_data and pg_write_all_data provide broad data access across schemas, without bypassing row-level security; they are much broader than this application's need to read only app.orders. A monitoring agent may appropriately use pg_monitor or one of its narrower monitoring roles to inspect database activity without gaining blanket access to business tables. Choose a capability by the job it must do. The security review compares these categories with the special database-owner role and higher-risk server capabilities.

Merge these role and membership entries into chapter 1's policy, retaining Alice and the external Priya definition. Replace Alice's direct grants with the capability grants below. The complete desired policy makes that replacement; the SQL lab deliberately leaves the old grants behind for the next investigation.

Policy fragmentPolicy field guide

SectionFieldRole / profile / settingObjectPrivilege / typeUnknown

roles:
  - 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.

Download the complete policy after this chapter.

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