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.

Steps to harden default privileges in PostgreSQL:

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

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.

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

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.

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.

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.