Re: DROP ROLE blocked by pg_init_privs - Mailing list pgsql-general
| From | immerrr again |
|---|---|
| Subject | Re: DROP ROLE blocked by pg_init_privs |
| Date | |
| Msg-id | CAERznn-SBBqQ3YcdZk9U4mqVQPsVgLisi=EdFzY5Fb7hOQ4g_Q@mail.gmail.com Whole thread Raw |
| In response to | Re: DROP ROLE blocked by pg_init_privs (Pavel Luzanov <p.luzanov@postgrespro.ru>) |
| Responses |
Re: DROP ROLE blocked by pg_init_privs
|
| List | pgsql-general |
Hi,
Thank you for replying. Great to know about pg_read_all_data, will have a look at that.
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
[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 <p.luzanov@postgrespro.ru> wrote:
HiOn 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
pgsql-general by date: