Create user to monitor database activity on Postgres
At this time, only superusers can see others' activity details in pg_stat_activity.

PostgreSQL could use a finer-grained rights model, where you can GRANT the MONITOR_QUERIES right to a user, for example. But right now it doesn't have one, and quite a few things are superuser-only. This is one of them.