Reputation: 579
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
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