ALTER DEFAULT PRIVILEGES not working? What we measured

ALTER DEFAULT PRIVILEGES answers with ALTER DEFAULT PRIVILEGES whether or not it will ever apply to anything. The schema and the roles have to exist; beyond that it does not check that the role it is recorded for ever creates a table, or may create anything in that schema. The first sign that it missed is permission denied for table on a table from last night’s migration, for an application role that reads every older table without trouble.

We ran the statement on PostgreSQL 17.11 in the ways a deployment script ends up running it, created tables as different roles afterwards, and checked each one as the application role. Three rules explain every result we got: the default belongs to one creating role, it is read when an object is created and at no other moment, and a per-schema default can add to the global one but not take away from it.

Why is ALTER DEFAULT PRIVILEGES not working?

Most often because it was recorded for a different role than the one that creates the tables. Without FOR ROLE, the statement sets defaults for objects that the role running it will create; run as postgres, it covers tables that postgres creates and no others. The documentation’s parameter list says: “Change default privileges for objects created by the target_role, or the current role if unspecified.”

In our test a migration role d_mig owns schema s and an application role d_app has USAGE on it:

A superuser can name any role in FOR ROLE. An ordinary role needs the privileges of the role it names: a role granted d_mig with inherit false got the same permission denied to change default privileges, until it ran set role d_mig first. A member granted with set false, which inherits but may not SET ROLE, was accepted. Several roles can be listed, for role a, b, and the role running it must qualify for each; for all roles does not exist and is a syntax error.

Which tables does a default privilege cover?

The ones whose creation is done by the target role itself, after the statement was committed. “Created by” means the role the CREATE runs as, which is not always the role that logged in:

The second row is the one that surprises a team: every developer is a member of the migration role, one of them creates a table from a psql session, and the default does not apply. The documentation is explicit: “new object permissions are only affected by the default privileges of the current role, and are not inherited from any roles in which the current role is a member.” Changing the owner afterwards or moving the table into the schema does not apply the default either. (The next migration on such a table fails as well, with must be owner of table.) It is read once, at creation, with the creating role and the schema of that moment.

Who owns the refused table or view? (\d s.*, column Owner)

• Not the migration roleThe default never appliedAnother role created it: a member without SET ROLE, or a superuser. Grant on this table by hand; changing the owner afterwards does not apply the default.
• The migration role

  Does \ddp show a line with that role under Owner, for this schema or with an empty schema, for the type of the object, that names the refused role with the privilege it needs?

  • No, or only under another OwnerRecorded for the wrong role, type or privilegeRun it again with FOR ROLE; sequences and functions each need their own statement.
  • YesThe table was not created there, or not after itIt existed before the default, was created in another schema and moved, or was created by another role and given to the migration role afterwards. Grant on this table by hand.

The three end points are the misses that left us with a refused table on PostgreSQL 17.11. In each of them a GRANT on the existing table is still needed; the default only helps the next one.

What counts as a table?

Everything you can SELECT from except a sequence. With a default on tables only, the application role could read all of these, created by the migration role afterwards: a table, a view, a materialized view, the result of create table … as, the result of select … into, a partitioned table, a partition created later with partition of, and a table made with (like …). The documentation mentions foreign tables as well; we did not test those.

Sequences are a separate type. nextval on a new sequence was refused with permission denied for sequence sq, and with select, insert on tables as the default, an insert into a new table with a serial column failed on permission denied for sequence ser_id_seq, while an insert into one with an identity column worked. After alter default privileges in schema c grant usage on sequences to e_app, the next serial table accepted the insert; the one created before it still needed its own grant. Which statement needs which privilege, sequences included, is in permission denied for table.

Why does a revoke in one schema do nothing?

Because per-schema defaults are added to the global ones, and a revoke per schema only removes what was granted per schema. The documentation: “This means you cannot revoke privileges per-schema if they are granted globally (either by default, or according to a previous ALTER DEFAULT PRIVILEGES command that did not specify a schema).”

“By default” is the part that bites. Functions are executable by PUBLIC through the built-in default, and that counts as global. We tried to change it for one schema:

So the working pattern is to revoke globally and grant back per schema. It matters for SECURITY DEFINER functions in particular: one that is created and has its EXECUTE revoked in a later statement is callable in between by every role with USAGE on the schema, unless the two statements are in one transaction. With the global revoke in place, a function this role newly creates in this database starts without PUBLIC. It changes nothing for functions that exist: after create or replace an older function was still callable by another role, because replacing a function keeps its privileges.

The same rule held for tables. After a global alter default privileges grant select on tables to d_app, a table in s2 was readable, and it stayed readable for new tables after alter default privileges in schema s2 revoke select on tables from d_app.

