Re: List user who have access to schema

[pinker <pinker@xxxxxxx>]

I think this one will give you report you need:

select schema_name, 
roleid::regrole, 
string_agg(member::regrole::text,',' order by member::regrole::text) users
from information_schema.schemata s, pg_user u
JOIN pg_auth_members a ON u.usename::text=a.roleid::regrole::text
WHERE s.schema_name not like 'pg_%' AND has_schema_privilege(usename,
s.schema_name, 'usage')
GROUP BY s.schema_name, roleid::regrole, u.usename
order by 1;



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html