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 > > >
