Pivotal Knowledge Base

Follow

Script - User information

Goal

Is to provide a simple scripts to help administrator identify User information in the database.

Note : Please verify the script on a test cluster , before running it on production .

Solution

User information in the database.

select
    a.rolname as "User name",
    a.oid as "User Oid",
    a.rolsuper as "Super User",
    b.rsqname as "Resource queue",
    a.rolcanlogin as "Can Login",
    a.rolconnlimit as "Connection limit"
from
    pg_roles a,
    pg_resqueue b
where
    a.rolresqueue=b.oid;

List of operation on the users ( like when it was created , altered etc ).

select 
     a.rolname as User, 
     staactionname as Create_alter, 
     stausename as Created_by, 
     stasubtype as Action, 
     statime as When 
from 
     pg_stat_last_shoperation p, 
     pg_authid a  
where 
     p.objid=a.oid 
     and 
     classid=1260 
order by 1 ;

When was the last time the user attempted to login and what was the query run.

Note: The below query provides you a valid data based on the availability of the master logs on $MASTER_DATA_DIRECTORY/pg_log directory.

select 
    logtime as Lastqueryrantime,
    logsessiontime as Sessloggedintodbtime,
    loguser as User,
    logdatabase as Db,
    loghost as Fromhost,
    logseverity as Serverity,
    logmessage as Queryran
from
    gp_toolkit.gp_log_database
where
    loguser='<username>'
order by 1 desc
limit 1;

The user privileges

select usename as "User name",
       nspname || '.' || relname as "Object Name",
       case relkind when 'r' then 'TABLE'
                    when 'v' then 'VIEW'
       end as "Object Type",
       priv as "privilege"
from pg_class
join pg_namespace on pg_namespace.oid = pg_class.relnamespace,
     pg_user,
     (values('SELECT', 1),('INSERT', 2),('UPDATE', 3),('DELETE', 4)) privs(priv, privorder)
where relkind in ('r', 'v')
      and has_table_privilege(pg_user.usesysid, pg_class.oid, priv)
      and not (nspname ~ '^pg_' or nspname = 'information_schema')
order by 2, 1, 3, privorder;

Comments

Powered by Zendesk