How to view the query of another session in pg_stat_activity without being super user?

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.

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