Re: DROP ROLE blocked by pg_init_privs

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