Message-ID: <09325e1fd009ed9ebfbadd8d6be703c51137c1ed.camel@cybertec.at>
Subject: Re: BUG #19682: Unable to drop a user with default privileges
 revoked
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Sylvain PERRAUD <ext.solutec.sperraud@grandlyon.com>
Cc: pgsql-bugs@lists.postgresql.org
Date: Fri, 11 Sep 2026 17:11:30 +0200
In-Reply-To: <1852627963.39112866.1789112747804.JavaMail.zimbra@grandlyon.com>
References: <19682-21342a50fcf153e5@postgresql.org>
	 <d7f3c4bdc993f62bb7f562112b74428ccf338752.camel@cybertec.at>
	 <1852627963.39112866.1789112747804.JavaMail.zimbra@grandlyon.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.60.2 (3.60.2-1.fc44) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/09325e1fd009ed9ebfbadd8d6be703c51137c1ed.camel%40cybertec.at>
Precedence: bulk

On Fri, 2026-09-11 at 09:45 +0200, Sylvain PERRAUD wrote:
> De: "Laurenz Albe" <laurenz.albe@cybertec.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 him=
self
> > >
> > > test=3D# create user alpha;
> > >
> > > test=3D#  ALTER DEFAULT PRIVILEGES FOR ROLE alpha GRANT ALL ON TABLES=
 to alpha;
> > >
> > > test=3D# \ddp alpha
> > >         Default access privileges
> > >  Owner | Schema | Type | Access privileges
> > > -------+--------+------+-------------------
> > > (0 rows)
> > >
> > > test=3D# drop user alpha;
> > >
> > > Conclusion 1 : Drop is working
> >
> > Right, because the ALTER DEFAULT PRIVILEGE did nothing.
>=20
> >=20
> Ok I understand but in this situation, why \ddp is not showing access pri=
vileges
> "alpha=3DarwdDxtm/alpha+" like in scenario 3 ? How can we know the hard-w=
ired default
> privileges if \ddp is not showing anything. Even the view pg_catalog.pg_d=
efault_acl is empty

Exactly.  Initially, there is a NULL in the system catalog, so you see noth=
ing
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=3D# create user alpha;
> > >
> > > test=3D#  ALTER DEFAULT PRIVILEGES FOR ROLE alpha REVOKE  ALL ON TABL=
ES FROM alpha;
> > >
> > > test=3D# \ddp alpha
> > >          Default access privileges
> > >  Owner | Schema | Type  | Access privileges
> > > -------+--------+-------+-------------------
> > >  alpha |        | table | (none)
> > > (1 row)
> > >
> > > test=3D# 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 ro=
le 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.
>=20
> >=20
> So the error message is confusing. It says "owner of default privileges o=
n new
> relations belonging to role alpha" and \ddp is showing "(none)" in acess =
privileges.=20

The error message is correct.  There is an empty ACL owned by "alpha", whic=
h 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 DEFAUL=
T 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 [...]
> > >
> >=20
> Here again it is confusing. When we are in this situation :
>=20
>  test=3D# \ddp alpha
>             Default access privileges
>   Owner | Schema | Type  |  Access privileges
>  -------+--------+-------+----------------------
>   alpha |        | table | alpha=3DarwdDxtm/alpha+
>         |        |       | beta=3DarwdDxtm/alpha
>=20
> We can think that we should revoke default privileges for both users alph=
a and
> beta to be able to drop role alpha. But if we revoke default privileges f=
or
> 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 own=
er
*does* have privileges on the table, so you shouldn't revoke that to restor=
e
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