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 .
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.
logtime as Lastqueryrantime,
logsessiontime as Sessloggedintodbtime,
loguser as User,
logdatabase as Db,
loghost as Fromhost,
logseverity as Serverity,
logmessage as Queryran
order by 1 desc
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;