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

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.
PostgreSQL looks at the current_user that creates the object. Creating as deploy does not use app_owner's defaults merely because app_owner owns the schema; creating after SET ROLE app_owner does.

Merge these entries into chapter 4's policy. Replace the reader's table-specific orders grant with the wildcard below, retaining schema USAGE, all role definitions, and existing memberships. Add the default privilege rule:

Policy fragmentPolicy field guide

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.

The lab compares both cases after installing the same default: a table created as deploy does not receive it, while a table created with current_user = app_owner does. Ownership transfers are not retroactive creation events, so moving an old table to app_owner does not apply the default either.

At creation time, PostgreSQL applies the defaults configured for current_user, including the relevant schema-specific defaults. Inheriting another role's privileges does not inherit its default privileges.

Download the complete policy after this chapter.

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