agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedBUG #19682: Unable to drop a user with default privileges revoked
2+ messages / 2 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>
2026-09-10 13:49 ` Re: BUG #19682: Unable to drop a user with default privileges revoked Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 2+ 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] 2+ messages in thread
* Re: BUG #19682: Unable to drop a user with default privileges revoked
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>
0 siblings, 0 replies; 2+ 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] 2+ messages in thread
end of thread, other threads:[~2026-09-10 13:49 UTC | newest]
Thread overview: 2+ 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>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox