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

Hello,

My answers between >> <<

Cheers
Sylvain

----- Mail original -----
De: "Laurenz Albe" <laurenz(dot)albe(at)cybertec(dot)at>
À: "ext solutec sperraud" <ext(dot)solutec(dot)sperraud(at)grandlyon(dot)com>, pgsql-bugs(at)lists(dot)postgresql(dot)org
Envoyé: Jeudi 10 Septembre 2026 15:49:00
Objet: 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.

>> Ok I understand but in this situation, why \ddp is not showing access privileges "alpha=arwdDxtm/alpha+" like in scenario 3 ? How can we know the hard-wired default privileges if \ddp is not showing anything. Even the view pg_catalog.pg_default_acl is empty :

test=# select * from pg_catalog.pg_default_acl;
oid | defaclrole | defaclnamespace | defaclobjtype | defaclacl
-----+------------+-----------------+---------------+-----------
(0 rows)

<<

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

>> So the error message is confusing. It says "owner of default privileges on new relations belonging to role alpha" and \ddp is showing "(none)" in acess privileges.
So how can we guess that we should grant default privileges (ALTER DEFAULT PRIVILEGES FOR ROLE alpha GRANT ALL ON TABLES to alpha) to be able to drop 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.

>> Here again it is confusing. When we are in this situation :

test=# \ddp alpha
Default access privileges
Owner | Schema | Type | Access privileges
-------+--------+-------+----------------------
alpha | | table | alpha=arwdDxtm/alpha+
| | | beta=arwdDxtm/alpha

We can think that we should revoke default privileges for both users alpha and beta to be able to drop role alpha. But if we revoke default privileges for user alpha, then we are like scenario 2 and drop is not working.

<<

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