PostgreSQL Default Privileges and Role Membership

Roles and Authentication / Guide 20 of 47 / Advanced

• OSUbuntu 26.04 LTS
• PostgreSQL18.6-0ubuntu0.26.04.1
• Authscram-sha-256
• TimeAbout 16 min

Default privileges are recorded per granting role and per schema. A table created by a different role is not covered, which is the commonest reason they appear not to work.

This guide includes

Use this when a grant stops covering new tables. This matters because a grant applies to what exists at the moment you make it and nothing created afterwards - which is the surprise that produces permission bugs weeks later.

• creating a new table, and watching the existing grant fail to cover it
• granting for the future with ALTER DEFAULT PRIVILEGES
• fixing the past with a group role, since default privileges do nothing retroactively

Before you start

• The analyst role with its grants from the previous guide.

1. 1

   Create a new table and watch the grant not cover it

2. 2

   Grant for the future

3. 3

   Fix the past with a group role

Official sources

• PostgreSQL 18 Documentation - Database Roles
• PostgreSQL 18 Documentation - The pg_hba.conf File
• PostgreSQL 18 Documentation - Password Authentication
• PostgreSQL 18 Documentation - Privileges
• PostgreSQL 18 Documentation - ALTER DEFAULT PRIVILEGES