Default privileges

Default privileges control what privileges are automatically granted when new objects are created. This ensures that roles get access to tables, sequences, and functions created after the initial grant.

Why default privileges?

Wildcard grants like GRANT SELECT ON ALL TABLES IN SCHEMA only apply to objects that exist right now. When a migration creates a new table, existing roles won't have access to it unless you re-run the grant.

ALTER DEFAULT PRIVILEGES solves this by configuring automatic grants for future objects.

Syntax

default_owner: app_owner

default_privileges:
  - owner: app_owner
    schema: public
    grant:
      - role: analytics
        privileges: [SELECT]
        on_type: table
      - role: analytics
        privileges: [USAGE, SELECT]
        on_type: sequence

This generates:

ALTER DEFAULT PRIVILEGES FOR ROLE "app_owner"
  IN SCHEMA "public"
  GRANT SELECT ON TABLES TO "analytics";

ALTER DEFAULT PRIVILEGES FOR ROLE "app_owner"
  IN SCHEMA "public"
  GRANT SELECT, USAGE ON SEQUENCES TO "analytics";

Owner context

The owner field specifies which role's object creation triggers the default grant. This is typically the role that runs migrations or creates tables (e.g. pgloader_pg, app_owner).

If owner is omitted on a default privilege entry, the top-level default_owner is used. If neither is set, it falls back to postgres.

Default privileges in profiles

When using profiles, default privileges are expanded automatically:

profiles:
  viewer:
    grants:
      - privileges: [SELECT]
        on: { type: table, name: "*" }
    default_privileges:
      - privileges: [SELECT]
        on_type: table

schemas:
  - name: inventory
    profiles: [viewer]

This generates a default privilege rule for inventory-viewer on tables in the inventory schema, using the default_owner as the owner context.