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.94.2)
	(envelope-from <pgsql-general-owner+archive@lists.postgresql.org>)
	id 1sgg6f-0063jP-TT
	for pgsql-general@arkaria.postgresql.org; Wed, 21 Aug 2024 07:50:14 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.94.2)
	(envelope-from <pgsql-general-owner+archive@lists.postgresql.org>)
	id 1sgg6d-0080a7-SU
	for pgsql-general@arkaria.postgresql.org; Wed, 21 Aug 2024 07:50:12 +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.94.2)
	(envelope-from <p.luzanov@postgrespro.ru>)
	id 1sgg6d-0080Xg-8R
	for pgsql-general@lists.postgresql.org; Wed, 21 Aug 2024 07:50:12 +0000
Received: from mail.postgrespro.ru ([93.174.131.139])
	by makus.postgresql.org with esmtps  (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.94.2)
	(envelope-from <p.luzanov@postgrespro.ru>)
	id 1sgg6Y-000hFP-JD
	for pgsql-general@lists.postgresql.org; Wed, 21 Aug 2024 07:50:10 +0000
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru;
	s=mx2023; t=1724226602;
	bh=LhyyEn1w13rcriBVVTRA0KDoaV5OUGd9+xFDlA7BJzY=;
	h=Message-ID:Date:User-Agent:Subject:To:References:From:In-Reply-To:
	 From;
	b=t+JL+BdTGT1Hb/gLtiUI2qo6JUsjQr59uO2QBTG/zForlv458XHPxV99FdWzuNPjU
	 eFUd6VvcaUmPp04bYXbcNRcVIwEk9STdbfzYzU8mUk2IEF40mMhoLSyrv/+pLgaonA
	 kCgmO4S3Z8qXK3yoczS0aLswy7jL7Sov4d2gNzYD1Dp8qsv3ZprWA7FtDNdKStpydS
	 e/fe5G4AkTbqutXYAst+8ezNKoYOUwZGTN0uJHkVCpezp5j2Fclv6tsnO3dWNxSarW
	 rvZRr/G4QY7BoF2mLTB3fMqVUJvye4fhg5JFkVCZrN/sXWiv6v18VXHsmPMy2Dw0fU
	 +Mun2Y+HqhwDQ==
Received: from [192.168.0.104] (unknown [62.217.185.30])
	(using TLSv1.3 with cipher TLS_AES_128_GCM_SHA256 (128/128 bits)
	 key-exchange X25519 server-signature RSA-PSS (2048 bits) server-digest SHA256)
	(Client did not present a certificate)
	(Authenticated sender: p.luzanov@postgrespro.ru)
	by mail.postgrespro.ru (Postfix/587) with ESMTPSA id 94AAB60136;
	Wed, 21 Aug 2024 10:50:02 +0300 (MSK)
Content-Type: multipart/alternative;
 boundary="------------ilWoukxN7D5b4pAhKShSEwM0"
Message-ID: <f3a638ed-afea-4a4d-891f-840ac3846525@postgrespro.ru>
Date: Wed, 21 Aug 2024 10:50:02 +0300
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: insufficient privilege with pg_read_all_stats granted
To: Costa Alexoglou <costa@dbtune.com>, pgsql-general@lists.postgresql.org
References: <CAJ+5Ff4rRJabEP5Qz=XkRrq+4zc7b9Ai3+zqCA+GYSurFsgvjg@mail.gmail.com>
Content-Language: en-US, ru-RU
From: Pavel Luzanov <p.luzanov@postgrespro.ru>
In-Reply-To: <CAJ+5Ff4rRJabEP5Qz=XkRrq+4zc7b9Ai3+zqCA+GYSurFsgvjg@mail.gmail.com>
X-KSMG-AntiPhishing: NotDetected, bases: 2024/08/21 05:09:00
X-KSMG-AntiSpam-Interceptor-Info: not scanned
X-KSMG-AntiSpam-Status: not scanned, disabled by settings
X-KSMG-AntiVirus: Kaspersky Secure Mail Gateway, version 2.1.0.7854, bases: 2024/08/21 06:50:00 #26388227
X-KSMG-AntiVirus-Status: NotDetected, skipped
X-KSMG-LinksScanning: not scanned, disabled by settings
X-KSMG-Message-Action: skipped
X-KSMG-Rule-ID: 1
List-Id: <pgsql-general.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-general@lists.postgresql.org>
List-Owner: <mailto:pgsql-general-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-general>
Archived-At: <https://www.postgresql.org/message-id/f3a638ed-afea-4a4d-891f-840ac3846525%40postgrespro.ru>
Precedence: bulk

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

