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.

Steps to harden default privileges in PostgreSQL:

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 (PostgreSQL) 16.11 (Ubuntu 16.11-0ubuntu0.24.04.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.

List schemas to confirm the schema where the creator role creates 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 to scope defaults to a single schema, or omit it to apply defaults to all schemas in the database.

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.

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.

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.

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.

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