Re: BUG #19682: Unable to drop a user with default privileges revoked

On Wed, 2026-09-09 at 09:29 +0000, PG Bug reporting form wrote:
> PostgreSQL version: 18.4
>
> Scenario 1 : create a user alpha then grant default privileges to himself
>
> test=# create user alpha;
>
> test=# ALTER DEFAULT PRIVILEGES FOR ROLE alpha GRANT ALL ON TABLES to alpha;
>
> test=# \ddp alpha
> Default access privileges
> Owner | Schema | Type | Access privileges
> -------+--------+------+-------------------
> (0 rows)
>
> test=# drop user alpha;
>
> Conclusion 1 : Drop is working

Right, because the ALTER DEFAULT PRIVILEGE did nothing.

> Scenario 2 : create a user alpha then revoke default privileges from himself
>
> test=# create user alpha;
>
> test=# ALTER DEFAULT PRIVILEGES FOR ROLE alpha REVOKE ALL ON TABLES FROM alpha;
>
> test=# \ddp alpha
> Default access privileges
> Owner | Schema | Type | Access privileges
> -------+--------+-------+-------------------
> alpha | | table | (none)
> (1 row)
>
> test=# drop user alpha;
> ERROR: role "alpha" cannot be dropped because some objects depend on it
> DETAIL: owner of default privileges on new relations belonging to role alpha
>
> Conclusion 2 : Drop is not working

Right, because now there are changed default privileges (an entry in pg_default_acl),
which prevents dropping the role.

> Scenario 3 : create a user alpha and beta then grant default privileges to both users
>
> test=# create user alpha;
>
> test=# create user beta;
>
> test=# ALTER DEFAULT PRIVILEGES FOR ROLE alpha GRANT ALL ON TABLES to beta;
>
> test=# \ddp alpha
> Default access privileges
> Owner | Schema | Type | Access privileges
> -------+--------+-------+---------------------
> alpha | | table | beta=arwdDxtm/alpha
> (1 row)

That's not what I see. At this point, I see

Default access privileges
Owner │ Schema │ Type │ Access privileges
═══════╪════════╪═══════╪══════════════════════
alpha │ ∅ │ table │ alpha=arwdDxtm/alpha↵
│ │ │ beta=arwdDxtm/alpha

That's because you didn't revoke the default privileges for the owner,
which are granted by default.

> test=# ALTER DEFAULT PRIVILEGES FOR ROLE alpha GRANT ALL ON TABLES to alpha;
>
> test=# \ddp alpha
> Default access privileges
> Owner | Schema | Type | Access privileges
> -------+--------+-------+----------------------
> alpha | | table | alpha=arwdDxtm/alpha+
> | | | beta=arwdDxtm/alpha
> (1 row)

I see the same thing after that second ALTER DEFAULT PRIVILEGES, because that
statement did nothing. The privileges were already there.

> test=# drop user alpha;
> ERROR: role "alpha" cannot be dropped because some objects depend on it
> DETAIL: owner of default privileges on new relations belonging to role alpha
>
> test=# ALTER DEFAULT PRIVILEGES FOR ROLE alpha REVOKE ALL ON TABLES FROM beta;
>
> test=# \ddp alpha
> Default access privileges
> Owner | Schema | Type | Access privileges
> -------+--------+------+-------------------
> (0 rows)

Right, because removing the default privileges restored the "default" default
privileges, so the entry for removed.

> test=# drop user alpha;
>
> Conclusion 3 : Drop is working whereas DEFAULT PRIVILEGES are still granted to alpha

Right, because those are the default default privileges.

In conclusion, you must have made a mistake somewhere to get that divergent
intermediate result. Other than that, everything is behaving as it should.

Yours,
Laurenz Albe