CODEWITHSUNDEEP
postgresql
Beto
Beto

Reputation: 579

Permission in PostgreSQL

I want to run a query on a PostgreSQL database. The query is as follows:

select * from pg_stat_activity;

But I want this to be done by a user who only has read permission. The user should not be allowed to do select in the tables because it is only to see who is connected to the database, not being able to see tables such as Passwords for example.

I gave permission only to the user to see the tables of the system catalog.

But when a different user connects to the database, his IP address does not appear, and in the query column it says he is not allowed to see what query he has done.

How can I give permission for this data to be shown without giving the user permission to access common tables?

Upvotes: 0

Views: 317

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247940

If you have PostgreSQL v10 or better, you can grant the role pg_read_all_stats to the user. This enables to see all statistics data, in particular all entries in pg_stat_activity.

For versions older than v10, you could create a SECURITY DEFINER function that belongs to a superuser and runs the query. You should observe what the documentation has to say about SECURITY DEFINER functions.

Upvotes: 1

Related Questions