agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedBUG #19682: Unable to drop a user with default privileges revoked
4+ messages / 3 participants
[nested] [flat]
* BUG #19682: Unable to drop a user with default privileges revoked
@ 2026-09-09 09:29 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 4+ messages in thread
From: PG Bug reporting form @ 2026-09-09 09:29 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: ext.solutec.sperraud@grandlyon.com
The following bug has been logged on the website:
Bug reference: 19682
Logged by: Sylvain Perraud
Email address: ext.solutec.sperraud@grandlyon.com
PostgreSQL version: 18.4
Operating system: RHEL 9.8
Description:
Hello,
Scenario 1 : create a user alpha then grant default privileges to himself
test=# create user alpha;
CREATE ROLE
test=# \ddp alpha
Default access privileges
Owner | Schema | Type | Access privileges
-------+--------+------+-------------------
(0 rows)
test=# ALTER DEFAULT PRIVILEGES FOR ROLE alpha GRANT ALL ON TABLES to
alpha;
ALTER DEFAULT PRIVILEGES
test=# \ddp alpha
Default access privileges
Owner | Schema | Type | Access privileges
-------+--------+------+-------------------
(0 rows)
test=# drop user alpha;
DROP ROLE
Conclusion 1 : Drop is working
***********************************************************************************************************************************************************************************
Scenario 2 : create a user alpha then revoke default privileges from himself
test=# create user alpha;
CREATE ROLE
test=# ALTER DEFAULT PRIVILEGES FOR ROLE alpha REVOKE ALL ON TABLES FROM
alpha;
ALTER DEFAULT PRIVILEGES
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
***********************************************************************************************************************************************************************************
Scenario 3 : create a user alpha and beta then grant default privileges to
both users
test=# create user alpha;
CREATE ROLE
test=# create user beta;
CREATE ROLE
test=# ALTER DEFAULT PRIVILEGES FOR ROLE alpha GRANT ALL ON TABLES to beta;
ALTER DEFAULT PRIVILEGES
test=# \ddp alpha
Default access privileges
Owner | Schema | Type | Access privileges
-------+--------+-------+---------------------
alpha | | table | beta=arwdDxtm/alpha
(1 row)
test=# ALTER DEFAULT PRIVILEGES FOR ROLE alpha GRANT ALL ON TABLES to
alpha;
ALTER DEFAULT PRIVILEGES
test=# \ddp alpha
Default access privileges
Owner | Schema | Type | Access privileges
-------+--------+-------+----------------------
alpha | | table | alpha=arwdDxtm/alpha+
| | | beta=arwdDxtm/alpha
(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
test=# ALTER DEFAULT PRIVILEGES FOR ROLE alpha REVOKE ALL ON TABLES FROM
beta;
ALTER DEFAULT PRIVILEGES
test=# \ddp alpha
Default access privileges
Owner | Schema | Type | Access privileges
-------+--------+------+-------------------
(0 rows)
test=# drop user alpha;
DROP ROLE
Conclusion 3 : Drop is working whereas DEFAULT PRIVILEGES are still granted
to alpha
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #19682: Unable to drop a user with default privileges revoked
@ 2026-09-10 13:49 Laurenz Albe <laurenz.albe@cybertec.at>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 4+ messages in thread
From: Laurenz Albe @ 2026-09-10 13:49 UTC (permalink / raw)
To: ext.solutec.sperraud@grandlyon.com; pgsql-bugs@lists.postgresql.org
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.
> 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.
> 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.
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #19682: Unable to drop a user with default privileges revoked
@ 2026-09-11 07:45 Sylvain PERRAUD <ext.solutec.sperraud@grandlyon.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 4+ messages in thread
From: Sylvain PERRAUD @ 2026-09-11 07:45 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #19682: Unable to drop a user with default privileges revoked
@ 2026-09-11 15:11 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Sylvain PERRAUD <ext.solutec.sperraud@grandlyon.com>
0 siblings, 0 replies; 4+ messages in thread
From: Laurenz Albe @ 2026-09-11 15:11 UTC (permalink / raw)
To: Sylvain PERRAUD <ext.solutec.sperraud@grandlyon.com>; +Cc: pgsql-bugs@lists.postgresql.org
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 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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2026-09-11 15:11 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-09-09 09:29 BUG #19682: Unable to drop a user with default privileges revoked PG Bug reporting form <noreply@postgresql.org>
2026-09-10 13:49 ` Laurenz Albe <laurenz.albe@cybertec.at>
2026-09-11 07:45 ` Sylvain PERRAUD <ext.solutec.sperraud@grandlyon.com>
2026-09-11 15:11 ` Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox