public inbox for [email protected]
help / color / mirror / Atom feedDROP ROLE blocked by pg_init_privs
5+ messages / 3 participants
[nested] [flat]
* DROP ROLE blocked by pg_init_privs
@ 2025-11-24 15:59 immerrr again <[email protected]>
2025-11-25 22:49 ` Re: DROP ROLE blocked by pg_init_privs Pavel Luzanov <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: immerrr again @ 2025-11-24 15:59 UTC (permalink / raw)
To: [email protected]
Hi everyone,
First time trying to configure a PG cluster by the book, I want to create a
role with read permissions on all current and future tables in the current
db. It looks smth like this
CREATE ROLE test_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO test_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO test_role;
I've been trying out different scenarios for the future, and currently having
a problem when trying to remove "test_role" after adding an extension.
CREATE EXTENSION pg_stat_statements;
DROP ROLE test_role;
The error is as follows:
role "test_role" cannot be dropped because some objects depend on it
DETAIL: privileges for default privileges on new relations belonging
to role postgres in schema public
privileges for view pg_stat_statements_info
initial privileges for view pg_stat_statements_info
privileges for view pg_stat_statements
initial privileges for view pg_stat_statements
Time: 0.001s
I revoke all permissions I know how, but it still won't let me drop "test_role":
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE SELECT ON TABLES FROM
test_role;
REVOKE SELECT ON TABLE public.pg_stat_statements_info FROM test_role;
REVOKE SELECT ON TABLE public.pg_stat_statements FROM test_role;
Expectedly, the error still mentions initial privileges:
role "test_role" cannot be dropped because some objects depend on it
DETAIL: initial privileges for view pg_stat_statements_info
initial privileges for view pg_stat_statements
I haven't been able to find much information on initial privileges and how to
manage them. I know that "REASSIGN OWNED BY" doesn't touch them, but "DROP
OWNED BY" does, but I'm a bit worried that DROP can remove other non-ACL
stuff that was actually created by the role.
I have come up with a solution that lets me clean up the initial privileges,
by repacking pg_init_privs.initprivs and and manually removing stuff from
pg_shdepends, but it feels super hacky:
UPDATE pg_init_privs
-- repacks ['postgres=arwdDxtm/postgres', 'test_role=r/postgres', '=r/postgres']
-- into ['postgres=arwdDxtm/postgres', '=r/postgres']
SET initprivs = (
SELECT array_agg(aclitem) FROM (
SELECT makeaclitem(
ip.grantee,
ip.grantor,
string_agg(ip.privilege_type, ','),
ip.is_grantable
) as aclitem
FROM aclexplode(initprivs) AS ip
WHERE ip.grantee != (SELECT oid FROM pg_roles WHERE rolname = 'test_role')
GROUP BY ip.grantor, ip.grantee, ip.is_grantable
) AS aclitems
)
WHERE privtype = 'e'
AND EXISTS (
SELECT 1
FROM aclexplode(initprivs) AS ip
WHERE ip.grantee = (SELECT oid FROM pg_roles WHERE rolname = 'test_role')
);
DELETE FROM pg_shdepend
WHERE deptype = 'i'
AND refclassid = 'pg_authid'::regclass
AND refobjid = (SELECT oid FROM pg_roles WHERE rolname = 'test_role')
So my questions are:
- Am I doing something with the initial role configuration?
- Is there a SQL command to drop the initial privileges safely?
- If not, should there be one?
Thanks!
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: DROP ROLE blocked by pg_init_privs
2025-11-24 15:59 DROP ROLE blocked by pg_init_privs immerrr again <[email protected]>
@ 2025-11-25 22:49 ` Pavel Luzanov <[email protected]>
2025-11-25 23:00 ` Re: DROP ROLE blocked by pg_init_privs immerrr again <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Pavel Luzanov @ 2025-11-25 22:49 UTC (permalink / raw)
To: immerrr again <[email protected]>; [email protected]
Hi
On 24.11.2025 18:59, immerrr again wrote:
> First time trying to configure a PG cluster by the book, I want to create a
> role with read permissions on all current and future tables in the current
> db. It looks smth like this
>
> CREATE ROLE test_role;
> GRANT SELECT ON ALL TABLES IN SCHEMA public TO test_role;
> ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO test_role;
>
> I've been trying out different scenarios for the future, and currently having
> a problem when trying to remove "test_role" after adding an extension.
Hm, I have checked your example, it works as expected:
postgres@postgres(16.9)=# CREATE ROLE test_role;
CREATE ROLE
postgres@postgres(16.9)=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO
test_role;
GRANT
postgres@postgres(16.9)=# ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO test_role;
ALTER DEFAULT PRIVILEGES
postgres@postgres(16.9)=# CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION
postgres@postgres(16.9)=# REVOKE SELECT ON ALL TABLES IN SCHEMA public
FROM test_role;
REVOKE
postgres@postgres(16.9)=# ALTER DEFAULT PRIVILEGES IN SCHEMA public
REVOKE SELECT ON TABLES FROM test_role;
ALTER DEFAULT PRIVILEGES
postgres@postgres(16.9)=# DROP ROLE test_role;
DROP ROLE
postgres@postgres(16.9)=# DROP EXTENSION pg_stat_statements;
DROP EXTENSION
In any case, since v14 you can use the predefined role pg_read_all_data.
--
Pavel Luzanov
Postgres Professional:https://postgrespro.com
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: DROP ROLE blocked by pg_init_privs
2025-11-24 15:59 DROP ROLE blocked by pg_init_privs immerrr again <[email protected]>
2025-11-25 22:49 ` Re: DROP ROLE blocked by pg_init_privs Pavel Luzanov <[email protected]>
@ 2025-11-25 23:00 ` immerrr again <[email protected]>
2025-11-25 23:06 ` Re: DROP ROLE blocked by pg_init_privs immerrr again <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: immerrr again @ 2025-11-25 23:00 UTC (permalink / raw)
To: Pavel Luzanov <[email protected]>; +Cc: [email protected]
Hi,
Thank you for replying. Great to know about pg_read_all_data, will have a
look at that.
Re: it works, not sure, can't make it work on my side. Here's a full repro:
[nix-shell:~]$ docker run --rm -ti -p 5555:5432 -e
POSTGRES_PASSWORD=pg_test_init_privs --name pg_test_init_privs -d
postgres:16.9
ae9fe66613867d4db6019bbc0806ef57b5bf7e8b83b10ee0dbb422c2d146d701
[nix-shell:~]$ psql postgres://postgres:pg_test_init_privs@localhost:5555
<<EOF
CREATE ROLE test_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO test_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO
test_role;
CREATE EXTENSION pg_stat_statements;
DROP ROLE test_role;
EOF
CREATE ROLE
GRANT
ALTER DEFAULT PRIVILEGES
CREATE EXTENSION
ERROR: role "test_role" cannot be dropped because some objects depend on it
DETAIL: privileges for default privileges on new relations belonging to
role postgres in schema public
privileges for view pg_stat_statements_info
privileges for view pg_stat_statements
Is there some difference in the configuration that I'm not accounting for?
Thanks
On Tue, Nov 25, 2025 at 11:49 PM Pavel Luzanov <[email protected]>
wrote:
> Hi
>
> On 24.11.2025 18:59, immerrr again wrote:
>
> First time trying to configure a PG cluster by the book, I want to create a
> role with read permissions on all current and future tables in the current
> db. It looks smth like this
>
> CREATE ROLE test_role;
> GRANT SELECT ON ALL TABLES IN SCHEMA public TO test_role;
> ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO test_role;
>
> I've been trying out different scenarios for the future, and currently having
> a problem when trying to remove "test_role" after adding an extension.
>
>
> Hm, I have checked your example, it works as expected:
>
> postgres@postgres(16.9)=# CREATE ROLE test_role;
> CREATE ROLE
> postgres@postgres(16.9)=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO
> test_role;
> GRANT
> postgres@postgres(16.9)=# ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT
> SELECT ON TABLES TO test_role;
> ALTER DEFAULT PRIVILEGES
> postgres@postgres(16.9)=# CREATE EXTENSION pg_stat_statements;
> CREATE EXTENSION
>
> postgres@postgres(16.9)=# REVOKE SELECT ON ALL TABLES IN SCHEMA public
> FROM test_role;
> REVOKE
> postgres@postgres(16.9)=# ALTER DEFAULT PRIVILEGES IN SCHEMA public
> REVOKE SELECT ON TABLES FROM test_role;
> ALTER DEFAULT PRIVILEGES
> postgres@postgres(16.9)=# DROP ROLE test_role;
> DROP ROLE
> postgres@postgres(16.9)=# DROP EXTENSION pg_stat_statements;
> DROP EXTENSION
>
> In any case, since v14 you can use the predefined role pg_read_all_data.
>
> --
> Pavel Luzanov
> Postgres Professional: https://postgrespro.com
>
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: DROP ROLE blocked by pg_init_privs
2025-11-24 15:59 DROP ROLE blocked by pg_init_privs immerrr again <[email protected]>
2025-11-25 22:49 ` Re: DROP ROLE blocked by pg_init_privs Pavel Luzanov <[email protected]>
2025-11-25 23:00 ` Re: DROP ROLE blocked by pg_init_privs immerrr again <[email protected]>
@ 2025-11-25 23:06 ` immerrr again <[email protected]>
2025-11-26 00:08 ` Re: DROP ROLE blocked by pg_init_privs Tom Lane <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: immerrr again @ 2025-11-25 23:06 UTC (permalink / raw)
To: Pavel Luzanov <[email protected]>; +Cc: [email protected]
Sorry, nvm, I forgot to drop the privileges in the repro. Can confirm, with
16.9 it works as expected:
[nix-shell:~]$ docker run --rm -ti -p 5555:5432 -e
POSTGRES_PASSWORD=pg_test_init_privs --name pg_test_init_privs -d
postgres:16.9
564ab91604018ff903c428f6a8659207a4071c30dbb94d206b20cb4f5e8d1635
[nix-shell:~]$ psql postgres://postgres:pg_test_init_privs@localhost:5555
<<EOF
CREATE ROLE test_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO test_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO
test_role;
CREATE EXTENSION pg_stat_statements;
REVOKE SELECT ON ALL TABLES IN SCHEMA public FROM test_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE SELECT ON TABLES FROM
test_role;
DROP ROLE test_role;
EOF
CREATE ROLE
GRANT
ALTER DEFAULT PRIVILEGES
CREATE EXTENSION
REVOKE
ALTER DEFAULT PRIVILEGES
DROP ROLE
The same script with postgres:17 image fails with the error I have been
describing:
[nix-shell:~]$ docker run --rm -ti -p 5555:5432 -e
POSTGRES_PASSWORD=pg_test_init_privs --name pg_test_init_privs -d
*postgres:17*
5be292dbe145375e0717f722d622f3be2b7e2764b225253fbc33ea1f9d13f3e7
[nix-shell:~]$ psql postgres://postgres:pg_test_init_privs@localhost:5555
<<EOF
CREATE ROLE test_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO test_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO
test_role;
CREATE EXTENSION pg_stat_statements;
REVOKE SELECT ON ALL TABLES IN SCHEMA public FROM test_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE SELECT ON TABLES FROM
test_role;
DROP ROLE test_role;
EOF
CREATE ROLE
GRANT
ALTER DEFAULT PRIVILEGES
CREATE EXTENSION
REVOKE
ALTER DEFAULT PRIVILEGES
*ERROR: role "test_role" cannot be dropped because some objects depend on
itDETAIL: initial privileges for view pg_stat_statements_infoinitial
privileges for view pg_stat_statements*
On Wed, Nov 26, 2025 at 12:00 AM immerrr again <[email protected]> wrote:
> Hi,
>
> Thank you for replying. Great to know about pg_read_all_data, will have a
> look at that.
>
> Re: it works, not sure, can't make it work on my side. Here's a full repro:
>
> [nix-shell:~]$ docker run --rm -ti -p 5555:5432 -e
> POSTGRES_PASSWORD=pg_test_init_privs --name pg_test_init_privs -d
> postgres:16.9
> ae9fe66613867d4db6019bbc0806ef57b5bf7e8b83b10ee0dbb422c2d146d701
>
> [nix-shell:~]$ psql postgres://postgres:pg_test_init_privs@localhost:5555
> <<EOF
> CREATE ROLE test_role;
> GRANT SELECT ON ALL TABLES IN SCHEMA public TO test_role;
> ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO
> test_role;
> CREATE EXTENSION pg_stat_statements;
> DROP ROLE test_role;
> EOF
>
> CREATE ROLE
> GRANT
> ALTER DEFAULT PRIVILEGES
> CREATE EXTENSION
> ERROR: role "test_role" cannot be dropped because some objects depend on
> it
> DETAIL: privileges for default privileges on new relations belonging to
> role postgres in schema public
> privileges for view pg_stat_statements_info
> privileges for view pg_stat_statements
>
> Is there some difference in the configuration that I'm not accounting for?
>
> Thanks
>
>
> On Tue, Nov 25, 2025 at 11:49 PM Pavel Luzanov <[email protected]>
> wrote:
>
>> Hi
>>
>> On 24.11.2025 18:59, immerrr again wrote:
>>
>> First time trying to configure a PG cluster by the book, I want to create a
>> role with read permissions on all current and future tables in the current
>> db. It looks smth like this
>>
>> CREATE ROLE test_role;
>> GRANT SELECT ON ALL TABLES IN SCHEMA public TO test_role;
>> ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO test_role;
>>
>> I've been trying out different scenarios for the future, and currently having
>> a problem when trying to remove "test_role" after adding an extension.
>>
>>
>> Hm, I have checked your example, it works as expected:
>>
>> postgres@postgres(16.9)=# CREATE ROLE test_role;
>> CREATE ROLE
>> postgres@postgres(16.9)=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO
>> test_role;
>> GRANT
>> postgres@postgres(16.9)=# ALTER DEFAULT PRIVILEGES IN SCHEMA public
>> GRANT SELECT ON TABLES TO test_role;
>> ALTER DEFAULT PRIVILEGES
>> postgres@postgres(16.9)=# CREATE EXTENSION pg_stat_statements;
>> CREATE EXTENSION
>>
>> postgres@postgres(16.9)=# REVOKE SELECT ON ALL TABLES IN SCHEMA public
>> FROM test_role;
>> REVOKE
>> postgres@postgres(16.9)=# ALTER DEFAULT PRIVILEGES IN SCHEMA public
>> REVOKE SELECT ON TABLES FROM test_role;
>> ALTER DEFAULT PRIVILEGES
>> postgres@postgres(16.9)=# DROP ROLE test_role;
>> DROP ROLE
>> postgres@postgres(16.9)=# DROP EXTENSION pg_stat_statements;
>> DROP EXTENSION
>>
>> In any case, since v14 you can use the predefined role pg_read_all_data.
>>
>> --
>> Pavel Luzanov
>> Postgres Professional: https://postgrespro.com
>>
>>
>>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: DROP ROLE blocked by pg_init_privs
2025-11-24 15:59 DROP ROLE blocked by pg_init_privs immerrr again <[email protected]>
2025-11-25 22:49 ` Re: DROP ROLE blocked by pg_init_privs Pavel Luzanov <[email protected]>
2025-11-25 23:00 ` Re: DROP ROLE blocked by pg_init_privs immerrr again <[email protected]>
2025-11-25 23:06 ` Re: DROP ROLE blocked by pg_init_privs immerrr again <[email protected]>
@ 2025-11-26 00:08 ` Tom Lane <[email protected]>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2025-11-26 00:08 UTC (permalink / raw)
To: immerrr again <[email protected]>; +Cc: Pavel Luzanov <[email protected]>; [email protected]
immerrr again <[email protected]> writes:
> Sorry, nvm, I forgot to drop the privileges in the repro. Can confirm, with
> 16.9 it works as expected:
> ...
> The same script with postgres:17 image fails with the error I have been
> describing:
The missing step here is
DROP OWNED BY test_role;
You have to use that to get rid of "initial privileges" because
neither REVOKE nor ALTER DEFAULT PRIVILEGES will touch existing
initial privileges. Pre-v17 didn't account for this properly
and would allow you to drop the role anyway, leaving dangling
entries behind in pg_init_privs. While those are harmless in
isolation, they cause problems for later dump/restore activity.
See discussion here:
https://www.postgresql.org/message-id/flat/1745535.1712358659%40sss.pgh.pa.us
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2025-11-26 00:08 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-11-24 15:59 DROP ROLE blocked by pg_init_privs immerrr again <[email protected]>
2025-11-25 22:49 ` Pavel Luzanov <[email protected]>
2025-11-25 23:00 ` immerrr again <[email protected]>
2025-11-25 23:06 ` immerrr again <[email protected]>
2025-11-26 00:08 ` Tom Lane <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox