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

Hello,

My answers between >> <<

Cheers
Sylvain

----- Mail original -----
De: "Laurenz Albe" <laurenz.albe@cybertec.at>
=C3=80: "ext solutec sperraud" <ext.solutec.sperraud@grandlyon.com>, pgsql-=
bugs@lists.postgresql.org
Envoy=C3=A9: Jeudi 10 Septembre 2026 15:49:00
Objet: Re: BUG #19682: Unable to drop a user with default privileges revoke=
d

On Wed, 2026-09-09 at 09:29 +0000, PG Bug reporting form wrote:
> PostgreSQL version: 18.4
>=20
> Scenario 1 : create a user alpha then grant default privileges to himself
>=20
> test=3D# create user alpha;
>=20
> test=3D#  ALTER DEFAULT PRIVILEGES FOR ROLE alpha GRANT ALL ON TABLES to =
alpha;
>=20
> test=3D# \ddp alpha
>          Default access privileges
>  Owner | Schema | Type | Access privileges
> -------+--------+------+-------------------
> (0 rows)
>=20
> test=3D# 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 pr=
ivileges "alpha=3DarwdDxtm/alpha+" like in scenario 3 ? How can we know the=
 hard-wired default privileges if \ddp is not showing anything. Even the vi=
ew pg_catalog.pg_default_acl is empty :

test=3D# 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 hims=
elf
>
> test=3D# create user alpha;
>=20
> test=3D#  ALTER DEFAULT PRIVILEGES FOR ROLE alpha REVOKE  ALL ON TABLES F=
ROM alpha;
>=20
> test=3D# \ddp alpha
>          Default access privileges
>  Owner | Schema | Type  | Access privileges
> -------+--------+-------+-------------------
>  alpha |        | table | (none)
> (1 row)
>=20
> 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 role a=
lpha
>=20
> Conclusion 2 : Drop is not working

Right, because now there are changed default privileges (an entry in pg_def=
ault_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 a=
cess privileges.=20
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 t=
o both users
>
> test=3D# create user alpha;
>=20
> test=3D# create user beta;
>=20
> test=3D#  ALTER DEFAULT PRIVILEGES FOR ROLE alpha GRANT ALL ON TABLES to =
beta;
>=20
> test=3D# \ddp alpha
>           Default access privileges
>  Owner | Schema | Type  |  Access privileges
> -------+--------+-------+---------------------
>  alpha |        | table | beta=3DarwdDxtm/alpha
> (1 row)

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

           Default access privileges
 Owner =E2=94=82 Schema =E2=94=82 Type  =E2=94=82  Access privileges  =20
=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=AA=E2=
=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=
=AA=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=AA=
=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=
=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=
=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90
 alpha =E2=94=82 =E2=88=85      =E2=94=82 table =E2=94=82 alpha=3DarwdDxtm/=
alpha=E2=86=B5
       =E2=94=82        =E2=94=82       =E2=94=82 beta=3DarwdDxtm/alpha

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

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

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

> 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 role a=
lpha
>=20
> test=3D#  ALTER DEFAULT PRIVILEGES FOR ROLE alpha REVOKE  ALL ON TABLES F=
ROM beta;
>=20
> test=3D# \ddp alpha
>          Default access privileges
>  Owner | Schema | Type | Access privileges
> -------+--------+------+-------------------
> (0 rows)

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

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

Right, because those are the default default privileges.

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

 test=3D# \ddp alpha
            Default access privileges
  Owner | Schema | Type  |  Access privileges
 -------+--------+-------+----------------------
  alpha |        | table | alpha=3DarwdDxtm/alpha+
        |        |       | beta=3DarwdDxtm/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