SQL to check user details and its activities
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=#
3. User last LOGIN details
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
....