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 ?
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;
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.
Starting with PostgreSQL 10 you can grant the role pg_read_all_stats in order to achieve the desired result.
