How to harden default privileges in PostgreSQL

Hardened default privileges in PostgreSQL prevent newly created tables, sequences, functions, and types from becoming unintentionally accessible to broad roles such as PUBLIC. Tight defaults reduce privilege creep as migrations add new database objects over time.

PostgreSQL applies a per-role default access control list (ACL) at object creation time. The ALTER DEFAULT PRIVILEGES command modifies that ACL for a specific creator role, optionally scoped to a schema, so future objects start with an explicit baseline of GRANTs and REVOKEs.

Default privilege changes apply only inside the current database and only to objects created after the change; existing objects keep their current permissions. Default privileges must be set for the role that runs DDL (migrations, schema changes), and separate defaults can be maintained per schema when different roles own objects in different namespaces. Object creation rights (for example, CREATE on a schema) are controlled separately by schema and database privileges.

1. Connect to the target database using psql with a role that can manage the creator role defaults.

   $ psql --dbname=appdb --username=postgres
   Password for user postgres:
   psql (16.1)
   Type "help" for help.
   
   appdb=#

   The session role must be a superuser or a member of the creator role that runs CREATE TABLE, CREATE FUNCTION, and other DDL.

2. List schemas to confirm the schema where the creator role creates objects.

   appdb=# \dn+
                                       List of schemas
     Name  |   Owner    |           Access privileges            |      Description
   --------+------------+----------------------------------------+-----------------------
    public | postgres   | postgres=UC/postgres                   +| standard public schema
           |            | =UC/postgres                            |
    app    | app_owner  | app_owner=UC/app_owner                 +| application objects
           |            | app_runtime=U/app_owner                 |
   (2 rows)

   Use IN SCHEMA to scope defaults to a single schema, or omit it to apply defaults to all schemas in the database.

3. Review existing default ACL entries for the creator role in the target schema.

   appdb=# SELECT defaclrole::regrole AS role,
          COALESCE(n.nspname, '(all)') AS schema,
          CASE d.defaclobjtype
            WHEN 'r' THEN 'tables'
            WHEN 'S' THEN 'sequences'
            WHEN 'f' THEN 'functions'
            WHEN 'T' THEN 'types'
            WHEN 'n' THEN 'schemas'
          END AS object_type,
          d.defaclacl AS acl
   FROM pg_default_acl d
   LEFT JOIN pg_namespace n ON n.oid = d.defaclnamespace
   WHERE defaclrole = 'app_owner'::regrole
     AND COALESCE(n.nspname, 'app') = 'app'
   ORDER BY 1, 2, 3;
    role | schema | object_type | acl
   ------+--------+-------------+-----
   (0 rows)

   No rows indicates no custom default ACL entries for that role and schema.

4. Revoke PUBLIC access from newly created functions and types for the creator role in the target schema.

   appdb=# ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
   REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;
   ALTER DEFAULT PRIVILEGES
   appdb=# ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
   REVOKE USAGE ON TYPES FROM PUBLIC;
   ALTER DEFAULT PRIVILEGES

   Revoking EXECUTE or USAGE from PUBLIC can break roles that previously relied on using new functions or types without an explicit GRANT.

5. Grant only the required privileges on future objects to the runtime role.

   appdb=# ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
   GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_runtime;
   ALTER DEFAULT PRIVILEGES
   appdb=# ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
   GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO app_runtime;
   ALTER DEFAULT PRIVILEGES
   appdb=# ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
   GRANT EXECUTE ON FUNCTIONS TO app_runtime;
   ALTER DEFAULT PRIVILEGES
   appdb=# ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
   GRANT USAGE ON TYPES TO app_runtime;
   ALTER DEFAULT PRIVILEGES

   Add TRUNCATE, REFERENCES, or TRIGGER for tables only when required, and repeat the same pattern for each schema or creator role that performs DDL.

6. Confirm the updated default ACL no longer grants privileges to PUBLIC.

   appdb=# SELECT defaclrole::regrole AS role,
          COALESCE(n.nspname, '(all)') AS schema,
          CASE d.defaclobjtype
            WHEN 'r' THEN 'tables'
            WHEN 'S' THEN 'sequences'
            WHEN 'f' THEN 'functions'
            WHEN 'T' THEN 'types'
            WHEN 'n' THEN 'schemas'
          END AS object_type,
          d.defaclacl AS acl
   FROM pg_default_acl d
   LEFT JOIN pg_namespace n ON n.oid = d.defaclnamespace
   WHERE defaclrole = 'app_owner'::regrole
     AND COALESCE(n.nspname, 'app') = 'app'
   ORDER BY 1, 2, 3;
      role    | schema | object_type |              acl
   -----------+--------+-------------+--------------------------------
    app_owner | app    | functions   | {app_runtime=X/app_owner}
    app_owner | app    | sequences   | {app_runtime=rwU/app_owner}
    app_owner | app    | tables      | {app_runtime=arwd/app_owner}
    app_owner | app    | types       | {app_runtime=U/app_owner}
   (4 rows)

   An = entry in the ACL represents privileges granted to PUBLIC.

7. Perform a transaction-scoped test to confirm newly created objects inherit the hardened defaults.

   appdb=# BEGIN;
   BEGIN
   appdb=*# SET ROLE app_owner;
   SET
   appdb=*# CREATE TABLE app.priv_test (id bigint GENERATED BY DEFAULT AS IDENTITY);
   CREATE TABLE
   appdb=*# \dp app.priv_test
                                  Access privileges
    Schema |   Name    | Type  |      Access privileges       | Column privileges | Policies
   --------+-----------+-------+------------------------------+-------------------+----------
    app    | priv_test | table | app_owner=arwdDxt/app_owner +|                   |
           |           |       | app_runtime=arwd/app_owner   |                   |
   (1 row)
   appdb=*# \dp app.priv_test_id_seq
                                      Access privileges
    Schema |      Name        |   Type   |      Access privileges       | Column privileges | Policies
   --------+------------------+----------+------------------------------+-------------------+----------
    app    | priv_test_id_seq | sequence | app_owner=rwU/app_owner     +|                   |
           |                  |          | app_runtime=rwU/app_owner    |                   |
   (1 row)
   appdb=*# ROLLBACK;
   ROLLBACK