PostgreSQL: insufficient privilege

Question

I'm using postgreSQL as a database for an application that I build. When I run the application, I open dbeaver and typed the SQL query:

 select * from pg_stat_activity

in order to see a list of SQL queries that has been processed during the application running.

In "query" column I got "insufficient privilege" for some queries. Do you know what I can do to see the actual SQL query?

Answer 1

You would have to run the query as a root-user to see all the queries. This is usually the postgres user.

On certain managed postgresql services (AWS), access to the root-user is restricted, and it is not possible to see queries executed by that user by design.

In general, the more privileges your user has, the more queries you can see in pg_stat_activity

Answer 2

If you are using Postgresql 10 and above, you can grant pg_read_all_stats role to your user from superuser account (postgres)

GRANT pg_read_all_stats TO username;

Answer 3

You do not necessarily have to run the query as a root-user. Giving someone root user access could have very bad security implications, as they could literally do anything. There is a workaround where you can create a function as root that has access to pg_stat_statements, and then grant the read-only user access to the function. For example:

CREATE OR REPLACE FUNCTION your_schema.get_stat_statements(showtext boolean = true) RETURNS SETOF pg_stat_statements AS
$$
  /* pganalyze-collector */ SELECT * FROM public.pg_stat_statements(showtext) LIMIT 100;
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;

GRANT USAGE on SCHEMA your_schema TO  your_readonly_user;
GRANT EXECUTE on FUNCTION your_schema.get_stat_statements(boolean) TO your_readonly_user;

Thanks to https://pganalyze.com/docs/install/amazon_rds/02_create_monitoring_user You can find an example of a similar approach here.