How to harden default privileges in PostgreSQL

New PostgreSQL objects can become too open when routine and type defaults still grant access to PUBLIC, or when migration-created tables and sequences do not grant the runtime role the access it actually needs. Hardening default privileges sets the future-object baseline before the next migration creates more tables, sequences, routines, or types.

ALTER DEFAULT PRIVILEGES changes the access control list that PostgreSQL applies when a role creates future objects. Target the role that runs DDL, such as an owner or migration role, rather than the runtime role that only reads or writes application data.

Default privilege changes are database-local and do not rewrite existing object permissions. Global defaults apply across schemas in the current database, while IN SCHEMA adds schema-specific defaults on top of those global settings. Remove built-in PUBLIC access to future routines and types at the global level, then add schema-scoped grants for the roles that should use new objects. Database CONNECT, schema USAGE, and schema CREATE privileges remain separate from future-object defaults.

1. Confirm the database, schema, DDL creator role, runtime role, and an ungranted test role.

   The examples below use database appdb, schema app, creator role app_owner, runtime role app_runtime, and ungranted validation role app_audit. Replace each name with the matching roles and schema from the target database, or create a temporary ungranted role in staging for the final check.

2. Connect to the target database with a role that can manage the creator role defaults.

   $ psql --dbname=appdb --username=postgres
   Password for user postgres:
   psql (PostgreSQL) 18.4 (Debian 18.4-1.pgdg13+1)
   Type "help" for help.
   
   appdb=#

   The session role must be a superuser or a member of the role named in FOR ROLE.

3. List schemas to confirm where the creator role creates application objects.

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

   Use IN SCHEMA for grants that should apply only to future objects in one schema. Omit IN SCHEMA only when changing the global default for that object type in the current database.

4. Review existing default privileges before changing them.

   appdb=# \ddp
            Default access privileges
    Owner | Schema | Type | Access privileges 
   -------+--------+------+-------------------
   (0 rows)

   No rows means the role has no custom default privilege entries. PostgreSQL can still apply built-in defaults such as PUBLIC access to newly created routines and types.

5. Remove PUBLIC access from future routines and types created by the owner role.

   appdb=# ALTER DEFAULT PRIVILEGES FOR ROLE app_owner
   REVOKE EXECUTE ON ROUTINES FROM PUBLIC;
   ALTER DEFAULT PRIVILEGES
   appdb=# ALTER DEFAULT PRIVILEGES FOR ROLE app_owner
   REVOKE USAGE ON TYPES FROM PUBLIC;
   ALTER DEFAULT PRIVILEGES

   Do not add IN SCHEMA to these two REVOKE commands when the goal is to remove PostgreSQL's built-in global PUBLIC defaults. Per-schema default privileges are added to global defaults, so a per-schema revoke cannot remove a privilege that was granted globally.

   ROUTINES is the standard term in supported PostgreSQL documentation, and FUNCTIONS remains accepted for this command. Use FUNCTIONS when an older target release does not recognize ROUTINES.

6. Grant only the required future-object privileges to the runtime role in the application schema.

   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 ROUTINES 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, TRIGGER, or MAINTAIN for tables only when the runtime role needs those actions. Repeat the pattern for each schema or creator role that performs DDL.

7. Confirm the default privilege entries.

   appdb=# \ddp
                    Default access privileges
      Owner   | Schema |   Type   |     Access privileges      
   -----------+--------+----------+----------------------------
    app_owner | app    | function | app_runtime=X/app_owner
    app_owner | app    | sequence | app_runtime=rwU/app_owner
    app_owner | app    | table    | app_runtime=arwd/app_owner
    app_owner | app    | type     | app_runtime=U/app_owner
    app_owner |        | function | app_owner=X/app_owner
    app_owner |        | type     | app_owner=U/app_owner
   (6 rows)

   A blank Schema column is a global default in the current database. The owner-only function and type rows show that the built-in PUBLIC default was removed for future routines and types created by app_owner.

8. Test future-object inheritance inside a transaction, then roll it back.

   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=*# CREATE FUNCTION app.priv_test_fn() RETURNS INTEGER LANGUAGE SQL AS $$ SELECT 1 $$;
   CREATE FUNCTION
   appdb=*# CREATE TYPE app.priv_test_status AS ENUM ('new', 'done');
   CREATE TYPE
   appdb=*# RESET ROLE;
   RESET
   appdb=*# SELECT has_table_privilege('app_runtime', 'app.priv_test', 'SELECT') AS rt_table,
          has_sequence_privilege('app_runtime', 'app.priv_test_id_seq', 'USAGE') AS rt_seq,
          has_function_privilege('app_runtime', 'app.priv_test_fn()', 'EXECUTE') AS rt_routine,
          has_type_privilege('app_runtime', 'app.priv_test_status', 'USAGE') AS rt_type;
    rt_table | rt_seq | rt_routine | rt_type 
   ----------+--------+------------+---------
    t        | t      | t          | t
   (1 ROW)
    
   appdb=*# SELECT has_function_privilege('app_audit', 'app.priv_test_fn()', 'EXECUTE') AS audit_routine,
          has_type_privilege('app_audit', 'app.priv_test_status', 'USAGE') AS audit_type;
    audit_routine | audit_type 
   ---------------+------------
    f             | f
   (1 ROW)
    
   appdb=*# ROLLBACK;
   ROLLBACK

   The runtime role can use the future objects, while the ungranted role no longer receives routine or type access through PUBLIC.