Skip to main content

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 byApproachWhat Atlas manages
Atlas, as part of your schema migrationsObject grants (permissions = true)The grants of each object, declared in the schema
Another tool, such as your application's migration toolDefault privileges (default_permissions = true)Roles, schema grants, and the default privileges that apply to the objects the other tool creates
Don't use default privileges in schema migrations

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:

atlas.hcl
docker "postgres" "hardened" {
image = "postgres:18"
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:

schema.sql
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:

schema.sql
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​

  1. Docker
  2. Atlas installed on your machine (installation guide)
  3. An Atlas Pro account (run atlas login to 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)
Cleaning up

When you're done, stop the container with atlas tool docker kill --name my-db.

atlas.hcl
env "local" {
url = getenv("DATABASE_URL")
dev = "docker://postgres/18/dev"
schema {
src = "file://schema.pg.hcl"
mode {
permissions = true
}
}
}

Step 1: Create Objects Without Explicit PUBLIC Permissions​

schema.pg.hcl
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.

Step 2: Grant PUBLIC Access Explicitly​

Add permission blocks for the objects that should keep PUBLIC access:

schema.pg.hcl
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.

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:

atlas.hcl
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:

setup.sql
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:

atlas.hcl
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 = true tells Atlas to inspect the default privileges of every role and manage them as default_permission blocks. It's set in a mode block labeled postgres, which also accepts the other mode options.
  • The public.* pattern excludes the objects in the public schema, 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 exclude public itself, since that also excludes the default privileges defined in it.
  • The *[type=default_permission].PUBLIC pattern leaves the privileges PostgreSQL grants PUBLIC by 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
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:

schema.pg.hcl
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:

ObjectGrants
invoicesapp: SELECT, INSERT, UPDATE, DELETE. reader: SELECT.
invoices_id_seqapp: 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.

Existing objects

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 rdsadmin on 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:

atlas.hcl
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 migrations also 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:

atlas.hcl
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.

note

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:

schema.sql
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​

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.