To be able to monitor PostgreSQL queries under non superuser you need to create some objects as superuser and grant select permissions to your monitoring user. Solution based on “How to view the query of another session in pg_stat_activity without being super user?

PostgreSQL code:

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

create or replace view public.view_pg_stat_activity as
select *,
clock_timestamp() - backend_start as backend_runtime,
clock_timestamp() - query_start as query_runtime,
clock_timestamp() - state_change as query_last_state_change_age
from public.get_pg_stat_activity();

grant select on public.view_pg_stat_activity to postgres_monitor;

In Grafana simply create datasource of type PostgreSQL and add table into your dashboard with query like this one:

SELECT pid::text "process", state, query_runtime as "query runtime", wait_event_type, wait_event, query from public.view_pg_stat_activity order by state