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

On Fri, 2026-09-11 at 09:45 +0200, Sylvain PERRAUD wrote:
> De: "Laurenz Albe" <laurenz(dot)albe(at)cybertec(dot)at>
> > 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

Exactly. Initially, there is a NULL in the system catalog, so you see nothing
in \ddp, which means "default default privileges".

See the documentation for a description of all that:
https://www.postgresql.org/docs/current/ddl-priv.html

Your statement was a no-operation, so nothing changed in the system catalog.

In scenario three, there were additional ACL items, so it was no longer the default value.

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

The error message is correct. There is an empty ACL owned by "alpha", which prevents
you from dropping the role. You have to drop that empty ACL.

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

You don't have to guess.
You modified the default privileges by dropping the ACL item for the table owner.
So you need to grant it again to restore the default.

> > > Scenario 3 [...]
> > >
> >
> 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.

Sure. That's because you are thinking wrongly. By default, the object owner
*does* have privileges on the table, so you shouldn't revoke that to restore
the default.

I'm not saying that all this is evident. It is complicated, and you should spend
some time with the documentation and experiment.

But there is no bug here, so you are on the wrong list.
The pgsql-general is more appropriate when you are asking for advice.

Yours,
Laurenz Albe