5. Future objects - pgroles docs

Refunds launches. Deploy creates app.refunds, but the report immediately fails. Nothing removed the working grants on orders; the new object simply never received them.

Chapter 5 · Future objects

PostgreSQL 18.3 · PGlite ↗

A new refunds table breaks the report

Existing wildcard grants are a snapshot. A migration created by the wrong role reveals that ownership and default privileges must line up.

Chapter 5 · Future objects

PG 18.3

Step 1 of 4

Every step seeds its own database — jump anywhere

Step 1

Create refunds as deploy

This is the tempting migration: deploy skips the SET ROLE recipe and creates the table as itself. The object is new, so the old orders grant says nothing about it.

Create the refunds table as deploy.

Run as

CREATE TABLE app.refunds (
  id bigint PRIMARY KEY,
  order_id bigint NOT NULL,
  total_cents bigint NOT NULL
);
INSERT INTO app.refunds VALUES (1, 1, 500);

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.

Existing objects and future objects are separate problems

A wildcard object grant covers matching objects that exist when reconciliation runs.
A default privilege changes what a particular owner grants when that owner creates a future object.
The migration must create the object as the same owner named by the default privilege.

The policy now pairs both halves:

pgroles.yamlpolicy schema

SectionFieldRole / profile / settingObjectPrivilege / typeUnknown

default_owner: app_owner

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

default_privileges:
  - owner: app_owner
    scope: { type: schema, schema: app }
    grant:
      - role: orders_reader
        privileges: [SELECT]
        on_type: table

The wildcard repairs and maintains existing tables. The default covers tables created later by app_owner. Neither substitutes for the other.

Default privileges belong to the creating role, not to the schema and not to the login that happens to run the migration.

Guide

Continue: offboarding

Use the durable owner to remove Priya without deleting her objects.

Open guide

Guide

Default privileges reference

See schema and global scopes, PUBLIC defaults, and object types.

Open guide