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