public inbox for [email protected]
help / color / mirror / Atom feedinsufficient privilege with pg_read_all_stats granted
2+ messages / 2 participants
[nested] [flat]
* insufficient privilege with pg_read_all_stats granted
@ 2024-08-20 20:50 Costa Alexoglou <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Costa Alexoglou @ 2024-08-20 20:50 UTC (permalink / raw)
To: [email protected]
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?
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: insufficient privilege with pg_read_all_stats granted
@ 2024-08-21 07:50 Pavel Luzanov <[email protected]>
parent: Costa Alexoglou <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Pavel Luzanov @ 2024-08-21 07:50 UTC (permalink / raw)
To: Costa Alexoglou <[email protected]>; [email protected]
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-08-21 07:50 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-08-20 20:50 insufficient privilege with pg_read_all_stats granted Costa Alexoglou <[email protected]>
2024-08-21 07:50 ` Pavel Luzanov <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox