From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | 杨伯宇(长堂) <yangboyu(dot)yby(at)alibaba-inc(dot)com> |
Cc: | "pgsql-hackers(at)lists(dot)postgresql(dot)org" <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Re: The presence of a NULL "defaclacl" value in pg_default_acl prevents the dropping of a role. |
Date: | 2024-01-02 16:56:12 |
Message-ID: | 4099506.1704214572@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
"=?UTF-8?B?5p2o5Lyv5a6HKOmVv+Wggik=?=" <yangboyu(dot)yby(at)alibaba-inc(dot)com> writes:
> postgres=# create user adminuser;
> CREATE ROLE
> postgres=# create user normaluser;
> CREATE ROLE
> postgres=# alter default privileges for role adminuser grant all on tables to normaluser;
> ALTER DEFAULT PRIVILEGES
> postgres=# alter default privileges for role adminuser revoke all ON tables from adminuser;
> ALTER DEFAULT PRIVILEGES
> postgres=# alter default privileges for role adminuser revoke all ON tables from normaluser;
> ALTER DEFAULT PRIVILEGES
> postgres=# select * from pg_default_acl where pg_get_userbyid(defaclrole) = 'adminuser';
> oid | defaclrole | defaclnamespace | defaclobjtype | defaclacl
> -------+------------+-----------------+---------------+-----------
> 16396 | 16394 | 0 | r | {}
> (1 row)
> postgres=# drop user adminuser ;
> ERROR: role "adminuser" cannot be dropped because some objects depend on it
> DETAIL: owner of default privileges on new relations belonging to role adminuser
This looks perfectly normal to me: the privileges for 'adminuser'
itself are not at the default state. If you then do
regression=# alter default privileges for role adminuser grant all on tables to adminuser ;
ALTER DEFAULT PRIVILEGES
then things are back to normal, and the pg_default_acl entry goes away:
regression=# select * from pg_default_acl;
oid | defaclrole | defaclnamespace | defaclobjtype | defaclacl
-----+------------+-----------------+---------------+-----------
(0 rows)
and you can drop the user:
regression=# drop user adminuser ;
DROP ROLE
You could argue that there's no need to be picky about an entry that
only controls privileges for the user-to-be-dropped, but it is working
as designed and documented.
I fear your proposed patch is likely to break more things than it fixes.
In particular it looks like it would forget the existence of the
user's self-revocation altogether, even before the drop of the user.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Nathan Bossart | 2024-01-02 17:31:20 | verify predefined LWLocks have entries in wait_event_names.txt |
Previous Message | Robert Haas | 2024-01-02 16:31:14 | Re: introduce dynamic shared memory registry |