Skip to main content

Managing Default Privileges in PostgreSQL

PostgreSQL grants privileges to the PUBLIC pseudo-role whenever certain objects are created. Every database user can EXECUTE any function, CONNECT to any database, and use the public schema without an explicit GRANT. For many production environments, this is too permissive.

Atlas handles these defaults differently depending on schema format. In HCL, the schema is fully declarative: if a default privilege isn't explicitly declared, Atlas revokes it. In SQL, you manage defaults by adding ALTER DEFAULT PRIVILEGES statements to your schema, usually as the first imported file or the opening statement in schema.sql. You can also write individual GRANT/REVOKE statements per object if you prefer fine-grained control.

For full coverage of roles, users, and permissions, see the declarative security guide and the versioned security guide.

Atlas Pro Feature

Roles, users, and permissions 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 is the built-in defaults that automatically grant EXECUTE to PUBLIC when you CREATE FUNCTION, and USAGE to PUBLIC on the public schema. These have been the defaults since PostgreSQL's early days.

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 system catalog and apply only to future objects created by the role that ran the command. Existing objects are not affected. Think of it as a hook that fires on CREATE and sets the initial ACL on the new object, not as a schema-level declaration. The actual privileges on any given object depend on when it was created relative to the ALTER DEFAULT PRIVILEGES statement.

This is why Atlas intentionally does not manage default privileges as database objects. Reading a schema file that contains one tells you what future objects will get, not what existing objects have. Two databases where the same schema was applied at different times can end up with different privilege states. That's not deterministic, and Atlas is built around deterministic diffs.

Instead, Atlas offers two safe ways to work with default privileges, depending on schema format. The rest of this guide walks through both.

How Atlas Handles Defaults​

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​

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.

Next Steps​