Pivotal Knowledge Base

Follow

How to query running super user queries from pg_stat_activity with non-super user

Supported Environments

    • HDB (HAWQ) 1.2.1.0
    • PHD 2.1.0.0
    • GPDB 4.x

Restrictions

SECURITY DEFINER are not supported in Pivotal HDB versions prior to 1.2.1.0. You can verify the version of HDB with the following command

gpadmin=# select version();
                                                                                    version
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 8.2.15 (Greenplum Database 4.2.0 build 1) (HAWQ 1.2.1.0 build 10335) on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.4.2 compiled on Aug  8 2014 16:31:48
(1 row)

Purpose

      In some environments there is a mix of super and non-super users that need to execute queries. By default non-super users do not have privligest to query entries in pg_stat_activity for suerp users. This provides some visibility issues to the end uers. For example if the end user wants to see if its ok to run one of their new queries they are not able to see what kind of workloads are currently running by the super users. This article describes a solution to this porblem without granted the end user with super user privleges.

Preparing the environment

      • As a super user create a new user call "jim"
        gpadmin=# CREATE USER jim with NOSUPERUSER;
        NOTICE:  resource queue required -- using default resource queue "pg_default"
        CREATE ROLE
        
      • Update $MASTER_DATA_DIRECTORY/pg_hba.conf so new user can login
        [gpadmin@hdm1 ~]$ vi $MASTER_DATA_DIRECTORY/pg_hba.conf
        host     all         jim         127.0.0.1/28    trust
        host     all         jim         172.28.0.0/16       trust
        
      • Run gpstop with u switch so force master to re-read pg_hba.conf. There is no impact to database when performing this operation
        [gpadmin@hdm1 ~]$ gpstop -u
        20141124:13:17:58:523220 gpstop:hdm1:gpadmin-[INFO]:-Starting gpstop with args: -u
        20141124:13:17:58:523220 gpstop:hdm1:gpadmin-[INFO]:-Gathering information and validating the environment...
        20141124:13:17:58:523220 gpstop:hdm1:gpadmin-[INFO]:-Obtaining Greenplum Master catalog information
        20141124:13:17:58:523220 gpstop:hdm1:gpadmin-[INFO]:-Obtaining Segment details from master...
        20141124:13:17:59:523220 gpstop:hdm1:gpadmin-[INFO]:-Greenplum Version: 'postgres (HAWQ) 4.2.0 build 1'
        20141124:13:17:59:523220 gpstop:hdm1:gpadmin-[INFO]:-Signalling all postmaster processes to reload
        ...
        
      • As super user create a new function call "get_pg_stats() and grant privleges to user "jim"
        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 jim;
        GRANT
        

Testing the get_pg_stats() function

    • login to database on super user "gpadmin" and submit a long running query
      gpadmin=# create table Hung_analyzer_UnitTest_12345 as select generate_series(1,999999); select count(*) from Hung_analyzer_UnitTest_12345 a,Hung_analyzer_UnitTest_12345 b, Hung_analyzer_UnitTest_12345 c;
      
    • Now login form user "jim" and query pg_stats_activity. You will notice "insufficient privilege" is returned
      gpadmin=> select * from pg_stat_activity ;
       datid | datname | procpid | sess_id | usesysid | usename |          current_query           | waiting |          query_start          |         backend_start         | client_addr | client_port | applica
      tion_name |          xact_start
      -------+---------+---------+---------+----------+---------+----------------------------------+---------+-------------------------------+-------------------------------+-------------+-------------+--------
      ----------+-------------------------------
       16554 | gpadmin |  215391 |      19 |       10 | gpadmin | insufficient privilege        |         |                               |                               |             |             | psql
                |
       16554 | gpadmin |  216162 |      22 |    16650 | user1   | select * from pg_stat_activity ; | f       | 2014-11-24 13:05:11.903662-08 | 2014-11-24 13:05:00.804789-08 | 127.0.0.1   |       36757 | psql
                | 2014-11-24 13:05:11.903662-08
      (2 rows)
      
    • Test the get_pg_stats() function and confirm you can see information about gpadmin users query
      gpadmin=# select * from get_pg_stats();
       datid | datname | procpid | sess_id | usesysid | usename |                                                    current_query                                                    | waiting |          query_s
      tart          |         backend_start         | client_addr | client_port | application_name |          xact_start
      -------+---------+---------+---------+----------+---------+---------------------------------------------------------------------------------------------------------------------+---------+-----------------
      --------------+-------------------------------+-------------+-------------+------------------+-------------------------------
       16554 | gpadmin |  215391 |      19 |       10 | gpadmin | select count(*) from Hung_analyzer_UnitTest_12345 a,Hung_analyzer_UnitTest_12345 b, Hung_analyzer_UnitTest_12345 c; | f       | 2014-11-24 13:19
      :58.427656-08 | 2014-11-24 12:52:46.821544-08 |             |          -1 | psql             | 2014-11-24 13:19:58.427656-08
       16554 | gpadmin |  216162 |      22 |       10 | gpadmin | select * from get_pg_stats();                                                                                       | f       | 2014-11-24 13:20
      :47.860888-08 | 2014-11-24 13:20:21.202308-08 |             |          -1 | psql             | 2014-11-24 13:20:47.860888-08
      

Comments

Powered by Zendesk