Finding relationship between username, schemaname, owner, objectname and acl on that object

posted Nov 1, 2014, 8:06 AM by Sachchida Ojha
SELECT use.usename as username, nsp.nspname as schemaname, c.relname as objectname, 
c.relkind as type, use2.usename as owner, c.relacl 
FROM pg_user use cross join pg_class c left join pg_namespace nsp on (c.relnamespace = nsp.oid) left join pg_user use2 on (c.relowner = use2.usesysid) 
WHERE c.relowner = use.usesysid and nsp.nspname NOT IN ('pg_catalog', 'pg_toast', 'information_schema') 
ORDER BY 1,2,3
Comments