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.
Probably something has changed, but I couldn't quickly find what exactly.
--
Pavel Luzanov
Postgres Professional: https://postgrespro.com
