18

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 ?

1
  • 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. Commented Feb 5, 2014 at 12:31

2 Answers 2

28

For postgres 10+

Use the pg_read_all_stats role or pg_monitor for broader access. Just GRANT it to the user/role you wish to give the required access.

GRANT pg_read_all_stats TO myuser;

For old postgres versions

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
SET search_path = pg_catalog,pg_temp;

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.

3
  • 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
    Commented Jan 22, 2015 at 18:58
  • You should set search_path for security definers to avoid security gaps: postgresql.org/docs/current/…
    – clemens
    Commented Jan 26 at 7:14
  • @clemens Correct, thanks. I did write this 10 years ago... Commented Mar 9 at 6:50
12

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 and acknowledge you have read our privacy policy.

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