agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedFrom: Sylvain PERRAUD <ext.solutec.sperraud@grandlyon.com>
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19682: Unable to drop a user with default privileges revoked
Date: Fri, 11 Sep 2026 09:45:47 +0200 (CEST)
Message-ID: <1852627963.39112866.1789112747804.JavaMail.zimbra@grandlyon.com> (raw)
In-Reply-To: <d7f3c4bdc993f62bb7f562112b74428ccf338752.camel@cybertec.at>
References: <19682-21342a50fcf153e5@postgresql.org>
<d7f3c4bdc993f62bb7f562112b74428ccf338752.camel@cybertec.at>
Hello,
My answers between >> <<
Cheers
Sylvain
----- Mail original -----
De: "Laurenz Albe" <laurenz.albe@cybertec.at>
À: "ext solutec sperraud" <ext.solutec.sperraud@grandlyon.com>, pgsql-bugs@lists.postgresql.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
view thread (4+ messages) latest in thread
Message-ID: <1852627963.39112866.1789112747804.JavaMail.zimbra@grandlyon.com>
Permalink: ../1852627963.39112866.1789112747804.JavaMail.zimbra@grandlyon.com/
Also on: postgresql.org/message-id/1852627963.39112866.1789112747804.JavaMail.zimbra@grandlyon.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: ext.solutec.sperraud@grandlyon.com, laurenz.albe@cybertec.at, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19682: Unable to drop a user with default privileges revoked
In-Reply-To: <1852627963.39112866.1789112747804.JavaMail.zimbra@grandlyon.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox