public inbox for [email protected]  

help / color / mirror / Atom feed

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

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

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: <CAERznn-SBBqQ3YcdZk9U4mqVQPsVgLisi=EdFzY5Fb7hOQ4g_Q@mail.gmail.com>

* 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