PostgreSQL: Default Privileges as Code

Atlas now manages PostgreSQL default privileges as default_permission blocks, for schemas whose objects are created by another tool.

PostgreSQL default privileges (ALTER DEFAULT PRIVILEGES) decide which grants an object gets when a given role creates it. Atlas now manages them as default_permission blocks: atlas schema inspect prints them, atlas schema apply makes the database match them, and atlas schema diff detects drift on them.

When to Use It

Use it when another tool, such as your application's migration tool, creates the objects. Atlas can't declare grants on objects it doesn't manage, but it can manage the default privileges that grant access to them as the tool creates them.

Don't enable it in environments where Atlas migrates your schema. Default privileges apply only to objects created later, and existing objects keep their grants. When Atlas creates the objects, declare their grants with permission blocks.

Example

In atlas.hcl, set default_permissions = true in the mode "postgres" block, and exclude the objects the migration tool creates in public:

env "prod" {
  url     = getenv("DATABASE_URL")
  dev     = "docker://postgres/18/dev"
  exclude = [
    # Objects in "public" belong to the migration tool.
    "public.*",
    # Leave the built-in privileges of PUBLIC to PostgreSQL.
    "*[type=default_permission].PUBLIC",
  ]
  schema {
    src = "file://schema.pg.hcl"
    mode "postgres" {
      roles               = true
      permissions         = true
      default_permissions = true
    }
  }
}

In the schema file, declare the privileges that app gets on the tables migrations creates in public:

default_permission {
  owner      = user.migrations
  schema     = schema.public
  on         = TABLES
  to         = user.app
  privileges = [DELETE, INSERT, SELECT, UPDATE]
}

If someone revokes DELETE by hand, atlas schema diff prints the statement that restores it:

atlas schema diff --env prod --from env://url --to env://schema.src

-- Grant on future tables of "migrations" in "public" to "app"
ALTER DEFAULT PRIVILEGES FOR ROLE "migrations" IN SCHEMA "public" GRANT DELETE ON TABLES TO "app";

Run atlas schema apply --env prod to apply it.

Limitations

• PostgreSQL only.
• Default privileges don't apply to existing objects. Grant access to those once, with GRANT.
• Requires Atlas Pro. Run atlas login to start a free trial.

Next Steps

The Managing Default Privileges in PostgreSQL guide covers when to use default privileges instead of object grants, a full environment that manages access only, and the Atlas user and exclusions for cloud provider roles.