Managing Default Privileges in PostgreSQL
PostgreSQL sets the initial privileges of every new object from its default privileges. Some are built
in, such as EXECUTE for PUBLIC on new functions, and others are defined with
ALTER DEFAULT PRIVILEGES.
Atlas supports two ways of managing access, and the right one depends on who creates the objects:
| Objects created by | Approach | What Atlas manages |
|---|---|---|
| Atlas, as part of your schema migrations | Object grants (permissions = true) | The grants of each object, declared in the schema |
| Another tool, such as your application's migration tool | Default privileges (default_permissions = true) | Roles, schema grants, and the default privileges that apply to the objects the other tool creates |
Atlas is declarative. Your schema describes the desired state of each object, and Atlas plans the
changes needed to reach it. Default privileges don't describe the state of any object. They only affect
objects created later, so existing objects keep their grants, and an object's grants depend on when it
was created. Don't enable default_permissions in an environment that migrates your schema, in either
the declarative or the versioned workflow. Declare object grants instead.
Use default_permissions only in an environment that manages access to schemas whose objects another
tool creates. Atlas can't declare grants for objects it doesn't manage, but it can manage the default
privileges that apply to them as they're created, detect drift in those privileges, and keep roles and
schema grants in your schema.
For full coverage of roles, users, and permissions, see the declarative security guide and the versioned security guide.
Roles, users, permissions, and default privileges are available only to Atlas Pro users. To use this feature, run:
atlas login
How PostgreSQL Default Privileges Work
PostgreSQL maintains two layers of default access control.
The first layer is built in. PUBLIC gets EXECUTE on every new function and procedure, and USAGE on
every new type and domain. The USAGE that PUBLIC has on the public schema isn't one of them. It's
a regular grant on that schema, set when the database is created.
The second is user-configured via ALTER DEFAULT PRIVILEGES, which overrides what happens when new
objects are created:
-- Future functions created by this role will NOT get PUBLIC EXECUTE.
ALTER DEFAULT PRIVILEGES REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;
-- Future tables created by this role will grant SELECT to a readonly role.
ALTER DEFAULT PRIVILEGES GRANT SELECT ON TABLES TO readonly;
These settings are stored in the pg_default_acl catalog. They apply only to objects that their role
creates later (FOR ROLE), optionally in a single schema (IN SCHEMA), and they never change existing
objects. As a result, an object's grants depend on when it was created, while default privileges describe
objects that don't exist yet. This is why Atlas manages object grants declaratively, and manages default
privileges for objects it doesn't create only when you enable default_permissions.
When Atlas Manages the Objects
Use this approach in every environment that migrates your schema. Declare object grants with permission
blocks or GRANT statements, and keep default_permissions disabled. Atlas doesn't manage the default
privileges themselves, but it takes them into account when it creates objects, so every object ends up
with the grants your schema declares.
HCL Schemas
HCL schemas are fully declarative. Atlas treats the HCL as the complete desired state: if a privilege is not declared, it should not exist. This applies to PostgreSQL's implicit defaults too.
When Atlas creates a function on the dev database, PostgreSQL automatically
grants EXECUTE to PUBLIC. Atlas inspects the result, sees a privilege that wasn't declared in HCL,
and generates a REVOKE after the CREATE statement. To keep a default privilege, declare it explicitly
with a permission block:
permission {
to = PUBLIC
for = function.add
privileges = [EXECUTE]
}
With this block present, Atlas sees the privilege in both the desired state and the dev database, so no
REVOKE is generated.
Matching Your Production Defaults
By default, Atlas generates REVOKE statements for default privileges not declared in HCL. But if
your production database already restricts these defaults (e.g., ALTER DEFAULT PRIVILEGES REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC), those REVOKE statements are redundant: production never granted PUBLIC EXECUTE in the first place.
To tell Atlas how your production databases are configured, set the same ALTER DEFAULT PRIVILEGES on
the dev database. Atlas inspects the dev database's pg_default_acl to decide what privileges a new
object receives. When the dev database already excludes PUBLIC EXECUTE, Atlas won't generate a
REVOKE for it. For example:
- Docker
- External Dev Database
docker "postgres" "hardened" {
image = "postgres:18"
baseline = <<-SQL
ALTER DEFAULT PRIVILEGES REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;
SQL
}
dev "postgres" "hardened" {
url = getenv("DEV_URL")
baseline = <<-SQL
ALTER DEFAULT PRIVILEGES REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;
SQL
}
SQL Schemas
SQL schemas work differently. Since default privileges are implicitly created when you apply the SQL
definitions on a database, Atlas does not automatically revoke them. Instead, you control defaults
explicitly by including ALTER DEFAULT PRIVILEGES statements in your schema file.
To revoke PostgreSQL's implicit PUBLIC EXECUTE from functions, add the revoke at the top of your SQL
schema:
ALTER DEFAULT PRIVILEGES REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;
CREATE FUNCTION add(a integer, b integer) RETURNS integer
LANGUAGE plpgsql AS $$
BEGIN
RETURN a + b;
END;
$$;
If you prefer fine-grained control, you can skip ALTER DEFAULT PRIVILEGES entirely and write individual
REVOKE statements after each function or procedure.
To add default grants, the same approach works in reverse. Objects created after an
ALTER DEFAULT PRIVILEGES GRANT ... inherit that grant:
ALTER DEFAULT PRIVILEGES GRANT SELECT ON TABLES TO PUBLIC;
CREATE TABLE t1 (id integer);
CREATE TABLE t2 (id integer);
The generated migration includes GRANT SELECT on each table. Removing the ALTER DEFAULT PRIVILEGES
line later generates REVOKE statements. Note that ALTER DEFAULT PRIVILEGES is order-dependent:
only objects defined after the statement are affected.
Walkthrough: Objects Atlas Creates
Prerequisites
- Docker
- Atlas installed on your machine (installation guide)
- An Atlas Pro account (run
atlas loginto authenticate)
Setup
Start by spinning up a local PostgreSQL database using atlas tool docker:
export DATABASE_URL=$(atlas tool docker --url "docker://postgres/18/demo" --name my-db)
When you're done, stop the container with atlas tool docker kill --name my-db.
- Atlas DDL (HCL)
- SQL
env "local" {
url = getenv("DATABASE_URL")
dev = "docker://postgres/18/dev"
schema {
src = "file://schema.pg.hcl"
mode {
permissions = true
}
}
}
env "local" {
url = getenv("DATABASE_URL")
dev = "docker://postgres/18/dev"
schema {
src = "file://schema.sql"
mode {
permissions = true
}
}
}
Step 1: Create Objects Without Explicit PUBLIC Permissions
- Atlas DDL (HCL)
- SQL
function "add" {
schema = schema.public
lang = PLpgSQL
arg "a" {
type = integer
}
arg "b" {
type = integer
}
return = integer
as = <<-SQL
BEGIN
RETURN a + b;
END;
SQL
}
procedure "noop" {
schema = schema.public
lang = PLpgSQL
as = <<-SQL
BEGIN
END;
SQL
}
schema "public" {
comment = "standard public schema"
}
Running atlas migrate diff produces:
-- Revoke on schema "public" from "PUBLIC"
REVOKE USAGE ON SCHEMA "public" FROM PUBLIC;
-- Create "add" function
CREATE FUNCTION "public"."add" ("a" integer, "b" integer) RETURNS integer LANGUAGE plpgsql AS $$
BEGIN
RETURN a + b;
END;
$$;
-- Revoke on function "add" from "PUBLIC"
REVOKE EXECUTE ON FUNCTION "public"."add" FROM PUBLIC;
-- Create "noop" procedure
CREATE PROCEDURE "public"."noop" () LANGUAGE plpgsql AS $$
BEGIN
END;
$$;
-- Revoke on procedure "noop" from "PUBLIC"
REVOKE EXECUTE ON PROCEDURE "public"."noop" FROM PUBLIC;
Atlas created each object and immediately revoked the privilege that PostgreSQL granted by default.
Without ALTER DEFAULT PRIVILEGES, Atlas passes through SQL objects as-is. PostgreSQL's built-in
defaults apply, and no REVOKE is generated:
CREATE FUNCTION add(a integer, b integer) RETURNS integer
LANGUAGE plpgsql AS $$
BEGIN
RETURN a + b;
END;
$$;
CREATE PROCEDURE noop()
LANGUAGE plpgsql AS $$
BEGIN
END;
$$;
To remove the implicit PUBLIC EXECUTE, add ALTER DEFAULT PRIVILEGES at the top:
// highlight-next-line
ALTER DEFAULT PRIVILEGES REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;
CREATE FUNCTION add(a integer, b integer) RETURNS integer
LANGUAGE plpgsql AS $$
BEGIN
RETURN a + b;
END;
$$;
CREATE PROCEDURE noop()
LANGUAGE plpgsql AS $$
BEGIN
END;
$$;
Atlas applies the statement on the dev database, then generates the appropriate REVOKE statements
after each function and procedure to match the desired state.
Step 2: Grant PUBLIC Access Explicitly
- Atlas DDL (HCL)
- SQL
Add permission blocks for the objects that should keep PUBLIC access:
permission {
to = PUBLIC
for = function.add
privileges = [EXECUTE]
}
permission {
to = PUBLIC
for = schema.public
privileges = [USAGE]
}
Running atlas migrate diff:
-- Grant on schema "public" to "PUBLIC"
GRANT USAGE ON SCHEMA "public" TO PUBLIC;
-- Grant on function "add" to "PUBLIC"
GRANT EXECUTE ON FUNCTION "public"."add" TO PUBLIC;
The procedure still gets its REVOKE because no explicit permission was added for it.
Use ALTER DEFAULT PRIVILEGES GRANT ... before the object definitions:
ALTER DEFAULT PRIVILEGES GRANT SELECT ON TABLES TO PUBLIC;
CREATE TABLE t1 (id integer);
CREATE TABLE t2 (id integer);
The generated migration includes GRANT SELECT on each table:
-- Create "t1" table
CREATE TABLE "public"."t1" ("id" integer NULL);
-- Grant on table "t1" to "PUBLIC"
GRANT SELECT ON TABLE "public"."t1" TO PUBLIC;
-- Create "t2" table
CREATE TABLE "public"."t2" ("id" integer NULL);
-- Grant on table "t2" to "PUBLIC"
GRANT SELECT ON TABLE "public"."t2" TO PUBLIC;
Removing the ALTER DEFAULT PRIVILEGES line later and running atlas migrate diff generates
the corresponding revokes:
-- Revoke on table "t1" from "PUBLIC"
REVOKE SELECT ON TABLE "public"."t1" FROM PUBLIC;
-- Revoke on table "t2" from "PUBLIC"
REVOKE SELECT ON TABLE "public"."t2" FROM PUBLIC;
Step 3: Match Production Defaults on the Dev Database
If your production database already runs ALTER DEFAULT PRIVILEGES to restrict defaults, configure the
dev database to match so Atlas doesn't generate unnecessary REVOKE statements:
docker "postgres" "hardened" {
image = "postgres:18"
baseline = <<-SQL
ALTER DEFAULT PRIVILEGES REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;
SQL
}
env "local" {
url = getenv("DATABASE_URL")
dev = docker.postgres.hardened.url
schema {
src = "file://schema.pg.hcl"
mode {
permissions = true
}
}
}
The same HCL schema from Step 1 (no permission blocks) now generates a shorter migration
with no REVOKE EXECUTE on functions or procedures:
-- Revoke on schema "public" from "PUBLIC"
REVOKE USAGE ON SCHEMA "public" FROM PUBLIC;
-- Create "add" function
CREATE FUNCTION "public"."add" ("a" integer, "b" integer) RETURNS integer LANGUAGE plpgsql AS $$
BEGIN
RETURN a + b;
END;
$$;
-- Create "noop" procedure
CREATE PROCEDURE "public"."noop" () LANGUAGE plpgsql AS $$
BEGIN
END;
$$;
The dev database's default privileges already exclude PUBLIC EXECUTE, so Atlas correctly determines
no revoke is needed.
When Another Tool Creates the Objects
In this example, the application's migration tool connects as migrations and creates the tables,
sequences, and functions in the public schema. The application connects as app, and analysts use the
reader role. Atlas doesn't touch the objects the migration tool creates. It manages the roles, the
extensions, the grants on the schema, and the default privileges that give new objects their grants.
Setup
Start a local PostgreSQL database using atlas tool docker:
export DATABASE_URL=$(atlas tool docker --url "docker://postgres/18/demo" --name my-db)
Then create the roles and grants that exist before Atlas starts managing access, and a table owned by the migration tool:
CREATE EXTENSION pgcrypto;
CREATE ROLE migrations LOGIN;
CREATE ROLE app LOGIN;
CREATE ROLE reader;
GRANT CREATE, USAGE ON SCHEMA public TO migrations;
GRANT USAGE ON SCHEMA public TO app, reader;
ALTER DEFAULT PRIVILEGES FOR ROLE migrations IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app;
SET ROLE migrations;
CREATE TABLE orders (id bigserial PRIMARY KEY, total numeric NOT NULL);
RESET ROLE;
psql "$DATABASE_URL" -f setup.sql
Configure Atlas
Create an atlas.hcl file with an environment that manages access only:
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
}
}
}
A few things to note about this configuration:
default_permissions = truetells Atlas to inspect the default privileges of every role and manage them asdefault_permissionblocks. It's set in amodeblock labeledpostgres, which also accepts the othermodeoptions.- The
public.*pattern excludes the objects in thepublicschema, such as the tables the migration tool creates, so Atlas never changes them. The schema itself, with its grants, extensions, and default privileges, stays managed. Don't excludepublicitself, since that also excludes the default privileges defined in it. - The
*[type=default_permission].PUBLICpattern leaves the privileges PostgreSQL grantsPUBLICby default to PostgreSQL. To manage them as well, see Built-in Privileges.
Step 1: Inspect the Current State
Inspect the database into a schema file:
atlas schema inspect --env prod > schema.pg.hcl
permission {
to = PUBLIC
for = schema.public
privileges = [USAGE]
}
permission {
to = user.app
for = schema.public
privileges = [USAGE]
}
permission {
to = user.migrations
for = schema.public
privileges = [CREATE, USAGE]
}
permission {
to = role.reader
for = schema.public
privileges = [USAGE]
}
default_permission {
owner = user.migrations
schema = schema.public
on = TABLES
to = user.app
privileges = [DELETE, INSERT, SELECT, UPDATE]
}
role "reader" {
}
user "app" {
}
user "migrations" {
}
extension "pgcrypto" {
schema = schema.public
version = "1.4"
comment = "cryptographic functions"
}
schema "public" {
comment = "standard public schema"
}
Roles that can log in appear as user blocks. The orders table isn't included, because the migration
tool owns it. Running atlas schema apply now reports no changes:
atlas schema apply --env prod
Schema is synced, no changes to be made
Step 2: Add Default Privileges
Give app access to the sequences and functions the migration tool creates in public, and let reader
read the tables it creates in any schema:
default_permission {
owner = user.migrations
schema = schema.public
on = SEQUENCES
to = user.app
privileges = [SELECT, USAGE]
}
default_permission {
owner = user.migrations
schema = schema.public
on = FUNCTIONS
to = user.app
privileges = [EXECUTE]
}
# Without "schema", the default privileges apply in every schema.
default_permission {
owner = user.migrations
on = TABLES
to = role.reader
privileges = [SELECT]
}
Then apply the change:
atlas schema apply --env prod
Atlas plans one ALTER DEFAULT PRIVILEGES statement per block:
Planning migration statements (3 in total):
-- grant on future sequences of "migrations" in "public" to "app":
-> ALTER DEFAULT PRIVILEGES FOR ROLE "migrations" IN SCHEMA "public" GRANT SELECT, USAGE ON SEQUENCES TO "app";
-- grant on future functions of "migrations" in "public" to "app":
-> ALTER DEFAULT PRIVILEGES FOR ROLE "migrations" IN SCHEMA "public" GRANT EXECUTE ON FUNCTIONS TO "app";
-- grant on future tables of "migrations" to "reader":
-> ALTER DEFAULT PRIVILEGES FOR ROLE "migrations" GRANT SELECT ON TABLES TO "reader";
Step 3: Create Objects With the Migration Tool
Now let the migration tool create a table and a function:
SET ROLE migrations;
CREATE TABLE invoices (id bigserial PRIMARY KEY, order_id bigint NOT NULL REFERENCES orders);
CREATE FUNCTION invoice_count() RETURNS bigint LANGUAGE sql AS 'SELECT count(*) FROM invoices';
RESET ROLE;
PostgreSQL applies the default privileges as it creates the objects:
| Object | Grants |
|---|---|
invoices | app: SELECT, INSERT, UPDATE, DELETE. reader: SELECT. |
invoices_id_seq | app: SELECT, USAGE. |
invoice_count() | app: EXECUTE. PUBLIC: EXECUTE (built-in). |
Atlas doesn't manage these objects, so atlas schema apply --env prod still reports no changes.
Default privileges only apply to objects created after them. The orders table existed before the
reader default privilege was added, so reader can't read it. To grant access to existing tables, run
a one-time GRANT outside Atlas, for example in a migration of the migration tool:
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reader;
Step 4: Detect Drift
Suppose someone changes a default privilege manually:
ALTER DEFAULT PRIVILEGES FOR ROLE migrations IN SCHEMA public REVOKE DELETE ON TABLES FROM app;
Run atlas schema diff to compare the database with the schema file:
atlas schema diff --env prod --from env://url --to env://schema.src
The output is the statement that restores the declared state:
-- 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 fix the drift. To fail a CI job when the database drifts, check
that the diff is empty:
test -z "$(atlas schema diff --env prod --from env://url --to env://schema.src --format '{{ sql . }}')"
Step 5: Remove a Role
To remove reader, delete its role block and the permission and default_permission blocks that
reference it, then run atlas schema apply --env prod:
Planning migration statements (4 in total):
-- revoke on schema "public" from "reader":
-> REVOKE USAGE ON SCHEMA "public" FROM "reader";
-- revoke on future tables of "migrations" from "reader":
-> ALTER DEFAULT PRIVILEGES FOR ROLE "migrations" REVOKE SELECT ON TABLES FROM "reader";
-- revoke on all tables in "public" from "reader":
-> REVOKE ALL ON ALL TABLES IN SCHEMA "public" FROM "reader";
-- drop role "reader":
-> DROP ROLE "reader";
PostgreSQL refuses to drop a role that still has privileges. The default privileges gave reader
SELECT on invoices, which is a table Atlas doesn't manage. Before it drops the role, Atlas revokes
the role's privileges on all tables in the schemas those default privileges cover. See
Dropping Roles.
Running in Production
Before using this setup in production:
- Make sure the user Atlas connects as can change the default privileges of
migrations. See Atlas User Permissions. - On managed services, exclude the provider's own roles, such as
rdsadminon Amazon RDS. See Cloud Provider Roles. - In CI, apply the reviewed schema from the default branch with
atlas schema apply --env prod --auto-approve, and run the drift check from Step 4 on a schedule. Only these jobs should get the database URL, from a scope that only the default branch can read, such as a protected environment.
Default Privileges as Code
With default_permissions enabled, the default_permission
blocks describe all the default privileges in the database. Atlas inspects the default privileges of
every role and plans the ALTER DEFAULT PRIVILEGES statements that make the database match the blocks,
including revoking the ones no block declares.
Built-in Privileges
PostgreSQL grants PUBLIC EXECUTE on new functions and USAGE on new types by default. Without the
*[type=default_permission].PUBLIC exclusion, Atlas manages these built-in privileges too, and
atlas schema inspect prints them for every role. For migrations, the output includes:
default_permission {
owner = user.migrations
on = FUNCTIONS
to = PUBLIC
privileges = [EXECUTE]
}
default_permission {
owner = user.migrations
on = TYPES
to = PUBLIC
privileges = [USAGE]
}
PUBLIC keeps these privileges on a role's new objects only as long as a block declares them. For
example, to stop PUBLIC from executing the functions that migrations creates, delete the FUNCTIONS
block above. Atlas then plans:
-- Revoke on future functions of "migrations" from "PUBLIC"
ALTER DEFAULT PRIVILEGES FOR ROLE "migrations" REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;
Since privileges can't be empty, deleting the block is how you revoke them. It also means that a role
you add without these blocks has its built-in privileges revoked right after Atlas creates it:
-- Create role "reports"
CREATE ROLE "reports";
-- Revoke on future functions of "reports" from "PUBLIC"
ALTER DEFAULT PRIVILEGES FOR ROLE "reports" REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;
-- Revoke on future types of "reports" from "PUBLIC"
ALTER DEFAULT PRIVILEGES FOR ROLE "reports" REVOKE USAGE ON TYPES FROM PUBLIC;
Excluding Default Privileges
exclude patterns match default privileges by their owner. To match a grantee, add it as a second part:
env "prod" {
# ...
exclude = [
# The default privileges that "migrations" sets.
"migrations[type=default_permission]",
# The default privileges of the role Atlas connects as.
"CURRENT_ROLE[type=default_permission]",
# The privileges granted to PUBLIC, in every entry.
"*[type=default_permission].PUBLIC",
# The schema "analytics", with the default privileges set in it.
"analytics[type=schema]",
]
}
- An excluded grantee keeps its privileges as they are, including the built-in ones.
- Excluding a schema also excludes the default privileges defined in it (
IN SCHEMA), but excluding its objects (public.*) doesn't. - Always include the type selector. Without it, a pattern such as
migrationsalso excludes the role itself.
Cloud Provider Roles
Managed PostgreSQL services come with their own roles, and the Atlas user can't change their default privileges. Exclude these roles along with their default privileges:
- Amazon RDS and Aurora:
rds*[type=role|user|default_permission] - Google Cloud SQL:
cloudsql*[type=role|user|default_permission] - Azure Database for PostgreSQL:
azure*[type=role|user|default_permission]
Provider roles that can log in, such as rdsadmin, are inspected as users, which is why the selector
includes user.
Atlas User Permissions
PostgreSQL lets a role change its own default privileges and those of the roles whose privileges it
inherits. If Atlas doesn't connect as a superuser, set up its user (atlas in this example) once.
Otherwise, ALTER DEFAULT PRIVILEGES FOR ROLE ... fails with
permission denied to change default privileges:
-- For each existing role whose default privileges Atlas changes.
GRANT migrations TO atlas WITH INHERIT TRUE;
-- For the roles Atlas creates.
ALTER ROLE atlas SET createrole_self_grant = 'inherit';
WITH INHERIT TRUE and createrole_self_grant require PostgreSQL 16 or later. On earlier versions, use
GRANT migrations TO atlas.
When Atlas connects as such a user, it also inspects the admin role (postgres on a standard
installation) as a user, along with its default privileges. Exclude both, like the
cloud provider roles:
exclude = ["public.*", "postgres[type=role|user|default_permission]"]
Dropping Roles
PostgreSQL doesn't drop a role that holds privileges or that default privileges still reference. Before
a DROP ROLE, Atlas revokes the default privileges granted to the role, and the privileges they already
gave it on existing objects. This covers all tables, sequences, and routines in the schemas those default
privileges apply to, including objects Atlas doesn't manage, as well as the types and schemas Atlas
manages, even with permissions disabled. Atlas also resets the default privileges the role owns to the
built-in ones.
PostgreSQL has no bulk revoke for types, so Atlas revokes privileges on each type it manages. Before
dropping a role, revoke its privileges on types Atlas doesn't manage, and on excluded objects in schemas
that no default privileges cover. With schemas = false, Atlas revokes only in the schemas that default
privileges are defined in.
SQL Schemas With Default Privileges
With default_permissions enabled, the ALTER DEFAULT PRIVILEGES statements in a SQL schema define the
desired default privileges, instead of being turned into object grants as described in
SQL Schemas. The following file describes the same state as the HCL in
Step 1:
CREATE EXTENSION pgcrypto;
CREATE ROLE migrations LOGIN;
CREATE ROLE app LOGIN;
CREATE ROLE reader;
GRANT CREATE, USAGE ON SCHEMA public TO migrations;
GRANT USAGE ON SCHEMA public TO app, reader;
ALTER DEFAULT PRIVILEGES FOR ROLE migrations IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app;
A statement without FOR ROLE sets the default privileges of the role that runs it. On the dev database,
that's the role Atlas connects as.
Next Steps
Declarative Security
Manage roles, permissions, and row-level security with atlas schema apply
Versioned Security
Ship security changes as reviewed migration files
Default Permissions Reference
The attributes of the default_permission block
CI/CD for Declarative Migrations
Plan, review, and apply declarative changes from your CI platform
Deploying Schema Changes
Deploy schema changes as part of your deployment process
Agent Skills
Install the Atlas skills for Claude Code, Codex, and Cursor, including the PostgreSQL skill
Need Help?
Click the Intercom bubble in the corner or schedule a demo to chat with our team.
You can also join the conversation on our Discord server.