Note: IF you want to add another query here, send your query with output to gpdba@gpdba.com. We will post it here with your name and email id.
1. List user information sachi=# select sachi-# a.rolname as "User name",sachi-# a.oid as "User Oid", sachi-# a.rolsuper as "Super User", sachi-# b.rsqname as "Resource queue", sachi-# a.rolcanlogin as "Can Login", sachi-# a.rolconnlimit as "Connection limit", sachi-# b.rsqcountlimit, sachi-# b.rsqcostlimit, sachi-# b.rsqovercommit, sachi-# b.rsqignorecostlimit sachi-# from sachi-# pg_roles a, sachi-# pg_resqueue b sachi-# where sachi-# a.rolresqueue=b.oid; User name | User Oid | Super User | Resource queue | Can Login | Connection limit | rsqcountlimit | rsqcostlimit | rsqovercommit | rsqignorecostlimit --------------+----------+------------+----------------+-----------+------------------+---------------+--------------+---------------+-------------------- admin | 88044 | f | pg_default | f | -1 | 20 | -1 | f | 0 gpadmin | 10 | t | pg_default | t | -1 | 20 | -1 | f | 0 gpmon | 96279 | t | pg_default | t | -1 | 20 | -1 | f | 0 sachi | 16994 | f | pg_default | t | -1 | 20 | -1 | f | 0 gpuser | 33477 | f | pg_default | t | -1 | 20 | -1 | f | 0 e2e_bdasvc | 180262 | f | e2erq | t | 2 | 2 | -1 | f | 0 (6 rows) 2. ACTIVITIES ON USERS sachi=# select sachi-# a.rolname as User, sachi-# staactionname as Create_alter, sachi-# stausename as Created_by, sachi-# stasubtype as Action, sachi-# statime as When sachi-# from sachi-# pg_stat_last_shoperation p, sachi-# pg_authid a sachi-# where sachi-# p.objid=a.oid sachi-# and sachi-# classid=1260 sachi-# order by 1 ; user | create_alter | created_by | action | when --------------+--------------+------------+------------------+------------------------------- admin | CREATE | gpadmin | ROLE | 2014-10-17 22:23:34.663169-04 admin | PRIVILEGE | gpadmin | GRANT | 2014-10-17 22:23:50.37452-04 e2e_bdasvc | CREATE | gpadmin | ROLE | 2015-02-25 15:56:40.121114-05 e2e_bdasvc | ALTER | gpadmin | CONNECTION LIMIT | 2015-02-25 16:06:50.401481-05 gpadmin | ALTER | gpadmin | PASSWORD | 2013-11-27 12:54:24.258383-05 gpmon | CREATE | gpadmin | ROLE | 2014-10-25 10:09:36.054814-04 gpuser | CREATE | gpadmin | ROLE | 2014-03-30 19:52:27.89646-04 sachi | ALTER | gpadmin | LOGIN | 2013-11-27 14:20:29.535817-05 sachi | CREATE | gpadmin | ROLE | 2013-11-27 13:00:00.963128-05 (9 rows)sachi=# sachi=# select sachi-# logtime as Lastqueryrantime, sachi-# logsessiontime as Sessloggedintodbtime, sachi-# loguser as User, sachi-# logdatabase as Db, sachi-# loghost as Fromhost, sachi-# logseverity as Serverity, sachi-# logmessage as Queryran sachi-# from sachi-# gp_toolkit.gp_log_database sachi-# where sachi-# loguser='sachi' sachi-# order by 1 desc sachi-# limit 1; lastqueryrantime | sessloggedintodbtime | user | db | fromhost | serverity | queryran -------------------------------+------------------------+-------+-------+----------+-----------+------------------------------------------------------------- --------------------- 2014-11-04 16:50:52.805827-05 | 2014-11-04 16:50:52-05 | sachi | sachi | [local] | FATAL | no pg_hba.conf entry for host "[local]", user "sachi", datab ase "sachi", SSL off (1 row) sachi=# 4. USER PRIVILEGES sachi=# select usename as "User name", sachi-# nspname || '.' || relname as "Object Name", sachi-# case relkind when 'r' then 'TABLE' sachi-# when 'v' then 'VIEW' sachi-# end as "Object Type", sachi-# priv as "privilege" sachi-# from pg_class sachi-# join pg_namespace on pg_namespace.oid = pg_class.relnamespace, sachi-# pg_user, sachi-# (values('SELECT', 1),('INSERT', 2),('UPDATE', 3),('DELETE', 4)) privs(priv, privorder) sachi-# where relkind in ('r', 'v') sachi-# and has_table_privilege(pg_user.usesysid, pg_class.oid, priv) sachi-# and not (nspname ~ '^pg_' or nspname = 'information_schema') sachi-# order by 2, 1, 3, privorder; User name | Object Name | Object Type | privilege --------------+---------------------------------------------------------------------------+-------------+----------- gpadmin | dbadmin.smart_analyze_config | TABLE | SELECT gpadmin | dbadmin.smart_analyze_config | TABLE | INSERT gpadmin | dbadmin.smart_analyze_config | TABLE | UPDATE gpadmin | dbadmin.smart_analyze_config | TABLE | DELETE gpmon | dbadmin.smart_analyze_config | TABLE | SELECT gpmon | dbadmin.smart_analyze_config | TABLE | INSERT gpmon | dbadmin.smart_analyze_config | TABLE | UPDATE gpmon | dbadmin.smart_analyze_config | TABLE | DELETE gpadmin | dbadmin.v_dist_key | VIEW | SELECT gpadmin | dbadmin.v_dist_key | VIEW | INSERT gpadmin | dbadmin.v_dist_key | VIEW | UPDATE gpadmin | dbadmin.v_dist_key | VIEW | DELETE gpmon | dbadmin.v_dist_key | VIEW | SELECT gpmon | dbadmin.v_dist_key | VIEW | INSERT gpmon | dbadmin.v_dist_key | VIEW | UPDATE gpmon | dbadmin.v_dist_key | VIEW | DELETE gpadmin | dbadmin.v_distribution_key | VIEW | SELECT gpadmin | dbadmin.v_distribution_key | VIEW | INSERT gpadmin | dbadmin.v_distribution_key | VIEW | UPDATE gpadmin | dbadmin.v_distribution_key | VIEW | DELETE .... |