On 20.08.2024 23:50, Costa Alexoglou wrote:
> I run PostgreSQL v15.8 (docker official image), and there is an issue 
> when reading pg_stat_staments table with a result of query most of the 
> times having `<insufficient privilege>` value.
>
> I have created the user that I use to fetch the data with the 
> following way:
> ```
> CREATE USER abcd WITH NOSUPERUSER NOCREATEROLE NOINHERIT LOGIN;
>
> GRANT pg_read_all_stats, pg_stat_scan_tables, pg_read_all_settings to 
> abcd;

I think the problem is in the NOINHERIT attribute for the abcd role.
abcd does not inherit the privileges gained from being included in other roles.

In v15, to see the text of SQL commands in pg_stat_statements, you can either explicitly
switch from abcd role to the pg_read_all_stats role (SET ROLE pg_read_all_stats)
or set the INHERIT attribute for abcd role (alter role abcd inherit).

In v16, you can explicitly specify how to get privileges in the GRANT command:

grant pg_read_all_stats to abcd with inherit true, set false;


>
> I also tried with PostgreSQL v14.13, and this was not the case, it was 
> working fine as expected.
> Then I tried v16.4 and v17beta3, and I faced the <insufficient 
> privilege> issue, so I guess something changed v15 onwards?

But I don't understand why it worked in v14.
Probablysomethinghas changed,  butIcouldn't quicklyfindwhatexactly.

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

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

<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    On 20.08.2024 23:50, Costa Alexoglou wrote:<br>
    <blockquote type="cite"
cite="mid:CAJ+5Ff4rRJabEP5Qz=XkRrq+4zc7b9Ai3+zqCA+GYSurFsgvjg@mail.gmail.com">
      <div dir="ltr">
        <div>I run PostgreSQL v15.8 (docker official image), and there
          is an issue when reading pg_stat_staments table with a result
          of query most of the times having `&lt;insufficient
          privilege&gt;` value.</div>
        <div><br>
          I have created the user that I use to fetch the data with the
          following way:<br>
          ```<br>
          CREATE USER abcd WITH NOSUPERUSER NOCREATEROLE NOINHERIT
          LOGIN;<br>
          <br>
          GRANT pg_read_all_stats, pg_stat_scan_tables,
          pg_read_all_settings to abcd;<br>
        </div>
      </div>
    </blockquote>
    <pre>
I think the problem is in the NOINHERIT attribute for the abcd role.
abcd does not inherit the privileges gained from being included in other roles.

In v15, to see the text of SQL commands in pg_stat_statements, you can either explicitly
switch from abcd role to the pg_read_all_stats role (SET ROLE pg_read_all_stats)
or set the INHERIT attribute for abcd role (alter role abcd inherit).

In v16, you can explicitly specify how to get privileges in the GRANT command:

grant pg_read_all_stats to abcd with inherit true, set false;
</pre>
    <br>
    <blockquote type="cite"
cite="mid:CAJ+5Ff4rRJabEP5Qz=XkRrq+4zc7b9Ai3+zqCA+GYSurFsgvjg@mail.gmail.com">
      <div dir="ltr">
        <div><br>
        </div>
        <div>I also tried with PostgreSQL v14.13, and this was not the
          case, it was working fine as expected.<br>
          Then I tried v16.4 and v17beta3, and I faced the
          &lt;insufficient privilege&gt; issue, so I guess something
          changed v15 onwards?</div>
      </div>
    </blockquote>
    <pre>
But I don't understand why it worked in v14.
<span class="EzKURWReUAB5oZgtQNkl" data-src-align="0:8"
    style="white-space: pre-wrap;">Probably</span><span
    style="white-space: pre-wrap;"> </span><span
    class="EzKURWReUAB5oZgtQNkl" data-src-align="9:3:13:2"
    style="white-space: pre-wrap;">something</span><span
    style="white-space: pre-wrap;"> has </span><span
    class="EzKURWReUAB5oZgtQNkl" data-src-align="16:10"
    style="white-space: pre-wrap;">changed</span><span
    class="EzKURWReUAB5oZgtQNkl" data-src-align="26:1"
    style="white-space: pre-wrap;">,</span> <span
    class="EzKURWReUAB5oZgtQNkl" data-src-align="28:2"
    style="white-space: pre-wrap;">but</span><span
    style="white-space: pre-wrap;"> </span><span
    class="EzKURWReUAB5oZgtQNkl" data-src-align="31:1"
    style="white-space: pre-wrap;">I</span><span
    style="white-space: pre-wrap;"> </span><span
    class="EzKURWReUAB5oZgtQNkl" data-src-align="33:7"
    style="white-space: pre-wrap;">couldn</span><span
    style="white-space: pre-wrap;">'t </span><span
    class="EzKURWReUAB5oZgtQNkl" data-src-align="41:6"
    style="white-space: pre-wrap;">quickly</span><span
    style="white-space: pre-wrap;"> </span><span
    class="EzKURWReUAB5oZgtQNkl" data-src-align="48:5"
    style="white-space: pre-wrap;">find</span><span
    style="white-space: pre-wrap;"> </span><span
    class="EzKURWReUAB5oZgtQNkl" data-src-align="54:3"
    style="white-space: pre-wrap;">what</span><span
    style="white-space: pre-wrap;"> </span><span
    class="EzKURWReUAB5oZgtQNkl" data-src-align="58:6"
    style="white-space: pre-wrap;">exactly</span><span
    class="EzKURWReUAB5oZgtQNkl" data-src-align="64:1"
    style="white-space: pre-wrap;">.</span></pre>
    <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>
  </body>
</html>

--------------ilWoukxN7D5b4pAhKShSEwM0--