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

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 next chapter tests the separate question of which role owns an object created by a migration.

Merge these entries into the policy from chapter 3; retain its existing grants and memberships. Add the durable owner and make schema ownership explicit:

Policy fragmentPolicy field guide

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.

Transferring an existing table to app_owner does not apply that role's default privileges. The next chapter demonstrates the separate repairs for existing objects and future objects.

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

The membership-mechanics chapter later takes the edge itself apart: inherited privilege use, SET ROLE, and delegated administration are separate facts.

Download the complete policy after this chapter. It retains the reader grants and Bob/reporting memberships from the earlier chapters.

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