Default Privileges

ALTER DEFAULT PRIVILEGES attaches grant templates to future objects created by a specific role in a schema. Without it, every migration must remember manual GRANT statements.

Recipe

-- Run as migration owner (creates tables in deploy)
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
  GRANT SELECT ON TABLES TO app_readers;
 
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_writers;
 
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
  GRANT USAGE, SELECT ON SEQUENCES TO app_writers;

When to reach for this: Bootstrap of a new schema, onboarding a new service role, or fixing recurring "forgot to GRANT" deploy failures.

Working Example

CREATE ROLE app_owner NOLOGIN;
CREATE ROLE app_migrator LOGIN PASSWORD 'vault';
GRANT app_owner TO app_migrator;
 
CREATE SCHEMA app AUTHORIZATION app_owner;
 
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
  REVOKE ALL ON TABLES FROM PUBLIC;
 
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
  GRANT SELECT ON TABLES TO app_readers;
 
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_writers;
 
-- Migration creates new table - grants apply automatically
SET ROLE app_owner;
CREATE TABLE app.orders (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY);
RESET ROLE;
 
-- Verify
SELECT grantee, privilege_type
FROM information_schema.table_privileges
WHERE table_schema = 'app' AND table_name = 'orders';

What this demonstrates:

• FOR ROLE app_owner matches the creating role - critical detail
• REVOKE ALL FROM PUBLIC on defaults closes public access for new tables
• New orders table already has app_readers/app_writers grants

Deep Dive

FOR ROLE Matters

-- Wrong: defaults for current user only
ALTER DEFAULT PRIVILEGES IN SCHEMA app GRANT SELECT ON TABLES TO app_ro;
 
-- Right: defaults when app_owner creates objects
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
  GRANT SELECT ON TABLES TO app_ro;

• Defaults are keyed by object creator role, not schema owner alone.
• CI migrator must consistently SET ROLE app_owner or connect as app_owner via SET SESSION AUTHORIZATION (superuser only).
• List configured defaults:

SELECT * FROM pg_default_acl;

Object Types

One-Time Grants Still Needed

-- Existing objects before defaults were set
GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_readers;

• ALTER DEFAULT PRIVILEGES is not retroactive.
• Migration bootstrap scripts often pair ALL TABLES + ALTER DEFAULT PRIVILEGES.

Gotchas

• Wrong FOR ROLE - Migrator runs as app_migrator but defaults set for app_owner. Fix: Align creator role or add defaults for both.
• Multiple migrators - Flyway user vs human hotfix user create objects without defaults. Fix: Single migration role only in prod.
• Forgot sequences - Table grants exist; INSERT fails on nextval. Fix: Default privileges on SEQUENCES too.
• Schema created by superuser - Defaults for app_owner do not apply to superuser-created tables. Fix: Never create app tables as superuser.
• Testing only on empty schema - Defaults never tested until second migration. Fix: CI creates two tables and asserts grants.

Alternatives

FAQs

Related

• GRANT/REVOKE Patterns - one-time grants on existing objects
• Migrations Basics - migration role discipline
• Permissions Best Practices - app connects as least privilege

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.