Content-Type: multipart/alternative;
 boundary="------------CREnfVuxnBrIRJLFeylf9fwo"
Message-ID: <ca3fe7b5-2399-4537-bba4-cd000907bda7@postgrespro.ru>
Date: Wed, 26 Nov 2025 01:49:15 +0300
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: DROP ROLE blocked by pg_init_privs
To: immerrr again <immerrr@gmail.com>, pgsql-general@lists.postgresql.org
References: <CAERznn-QWVpAvqnyF=rZfiuxkeDG0tym_rY+RuEkSPWvzgi67Q@mail.gmail.com>
Content-Language: en-US
From: Pavel Luzanov <p.luzanov@postgrespro.ru>
In-Reply-To: <CAERznn-QWVpAvqnyF=rZfiuxkeDG0tym_rY+RuEkSPWvzgi67Q@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/ca3fe7b5-2399-4537-bba4-cd000907bda7%40postgrespro.ru>
Precedence: bulk

This is a multi-part message in MIME format.
--------------CREnfVuxnBrIRJLFeylf9fwo
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

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


--------------CREnfVuxnBrIRJLFeylf9fwo
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit

<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <font size="2">Hi</font><br>
    <br>
    <div class="moz-cite-prefix">On 24.11.2025 18:59, immerrr again
      wrote:<span style="white-space: pre-wrap">
</span></div>
    <blockquote type="cite"
cite="mid:CAERznn-QWVpAvqnyF=rZfiuxkeDG0tym_rY+RuEkSPWvzgi67Q@mail.gmail.com">
      <pre wrap="" class="moz-quote-pre">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.</pre>
    </blockquote>
    <br>
    <pre class="moz-signature" cols="72"></pre>
    Hm, I have checked your example, it works as expected:<br>
    <br>
    <font face="monospace">postgres@postgres(16.9)=# CREATE ROLE
      test_role;<br>
      CREATE ROLE<br>
      postgres@postgres(16.9)=# GRANT SELECT ON ALL TABLES IN SCHEMA
      public TO test_role;<br>
      GRANT<br>
      postgres@postgres(16.9)=# ALTER DEFAULT PRIVILEGES IN SCHEMA
      public GRANT SELECT ON TABLES TO test_role;<br>
      ALTER DEFAULT PRIVILEGES<br>
      postgres@postgres(16.9)=# CREATE EXTENSION pg_stat_statements;<br>
      CREATE EXTENSION<br>
      <br>
      postgres@postgres(16.9)=# REVOKE SELECT ON ALL TABLES IN SCHEMA
      public FROM test_role;<br>
      REVOKE<br>
      postgres@postgres(16.9)=# ALTER DEFAULT PRIVILEGES IN SCHEMA
      public REVOKE SELECT ON TABLES FROM test_role;<br>
      ALTER DEFAULT PRIVILEGES<br>
      postgres@postgres(16.9)=# DROP ROLE test_role;<br>
      DROP ROLE<br>
      postgres@postgres(16.9)=# DROP EXTENSION pg_stat_statements;<br>
      DROP EXTENSION<br>
    </font><br>
    In any case, since v14 you can use the predefined role
    pg_read_all_data.<br>
    <pre class="moz-signature" cols="72">-- 
Pavel Luzanov
Postgres Professional: <a class="moz-txt-link-freetext" href="https://postgrespro.com">https://postgrespro.com</a></pre>
    <br>
  </body>
</html>

--------------CREnfVuxnBrIRJLFeylf9fwo--