4. Ownership - pgroles docs

Acme’s migration pipeline wants to add a column to orders. The grants that make reports work are irrelevant: Priya created the table, so Priya owns it.

Chapter 4 · Ownership

PostgreSQL 18.3 · PGlite ↗

The migration runner hits Priya’s old table

The original founder created orders herself. Object grants can allow data access, but only an owner—or a role holding the owner’s privileges—can change the table.

Chapter 4 · Ownership

PG 18.3

Step 1 of 3

Every step seeds its own database — jump anywhere

Step 1

Let deploy try the migration

Deploy can connect, but Acme never gave it ownership of Priya’s table. ALTER TABLE checks ownership rather than an ordinary ACL privilege.

Run the migration as deploy.

Run as

ALTER TABLE app.orders ADD COLUMN source text;

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.

A durable production shape

The capability role answers “who may read?” The owner role answers “who may change the objects?” They are different jobs.

Bob / reporting_app ──member of──> orders_reader ──USAGE + SELECT──> app.orders

deploy ──member of──> app_owner ──owns──> app schema and its objects

Because the membership inherits, deploy already carries the owner’s authority for owner-only DDL. The migration recipe still uses SET ROLE app_owner before creating objects—not to pass the ownership check, but so that everything the migration creates belongs to the durable owner rather than to the deployment login.

Add the durable owner and make schema ownership explicit:

pgroles.yamlpolicy schema

SectionFieldRole / profile / settingObjectPrivilege / typeUnknown

roles:
  - name: deploy
    login: true
  - name: app_owner
  - name: orders_reader

schemas:
  - name: app
    owner: app_owner

memberships:
  - role: app_owner
    members:
      - name: deploy

pgroles converges the app schema owner. It does not change the owner of every table inside the schema; existing objects need a migration or retirement workflow. Future migrations should create objects as app_owner.

Ownership is authority over the object, not another ACL entry. Give it to a durable role, not a person or deployment login.

The lesson used SET ROLE app_owner as a migration recipe so new objects land on the durable owner. The membership-mechanics chapter later takes the edge itself apart: INHERIT, SET, and ADMIN are three separate facts.

Guide

Continue: future objects

Create a new table and see why old wildcard grants do not follow it.

Open guide

Guide

Executor privileges

Check what the pgroles executor needs to transfer ownership.

Open guide