Re: DROP ROLE blocked by pg_init_privs

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(at)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(at)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 <immerrr(at)gmail(dot)com> 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(at)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(dot)luzanov(at)postgrespro(dot)ru>
> 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(at)postgres(16.9)=# CREATE ROLE test_role;
>> CREATE ROLE
>> postgres(at)postgres(16.9)=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO
>> test_role;
>> GRANT
>> postgres(at)postgres(16.9)=# ALTER DEFAULT PRIVILEGES IN SCHEMA public
>> GRANT SELECT ON TABLES TO test_role;
>> ALTER DEFAULT PRIVILEGES
>> postgres(at)postgres(16.9)=# CREATE EXTENSION pg_stat_statements;
>> CREATE EXTENSION
>>
>> postgres(at)postgres(16.9)=# REVOKE SELECT ON ALL TABLES IN SCHEMA public
>> FROM test_role;
>> REVOKE
>> postgres(at)postgres(16.9)=# ALTER DEFAULT PRIVILEGES IN SCHEMA public
>> REVOKE SELECT ON TABLES FROM test_role;
>> ALTER DEFAULT PRIVILEGES
>> postgres(at)postgres(16.9)=# DROP ROLE test_role;
>> DROP ROLE
>> postgres(at)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
>>
>>
>>