pg_stat_activity table is most often used table to query the current activity of the database. sachi=# select * from pg_stat_activity; datid | datname | procpid | sess_id | usesysid | usename | current_query | waiting | query_start | backend_start | 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; datid | datname | procpid | sess_id | usesysid | usename | current_query | waiting | query_start | backend_start | 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(); datid | datname | procpid | sess_id | usesysid | usename | current_query | waiting | query_start | backend_start | 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=# |
|
|