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 1vO27c-006q2N-1n for pgsql-general@arkaria.postgresql.org; Tue, 25 Nov 2025 23:06:56 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vO27b-00CSYR-0G for pgsql-general@arkaria.postgresql.org; Tue, 25 Nov 2025 23:06:55 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vO27a-00CSYJ-24 for pgsql-general@lists.postgresql.org; Tue, 25 Nov 2025 23:06:55 +0000 Received: from mail-yx1-xb136.google.com ([2607:f8b0:4864:20::b136]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1vO27Y-001VGI-22 for pgsql-general@lists.postgresql.org; Tue, 25 Nov 2025 23:06:54 +0000 Received: by mail-yx1-xb136.google.com with SMTP id 956f58d0204a3-63fc8c337f2so5877009d50.0 for ; Tue, 25 Nov 2025 15:06:51 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1764112010; x=1764716810; 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=7BJ/YRHY6MyItMqhojdvJqbSMDaIJRGBjO1olYcsxFA=; b=DoaUvR7DW4Xjt6sjFsejozcEiY6MwO2Y9+9KTSItJ4ru5AjdUY2kIKLTKDII/BlcLM 9aCP7uoYtC5wR1CuLOm3omMrjHi535fpn2L36DvHPmqbDCDHdu04n/qDsf/Dt4Ft029q mApAz/eMmBebhHyNBtAmn9XWXzYqF6xvSHs6dyKBJuUIols/5Q0Opff3SchaQKTMfqOK nqX/m0/DM1tT2gaG6O28m4b2dsFWfVOf2UQXukz2wRhdUk72nDk7KT3mkZ0Az4XoVtTN RSqcxSTD4C7OlvHsctxj/iqiZMncQW9k7Q76BNDSeTKRzpJ0DPHhGKABzrAWLdmJOcFd Ejew== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1764112010; x=1764716810; 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=7BJ/YRHY6MyItMqhojdvJqbSMDaIJRGBjO1olYcsxFA=; b=PMXPehf7mzRHV1H920F0xmST4esL4u1FLQSy2r5sSUFdzBrQCeBAlwFsECD+DPZi3o IyRt00wrAafs/i4BQzxJQWwGfjhbveGMh4HPgqCUzs5IM69eOp/Lr+nLNE4v4Wen34hi jJ8Vr1JX7E6MQz4EqBOdw7bsrsOeoMpm1JJZcYHJUd+RsbBlKE0/ovJ9wyNrQQqshObB 8UIHKxBAo42vKpgRAUd2GPdnEC+vRjdC0BIm5j7fMiAjm1/KBEbtHzOBY0h2lnQdV6tW L2fgCRL3kYbVoXrPm2bSFc0gxCJe84M8bU9jI4O4y3jksZHw6XutfJdm3SQt59ggZSM0 vbpw== X-Gm-Message-State: AOJu0YzYfSzjxpdoMdQEG6Jz/j+jJjj1YuhGf/CxiGR7OSk3Umj2ct/B i0BPDs1q9bBVWaK9AkNcRwkkCh8rUuZxoOISWP+qshj8jTD3Wms5qLoznmUytRuJ7BMe0oM+Juq K4ryRTABgUuJMTFxUt3A7hXBTWxMo6hO9ZfPe X-Gm-Gg: ASbGncuopr0aF/hPrtldnJybTwSK5NHDu6GJrotJjKieFBiQvHwmAlj9mzzRii9DS0p 7UPN+dr5iOppvM+6KYuS2XtrmrhiIwj9AK9Bch/L7Pf+wRWVJ5h4lxTayKB9ofwwDe9dy2sh0ID 24VDhRG55VkYXO9++gOuSzXR06xcgREMfZkh8ffq4saIOSlfHZ+hgXfpXU8v/mmIoUfG2MunDLh shbk06F5BVXYeS2ywCmbjaAtCbq8z51LfZGDJ78ZLnYH/IQR2MnJxr2JoUC6H1+c0PBajho X-Google-Smtp-Source: AGHT+IFKlUeDqHDythNPQFgPw5hzErGp/zKOAyl7BiEB+hd0AsldcgzRgUFVzvGPQxv7mjR60E2AlXAmDqCpBxrPli8= X-Received: by 2002:a05:690c:7a1:b0:784:9076:a0f5 with SMTP id 00721157ae682-78ab6efd2bamr78256287b3.44.1764112010376; Tue, 25 Nov 2025 15:06:50 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: immerrr again Date: Wed, 26 Nov 2025 00:06:39 +0100 X-Gm-Features: AWmQ_bmze8MOxzSdZwUyPqIU4hL9Xs8yKGf1wvA600aY6DX9Sob3_oNBFElT_Cw 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="000000000000b8fa17064473558b" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000b8fa17064473558b Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Sorry, nvm, I forgot to drop the privileges in the repro. Can confirm, with 16.9 it works as expected: [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 564ab91604018ff903c428f6a8659207a4071c30dbb94d206b20cb4f5e8d1635 [nix-shell:~]$ psql postgres://postgres:pg_test_init_privs@localhost:5555 < = wrote: > 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 repr= o: > > [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 > < 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=E2=80=AFPM Pavel Luzanov > 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 creat= e a >> role with read permissions on all current and future tables in the curre= nt >> 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 h= aving >> 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 = 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. >> >> -- >> Pavel Luzanov >> Postgres Professional: https://postgrespro.com >> >> >> --000000000000b8fa17064473558b Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Sorry, nvm, I forgot to drop the privileges in the repro. = Can confirm, with 16.9 it works as expected:

[nix-shell:~]$ docker run --rm -ti -p 5555:5432 -= e POSTGRES_PASSWORD=3Dpg_test_init_privs --name pg_test_init_privs -d postg= res:16.9
564ab91604018ff903c428f6a8659207a4071c30dbb94d206b20cb4f5e8d163= 5

[nix-shell:~]$ psql postgres://postgres:pg_test_init_privs@localho= st:5555 <<EOF
CREATE ROLE test_role;
GRANT SELECT ON ALL TABLES= IN SCHEMA public TO test_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA publi= c GRANT SELECT ON TABLES TO test_role;
CREATE EXTENSION pg_stat_statemen= ts;
REVOKE SELECT ON ALL TABLES IN SCHEMA public FROM test_role;
ALTE= R DEFAULT PRIVILEGES IN SCHEMA public REVOKE SELECT ON TABLES FROM test_rol= e;
DROP ROLE test_role;
EOF
CREATE ROLE
GRANT
ALTER DEFAULT = PRIVILEGES
CREATE EXTENSION
REVOKE
ALTER DEFAULT PRIVILEGES
DRO= P ROLE

The same script with postgres:17 image= fails with the error I have been describing:

[nix-shell:~]$ docker run --rm -ti -p 5555:5432 -= e POSTGRES_PASSWORD=3Dpg_test_init_privs --name pg_test_init_privs -d postgres:17
5be292dbe14537= 5e0717f722d622f3be2b7e2764b225253fbc33ea1f9d13f3e7

[nix-shell:~]$ ps= ql postgres://postgres:pg_test_init_privs@localhost:5555 <<EOF
CREATE ROLE test_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO t= est_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABL= ES TO test_role;
CREATE EXTENSION pg_stat_statements;
REVOKE SELECT O= N ALL TABLES IN SCHEMA public FROM test_role;
ALTER DEFAULT PRIVILEGES I= N SCHEMA public REVOKE SELECT ON TABLES FROM test_role;
DROP ROLE test_r= ole;
EOF
CREATE ROLE
GRANT
ALTER DEFAULT PRIVILEGES
CREATE E= XTENSION
REVOKE
ALTER DEFAULT PRIVILEGES
ERROR: =C2=A0role "test_role" cannot be dro= pped because some objects depend on it
DETAIL: =C2=A0initial privileges = for view pg_stat_statements_info
initial privileges for view pg_stat_sta= tements


=

On Wed, Nov 26, 2025 at 12:00=E2=80=AFAM immerrr agai= n <immerrr@gmail.com> wrote:=
Hi,

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

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 pos= tgres:16.9
ae9fe66613867d4db6019bbc0806ef57b5bf7e8b83b10ee0dbb422c2d146d= 701

[nix-shell:~]$ psql postgres://postgres:pg_test_init_privs@local= host:5555 <<EOF
CREATE ROLE test_role;
GRANT SELECT ON ALL TABL= ES IN SCHEMA public TO test_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA pub= lic GRANT SELECT ON TABLES TO test_role;
CREATE EXTENSION pg_stat_statem= ents;
DROP ROLE test_role;
EOF

CREATE ROLE
GRANT
ALTER DEFAULT PRIVILEGES
CREATE EXT= ENSION
ERROR: =C2=A0role "test_role" cannot be dropped because= some objects depend on it
DETAIL: =C2=A0privileges for default privileg= es on new relations belonging to role postgres in schema public
privileg= es for view pg_stat_statements_info
privileges for view pg_stat_statemen= ts

Is there some difference in the configura= tion that I'm not accounting for?

Thanks
=


On Tue, Nov 25, 2025 at 11:49=E2=80=AFPM Pavel Luzanov <p.luzanov@postgrespro.ru<= /a>> wrote:
<= u> =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

    

  







--000000000000b8fa17064473558b--