12

I have a Postgresql 9.2 database. Two users are created on this database. When I perform the following query as superuser, I can see everything.

select * from pg_stat_activity

However, is it possible to acheive the same result without being connected as superuser ?

Which privilege/role should I grant/create to have the results that a superuser can see ?

  • At this point, there's no right to grant, it's hardcoded to superuser. That's been discussed on the mailing list lately, and may change in 9.5 if someone finds the time to work on it. – Craig Ringer Feb 5 '14 at 12:31
19

At this point, there's no right to grant, it's hardcoded to superuser. That's been discussed on the mailing list lately, and may change in 9.5 if someone finds the time to work on it.

As a workaround, you can create a SECURITY DEFINER function that is owned by the superuser, and runs the query you want. This will allow non-superusers to see the contents of pg_stat_activity by calling the function.

E.g., run as a superuser:

CREATE FUNCTION get_sa() RETURNS SETOF pg_stat_activity AS
$$ SELECT * FROM pg_catalog.pg_stat_activity; $$
LANGUAGE sql
VOLATILE
SECURITY DEFINER;

CREATE VIEW pg_stat_activity_allusers AS SELECT * FROM get_sa();

GRANT SELECT ON pg_stat_activity_allusers TO public;

Note that free access to pg_stat_activity is restricted for a reason. It's possible to snoop sensitive information from other people's queries - imagine for example if another user was using pgcrypto. Rather than granting rights to public you should grant them only to a specific user or role that is to act as a surrogate user for monitoring.

  • Thanks! Was just trying to figure out how to give a monitor account permission to read from pg_stat_activity without granting SUPERUSER. – epic_fil Jan 22 '15 at 18:58
2

Starting with PostgreSQL 10 you can grant the role pg_read_all_stats in order to achieve the desired result.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.