MIME-Version: 1.0
From: Costa Alexoglou <costa@dbtune.com>
Date: Tue, 20 Aug 2024 22:50:09 +0200
Message-ID: <CAJ+5Ff4rRJabEP5Qz=XkRrq+4zc7b9Ai3+zqCA+GYSurFsgvjg@mail.gmail.com>
Subject: insufficient privilege with pg_read_all_stats granted
To: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000d9487106202392e8"
Archived-At: <https://www.postgresql.org/message-id/CAJ%2B5Ff4rRJabEP5Qz%3DXkRrq%2B4zc7b9Ai3%2BzqCA%2BGYSurFsgvjg%40mail.gmail.com>
Precedence: bulk

--000000000000d9487106202392e8
Content-Type: text/plain; charset="UTF-8"

Hey folks,

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;

GRANT pg_monitor to abcd;
```

I explicitly gave `pg_read_all_stats` and also called `pg_monitor` just to
be on the safe side, but stil I get the insufficient privilege error.

```
SELECT
   r.rolname AS member,
   m.rolname AS role
FROM
   pg_auth_members am
JOIN
   pg_roles r ON r.oid = am.member
JOIN
   pg_roles m ON m.oid = am.roleid
WHERE
   m.rolname = 'pg_read_all_stats'
   AND r.rolname = 'abcd';

 member |       role
--------+-------------------
 abcd   | pg_read_all_stats
(1 row)
```

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?

--000000000000d9487106202392e8
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hey folks,<div><br></div><div>I run PostgreSQL v15.8 (dock=
er official image), and there is an issue when reading pg_stat_staments tab=
le with a result of query most of the times having `&lt;insufficient privil=
ege&gt;` value.</div><div><br>I have created the user that I use to fetch t=
he 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><br>GRANT pg_monitor to abcd;<br>=
</div><div>```<br><br>I explicitly gave `pg_read_all_stats` and also called=
 `pg_monitor` just to be on=C2=A0the safe side, but stil I get the insuffic=
ient privilege error.<br><br></div><div>```</div><div>SELECT<br>=C2=A0 =C2=
=A0r.rolname AS member,<br>=C2=A0 =C2=A0m.rolname AS role<br>FROM<br>=C2=A0=
 =C2=A0pg_auth_members am<br>JOIN<br>=C2=A0 =C2=A0pg_roles r ON r.oid =3D a=
m.member<br>JOIN<br>=C2=A0 =C2=A0pg_roles m ON m.oid =3D am.roleid<br>WHERE=
<br>=C2=A0 =C2=A0m.rolname =3D &#39;pg_read_all_stats&#39;<br>=C2=A0 =C2=A0=
AND r.rolname =3D &#39;abcd&#39;;<br><br>=C2=A0member | =C2=A0 =C2=A0 =C2=
=A0 role<br>--------+-------------------<br>=C2=A0abcd =C2=A0 | pg_read_all=
_stats<br>(1 row)<br></div><div>```</div><div><br></div><div>I also tried w=
ith PostgreSQL v14.13, and this was not the case, it was working fine as ex=
pected.<br>Then I tried v16.4 and=C2=A0v17beta3, and I faced the &lt;insuff=
icient privilege&gt; issue, so I guess something changed v15 onwards?</div>=
</div>

--000000000000d9487106202392e8--