Understanding ALTER DEFAULT PRIVILEGES in PostgreSQL

PostgreSQL's ALTER DEFAULT PRIVILEGES is a handy way to pre-configure permissions for future objects, but its behavior often trips up even experienced users. The command works on what PostgreSQL calls "default privileges"—the set of privileges an object has immediately after creation.

By default, object owners get full privileges on everything they create. For most object types, no other role has any access by default. There are exceptions where PUBLIC receives privileges automatically:

  • On databases, PUBLIC receives CONNECT and TEMP.
  • On functions and procedures, PUBLIC receives EXECUTE.
  • On languages and data types, PUBLIC receives USAGE.

Inspecting these defaults with psql can be misleading. The \dp command shows nothing whether an object has an empty privilege array or NULL defaults. If you need to tell the difference, you have to query the system catalogs directly:

1

2

3

4

5

6

7

8

9

SELECT relname, relacl

FROM pg_class

WHERE relnamespace = 'public'::regnamespace;

relname │ relacl

═════════╪════════

defpriv │

nopriv  │ {}

(2 rows)

An empty privilege set is stored as an empty aclitem array, while default privileges are represented as NULL.

What the Command Changes

ALTER DEFAULT PRIVILEGES modifies the privileges applied to objects created in the future. It has no effect on objects that already exist—for those you need GRANT or REVOKE. This makes it useful for scenarios like separating object ownership from application logins: instead of grating privileges on every new object to an application user, you can change the owner role's default privileges and let new objects inherit the right ACLs automatically.

The same technique works for read-only roles. You can alter default privileges so the owner role automatically grants USAGE on schemas and SELECT on tables, sequences, and views to a read-only login. (PostgreSQL 14 and later also ship the predefined pg_read_all_data role as an alternative.)

The full syntax looks like this:

1

2

3

4

5

ALTER DEFAULT PRIVILEGES

    [ FOR { ROLE | USER } target_role [, ...] ]

    [ IN SCHEMA schema_name [, ...] ]

    { GRANT  privilege [, ...] ON object_type TO role [, ...] |

      REVOKE privilege [, ...] ON object_type FROM role [, ...] }

The FOR ROLE Trap

The most common cause of confusion is the FOR ROLE clause. According to PostgreSQL documentation, it names "an existing role of which the current role is a member," and if omitted the current role is assumed. But the semantic detail matters more: ALTER DEFAULT PRIVILEGES only changes defaults for objects created by target_role. If you omit the clause, the change applies only to objects created by the current user.

Novices often expect omission to mean "all roles," but that behavior doesn't exist. Implementing FOR ALL ROLES would let one user influence privileges on another user's objects, opening a path to privilege escalation for any non-superuser. This is why there is no such option—only a superuser could safely alter defaults for every role, and the built-in behavior has been deliberately kept restrictive.

The IN SCHEMA Gotcha

If the IN SCHEMA clause confuses you less than FOR ROLE, that's because it works in the opposite direction: when omitted, default privileges are changed for all schemas, not just one.

Combining IN SCHEMA with REVOKE can produce unexpected results, since revoking a privilege that was never granted changes nothing. Take the common task of removing the default EXECUTE on functions for PUBLIC:

1

2

ALTER DEFAULT PRIVILEGES

   REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;

Restricting this revocation to a single schema does nothing:

1

2

ALTER DEFAULT PRIVILEGES IN SCHEMA x

   REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;

Because the default EXECUTE for functions applies without a schema restriction, you have to revoke it without IN SCHEMA. If you want EXECUTE only for functions in particular schemas, first revoke the blanket default, then grant it back on the desired schemas.

Viewing and Removing Altered Defaults

Altered default privileges live in the pg_default_acl catalog. Querying it directly works:

1

2

3

4

5

6

7

8

9

10

11

SELECT defaclrole::regrole AS creator,

       defaclnamespace::regnamespace AS 'schema',

       defaclobjtype AS object_type,

       defaclacl AS default_permissions

FROM pg_default_acl;

creator │ schema  │ object_type │ default_permissions

═════════╪═════════╪═════════════╪═════════════════════

laurenz │ -       │ f           │ {laurenz=X/laurenz}

laurenz │ laurenz │ r           │ {duff=r/laurenz}

(2 rows)

Within psql, the \ddp command gives a shorthand for the same information.

Default privileges also complicate role removal. To DROP a role, you must first clean up both default privileges associated with that role as the creator (or target of FOR ROLE) and default privileges that grant privileges to the role:

1

2

3

4

5

ALTER DEFAULT PRIVILEGES FOR ROLE laurenz

   IN SCHEMA laurenz REVOKE SELECT ON TABLES FROM duff;

ALTER DEFAULT PRIVILEGES FOR ROLE laurenz

   GRANT EXECUTE ON FUNCTIONS TO PUBLIC;

This sometimes means issuing a GRANT just to remove an entry—restoring the default EXECUTE on functions for PUBLIC is the way to undo a prior REVOKE. When you need to drop the role entirely, an easier path exists: DROP OWNED removes all default privileges tied to the role, along with its other privileges and owned objects.

1

DROP OWNED BY laurenz;

You must be a member of the role or a superuser to run it.