public inbox for [email protected]  

help / color / mirror / Atom feed

From: Pavel Luzanov <[email protected]>
To: immerrr again <[email protected]>
To: [email protected]
Subject: Re: DROP ROLE blocked by pg_init_privs
Date: Wed, 26 Nov 2025 01:49:15 +0300
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAERznn-QWVpAvqnyF=rZfiuxkeDG0tym_rY+RuEkSPWvzgi67Q@mail.gmail.com>
References: <CAERznn-QWVpAvqnyF=rZfiuxkeDG0tym_rY+RuEkSPWvzgi67Q@mail.gmail.com>

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

view thread (5+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: DROP ROLE blocked by pg_init_privs
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox