Users, Group roles, Group role user mapping, priv granted to a role etc

posted Oct 30, 2014, 9:59 AM by Sachchida Ojha   [ updated Nov 3, 2014, 7:33 PM ]

--List databases

select datname,datacl from pg_database;

 

The gadget spec URL could not be found

--List Schemas

select nspname from pg_catalog.pg_namespace;

 

-- List User Roles

select *  from pg_user;

 

 -- List Group Roles

select * from pg_group;

 

The gadget spec URL could not be found

-- Group role, user mapping

select b.groname,a.usename from pg_user a, (select

groname,grosysid,

unnest(grolist) as useid

from pg_group pgg

--where groname='readonly'

) as b

where  a.usesysid=b.useid

order by 1,2;

 

-- Priv granted to group role

select grantee as rolename,privilege_type as priv, table_schema as schemaname,table_name as tabname, grantor as tableowner

from information_schema.role_table_grants where grantee= ' readonly ';

The gadget spec URL could not be found
sachi=# select schemaname,viewname from pg_views where viewname like '%priv%'; schemaname | viewname --------------------+---------------------- information_schema | table_privileges information_schema | usage_privileges information_schema | data_type_privileges information_schema | column_privileges information_schema | routine_privileges (5 rows) sachi=#

sachi=# select schemaname,viewname from pg_views where viewname like '%rol%';
     schemaname     |             viewname              
--------------------+-----------------------------------
 gp_toolkit         | gp_resq_role
 pg_catalog         | pg_roles
 information_schema | applicable_roles
 information_schema | administrable_role_authorizations
 information_schema | enabled_roles
 information_schema | role_column_grants
 information_schema | role_routine_grants
 information_schema | role_table_grants
 information_schema | role_usage_grants
 gp_toolkit         | gp_roles_assigned
(10 rows)


UNIX Way

$pg_dump -s -c sachi |egrep "${SCHEMAS}\..*OWNER TO" >>privilagesddl.sql

Comments