Where there was no global grant, a per-schema revoke did what it says: a table created before the revoke stayed readable, a table created after it was refused, and the entry disappeared from the catalog. Revoking a default changes no table that already exists.

How do you show the default privileges that are set?

With \ddp in psql, or from pg_default_acl. \ddp partway through our runs:

  Owner   | Schema |   Type   |  Access privileges
----------+--------+----------+----------------------
 d_mig    | s      | function | d_app=X/d_mig
 d_mig    | s      | table    | d_app=ar/d_mig
 d_mig    |        | function | d_mig=X/d_mig
 d_mig    |        | table    | d_mig=arwdDxtm/d_mig+
          |        |          | d_app=r/d_mig
 postgres | s      | table    | d_app=r/postgres

“Owner” is the creating role the line applies to, not the owner of anything that exists. An empty schema is a global default. The third line is what the global revoke from PUBLIC looks like: only the creator itself is listed with execute, and the built-in entry for PUBLIC is not. The last line is the superuser’s statement without FOR ROLE, which has never applied to a table in this database.

The same as a query, with the privileges written out:

select d.defaclrole::regrole as creator,
       case when d.defaclnamespace = 0 then '(all schemas)'
            else d.defaclnamespace::regnamespace::text end as schema,
       d.defaclobjtype as type,   -- r table, S sequence, f function, T type, n schema
       case when a.grantee = 0 then 'PUBLIC'
            else a.grantee::regrole::text end as grantee,
       string_agg(a.privilege_type, ', ' order by a.privilege_type) as privileges
from pg_default_acl d
left join lateral aclexplode(d.defaclacl) a on true
group by 1, 2, 3, 4
order by 1, 2, 3, 4;

A global entry has defaclnamespace 0, not null, and a grant to PUBLIC has grantee 0; cast to a name both print as -, hence the two case expressions. The left join keeps an entry from which everything was revoked, which \ddp shows as (none).

The catalog is per database. The defaults we set in one database did not exist in a second one in the same cluster, although the roles did.

Why can a role with default privileges not be dropped?

Because a default privilege that names the role counts as a dependency, and it has to be cleared in every database where one exists. drop role d_app failed with role "d_app" cannot be dropped because some objects depend on it, and the detail listed, between the tables, lines such as privileges for default privileges on new relations belonging to role d_mig in schema s. drop owned by d_app followed by drop role d_app worked; drop owned by removed the role’s grants and its entries in the default privileges of that database. It also drops every object the role owns there, which for an application role that owns nothing is what you want.

It has to be run in each database. With a default privilege for the role in a second database, drop owned by in the first was not enough: drop role still failed, with 2 objects in database other.

The creating role is held as well. A role for which a default had been recorded could not be dropped: owner of default privileges on new relations belonging to role e_mig2. For this case the documentation says “it is necessary to reverse the changes in its default privileges or use DROP OWNED BY to get rid of the default privileges entry for the role.” For a role that owns tables the order matters. reassign owned by moved a migration role’s table to another role and left the default privileges entry behind, so drop role still failed on that line; drop owned by after it removed the entry, and the reassigned table was still there. drop owned by without the reassign would have dropped the table.

Do you need default privileges for a read-only role?

Not for reading: membership of the predefined role pg_read_all_data covers tables that do not exist yet, in every schema and every database of the cluster. A role with only that membership read a table the migration role created afterwards, in a schema it had no USAGE on, and was refused on insert and on nextval. Row-level security still applied to it: the documentation says the role “does not have the role attribute BYPASSRLS set”, and on a table with a policy for another role only it counted 0 rows.

“All data” is wider than the application’s schema, though: the same role read pg_authid, with the stored password verifier of every role that has one. A read-only role built from grant select on all tables in schema plus a default privilege for the migration role reads the tables in the schemas you name, next to what every role can read. Both recipes side by side, and what such a role can still do besides reading, are in a read-only user in PostgreSQL.

What do default privileges not do?

They grant privileges on new objects and nothing more. A table the migration role created under the default was readable by the application role in full: select count(*) returned both tenants’ rows, and relrowsecurity was f. Row-level security and its policies have to be put on each new table by the migration that creates it. A table that has a tenant_id column and no policy is the widest gap in our row-level security article, and a default privilege makes such a table reachable the moment it exists.

What we did not measure

Foreign tables, types and schemas as object types. Whether pg_dump and pg_restore carry default privileges over, and what a restore as a different role does to them. Managed services where the administrator role is not a superuser. PostgreSQL versions other than 17.11.

Sources: PostgreSQL 17 documentation, ALTER DEFAULT PRIVILEGES and Predefined Roles, read on 13 October 2026.