From: | pinker <pinker(at)onet(dot)eu> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: List user who have access to schema |
Date: | 2018-08-03 14:26:25 |
Message-ID: | 1533306385561-0.post@n3.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2018-08-03 14:52:04 | Re: List of objects owned by a schema/owner |
Previous Message | Tiffany Thang | 2018-08-03 12:33:48 | List of objects owned by a schema/owner |
From | Date | Subject | |
---|---|---|---|
Next Message | Huan Ruan | 2018-08-06 03:24:08 | read explain (analyse, buffers) output in Postgres 10.4 with parallel on |
Previous Message | Adrian Klaver | 2018-08-02 18:47:25 | Re: List user who have access to schema |