We have migrated to new Google sites!
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;
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=#