Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vO21D-006m6n-1q for pgsql-general@arkaria.postgresql.org; Tue, 25 Nov 2025 23:00:19 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vO21B-00CPUV-0I for pgsql-general@arkaria.postgresql.org; Tue, 25 Nov 2025 23:00:17 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vO21A-00CPUM-1d for pgsql-general@lists.postgresql.org; Tue, 25 Nov 2025 23:00:16 +0000 Received: from mail-yx1-xb130.google.com ([2607:f8b0:4864:20::b130]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1vO217-001TFk-2A for pgsql-general@lists.postgresql.org; Tue, 25 Nov 2025 23:00:15 +0000 Received: by mail-yx1-xb130.google.com with SMTP id 956f58d0204a3-640f2c9ccbdso5048696d50.1 for ; Tue, 25 Nov 2025 15:00:14 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1764111613; x=1764716413; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=DKz2WGqd6BIqr7AbmLlbMhSe0Bqw0WrFFDCtLeyoqyI=; b=fWn324IGyf2UQZ6J4MwVN1I5Mw1VGDLi0MNATqMglsl2S36kXyzTnu3iaAArLB5cA7 0Me/+iCm+OcdjvZKMMaViqGVtI2jvj3RbymmF+tl0GkuMjLe9rMeXxkekz9QSElIj3Bz qGbf3NIV+R7Ov3l+zq+q4Ssyzso9XUUpctn4BTjRw1qTdvj5iqyfEjXFC6rFTTeQWr5F lSiJQzL/GWXi5RLs58X3pvdpVpbEBV6UG9YY0lGc15uq30kThNKS0+ucCJjbmXfJTGyJ b6aTFxrYU51wSwPEyo77g7bo4kW1dOx2O2BIrM4G49K4+MsTv0qgMEk5UdVENV+VWRzg sm8g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1764111613; x=1764716413; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-gg:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=DKz2WGqd6BIqr7AbmLlbMhSe0Bqw0WrFFDCtLeyoqyI=; b=Vcslzt9xVdebwFh8OIaJ5uaTYLkfJqzvDntyZg3ppKXo4BxfPc/H98E9wwwVuMRwTY xqgzs1BH5oPkho7vBmMCYeAyLpcFMFsI3m8uYFwI21cNrUptKb/hrA3JvK+EQ/+Uye1A DbClak1UDzgaiLjGbkSaxvxRKGzW5BNaDD0nimd3TyTY7rXxWoYVJIEG9gLFo/eCI5md BMrHjUkMi2oGOodorFEwO5sirgzTkzBAN4IXv0RpbyANs+cz5LmReHstUTnWYaRU6wOA L953Jq9NxAiyV0RgSyxLuTlCh3HQ+kmDlJQSDZRK66iMj3Z6ZjfYQKuGVIsz3GpIWSDs xMBQ== X-Gm-Message-State: AOJu0Yyk5Oi7GEZ0kETaN38Ni8oyJdD6uU9PjFXTtHlTppxP8jIA9cHl +SV1S5yRYaJGaUEbdnofnTwZDiLMSH3xkUz1kd+6WIFiu1idGyV8o13n9L/p29NZKCaT7Tz+oOV Hrru+AHusSpTCLlWyi5A9zx3SRcFsTiZRMt8f X-Gm-Gg: ASbGncu9EPRNbHas5zOONWrBRJNG98/WsTqlKklVNkhHJzrtrU9WmevXpr9KejO7x8W jwO3BVVVJ5tftq4J5gq4p3mPopqtschOQPEENuIFj3uIfNTXffLp0RIYXayjs5ncmHFHmptx4AM Kd6FunRj8V44ain7Rpv34Q3N0wWlbdELNELuHXzc5nj4YHXDPFRzcO1S64Nu7fXY5QtNxJ16iiG dogux3UnKfuS/OVrtqnML3N5VLmg/ewsbsjH6Ru6cZJBRHOfA8CY/Hoo4JmuZnjbFudhk8mbiiU CkB7gWw= X-Google-Smtp-Source: AGHT+IH3MalERlTguwc/3SMr2BW/x5HV/b1nzNyzvss9CwoyDB9n4erq8bZf6oPNyUlR00RoxTrUcCOlxSYQmiwvkFY= X-Received: by 2002:a05:690e:849:b0:63f:b4ee:792c with SMTP id 956f58d0204a3-64329320a39mr2463840d50.19.1764111613282; Tue, 25 Nov 2025 15:00:13 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: immerrr again Date: Wed, 26 Nov 2025 00:00:02 +0100 X-Gm-Features: AWmQ_bl2zLOXDq4Zp44phR4MPi0xt5iy6HGjd0-u0gukpLG6ixgSph6h6n_5nyA Message-ID: Subject: Re: DROP ROLE blocked by pg_init_privs To: Pavel Luzanov Cc: pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="0000000000000dccf80644733ec3" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000000dccf80644733ec3 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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=3Dpg_test_init_privs --name pg_test_init_privs -d postgres:16.9 ae9fe66613867d4db6019bbc0806ef57b5bf7e8b83b10ee0dbb422c2d146d701 [nix-shell:~]$ psql postgres://postgres:pg_test_init_privs@localhost:5555 < 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 curren= t > 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 ha= ving > 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)=3D# CREATE ROLE test_role; > CREATE ROLE > postgres@postgres(16.9)=3D# GRANT SELECT ON ALL TABLES IN SCHEMA public T= O > test_role; > GRANT > postgres@postgres(16.9)=3D# ALTER DEFAULT PRIVILEGES IN SCHEMA public GRA= NT > SELECT ON TABLES TO test_role; > ALTER DEFAULT PRIVILEGES > postgres@postgres(16.9)=3D# CREATE EXTENSION pg_stat_statements; > CREATE EXTENSION > > postgres@postgres(16.9)=3D# REVOKE SELECT ON ALL TABLES IN SCHEMA public > FROM test_role; > REVOKE > postgres@postgres(16.9)=3D# ALTER DEFAULT PRIVILEGES IN SCHEMA public > REVOKE SELECT ON TABLES FROM test_role; > ALTER DEFAULT PRIVILEGES > postgres@postgres(16.9)=3D# DROP ROLE test_role; > DROP ROLE > postgres@postgres(16.9)=3D# 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 > > > --0000000000000dccf80644733ec3 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi,

Thank you for replying. Great to know about pg_= read_all_data, will have a look at that.=C2=A0

Re: it wo= rks, not sure, can't make it work on my side. Here's a full repro:<= br>
[nix-shell:~]$ docker run --rm -= ti -p 5555:5432 -e POSTGRES_PASSWORD=3Dpg_test_init_privs --name pg_test_in= it_privs -d postgres:16.9
ae9fe66613867d4db6019bbc0806ef57b5bf7e8b83b10e= e0dbb422c2d146d701

[nix-shell:~]$ psql postgres://postgres:pg_test_i= nit_privs@localhost:5555 <<EOF
CREATE ROLE test_role;
GRANT SEL= ECT ON ALL TABLES IN SCHEMA public TO test_role;
ALTER DEFAULT PRIVILEGE= S 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 PRIVILE= GES
CREATE EXTENSION
ERROR: =C2=A0role "test_role" cannot b= e dropped because some objects depend on it
DETAIL: =C2=A0privileges for= default privileges on new relations belonging to role postgres in schema p= ublic
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=E2= =80=AFPM Pavel Luzanov <p.lu= zanov@postgrespro.ru> wrote:
=20 =20 =20
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_ro=
le;

I've been trying out different scenarios for the future, and currently =
having
a problem when trying to remove "test_role" after adding an exten=
sion.


    Hm, I have checked your example, it works as expected:

    

    postgres@postgres(16.9)=3D# CREATE ROLE
      test_role;

      CREATE ROLE

      postgres@postgres(16.9)=3D# GRANT SELECT ON ALL TABLES IN SCHEMA
      public TO test_role;

      GRANT

      postgres@postgres(16.9)=3D# ALTER DEFAULT PRIVILEGES IN SCHEMA
      public GRANT SELECT ON TABLES TO test_role;

      ALTER DEFAULT PRIVILEGES

      postgres@postgres(16.9)=3D# CREATE EXTENSION pg_stat_statements;

      CREATE EXTENSION

      

      postgres@postgres(16.9)=3D# REVOKE SELECT ON ALL TABLES IN SCHEMA
      public FROM test_role;

      REVOKE

      postgres@postgres(16.9)=3D# ALTER DEFAULT PRIVILEGES IN SCHEMA
      public REVOKE SELECT ON TABLES FROM test_role;

      ALTER DEFAULT PRIVILEGES

      postgres@postgres(16.9)=3D# DROP ROLE test_role;

      DROP ROLE

      postgres@postgres(16.9)=3D# DROP EXTENSION pg_stat_statements;

      DROP EXTENSION

    

    In any case, since v14 you can use the predefined role
    pg_read_all_data.

    
--=20
Pavel Luzanov
Postgres Professional: https://postgrespro.com

    

  





--0000000000000dccf80644733ec3--