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?
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;
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
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.
One possible workaround to this is defining a MATERIALIZED VIEW using a user that has enough privileges to view the data, and granting access to the view to the use that does not have the privileges.
One downside to this is that the view will have to be refreshed periodically using REFRESH MATERIALIZED VIEW, also by the owner of the view (or some other user with privileges).
