pg_stat_activity not showing current_query text

Post date: Feb 26, 2015 5:8:26 PM

pg_stat_activity table is most often used table to query the current activity of the database.

sachi=# select * from pg_stat_activity;

| client_addr | client_port | application_name | xact_start

-------+---------+---------+---------+----------+---------+---------------------------------+---------+-------------------------------+----------------------

---------+-------------+-------------+------------------+-------------------------------

16993 | sachi | 16717 | 10207 | 10 | gpadmin | <IDLE> | f | 2015-02-25 16:06:50.401201-05 | 2015-02-25 15:56:30.2

74394-05 | | -1 | psql |

16993 | sachi | 32076 | 10740 | 10 | gpadmin | select * from pg_stat_activity; | f | 2015-02-26 11:54:43.244429-05 | 2015-02-26 10:40:50.1

86253-05 | | -1 | psql | 2015-02-26 11:54:43.244429-05

(2 rows)

when you run the same query from NON-Privileged ie non gpadmin user, you get something like

gpuser=# select * from pg_stat_activity;

| client_addr | client_port | application_name | xact_start

-------+---------+---------+---------+----------+---------+---------------------------------+---------+-------------------------------+----------------------

---------+-------------+-------------+------------------+-------------------------------

16993 | sachi | 16717 | 10207 | 10 | gpadmin | <insuffient priviledge> | f | 2015-02-25 16:06:50.401201-05 | 2015-02-25 15:58:30.2

74394-05 | | -1 | psql |

16993 | sachi | 32076 | 10740 | 10 | gpadmin | <insuffient priviledge> | f | 2015-02-26 11:54:43.244429-05 | 2015-02-26 10:40:50.1

86253-05 | | -1 | psql | 2015-02-26 11:58:43.244429-05

(2 rows)

How to solve this problem?

Here is a simple way you can solve this. Ask your DBA who has gpadmin privilege to create a function like below and grant you the function privilege. Now you can query the function to get the desired result.

gpadmin=#

CREATE or REPLACE FUNCTION get_pg_stats()

returns setof pg_stat_activity

as

'select * from pg_stat_activity;'

LANGUAGE 'sql'

SECURITY DEFINER;

CREATE FUNCTION

gpadmin=# GRANT ALL on FUNCTION get_pg_stats() to gpuser;

GRANT

gpuser=# select * from get_pg_stats();

| client_addr | client_port | application_name | xact_start

-------+---------+---------+---------+----------+---------+---------------------------------+---------+-------------------------------+----------------------

---------+-------------+-------------+------------------+-------------------------------

16993 | sachi | 16717 | 10207 | 10 | gpadmin | <IDLE> | f | 2015-02-25 16:06:50.401201-05 | 2015-02-25 15:56:30.2

74394-05 | | -1 | psql |

16993 | sachi | 2688 | 10742 | 10 | gpadmin | select * from pg_stat_activity; | f | 2015-02-26 12:03:42.975227-05 | 2015-02-26 12:03:37.2

76689-05 | | -1 | psql | 2015-02-26 12:03:42.975227-05

(2 rows)

sachi=#