List user who have access to schema
Hi All:
I'm looking for query which can list all users who have access to a
particular schema.
The user may be granted role, which is turn may have access to the schema.
If the schema name is sch1,
grant select on table sch1.tab1 to role_ro;
grant ALL on table sch1.tab1 to role_rw;
grant role_ro to user1;
grant role_rw to user2;
I'm looking for report like
Schema | Role | Username
------------------------------------------------------------------------
sch1 role_ro user1, user3
role_rw user2, user4
Thanks in advance,
-SR
Suresh Raja <suresh.rajaabc@gmail.com> writes:
I'm looking for query which can list all users who have access to a
particular schema.
Something involving
SELECT ... FROM pg_user
WHERE has_schema_privilege(usename, 'schema-of-interest', 'usage');
would probably be what you want.
regards, tom lane
yes ... how can i pass variable * to the function has_schema_privilege(*,
'schema-of-interest', 'usage');
Thanks!
On Thu, Aug 2, 2018 at 12:58 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Show quoted text
Suresh Raja <suresh.rajaabc@gmail.com> writes:
I'm looking for query which can list all users who have access to a
particular schema.Something involving
SELECT ... FROM pg_user
WHERE has_schema_privilege(usename, 'schema-of-interest', 'usage');would probably be what you want.
regards, tom lane
On Thursday, August 2, 2018, Suresh Raja <suresh.rajaabc@gmail.com> wrote:
yes ... how can i pass variable * to the function has_schema_privilege(*,
'schema-of-interest', 'usage');
You cannot...you must execute the function once for every user, hence the
original query's from clause.
David J.
On 08/02/2018 11:23 AM, Suresh Raja wrote:
yes ... how can i pass variable * to the function
has_schema_privilege(*, 'schema-of-interest', 'usage');
PREPARE schema_user(varchar, varchar) AS SELECT * FROM pg_user
WHERE has_schema_privilege($1, $2, 'create');
EXECUTE schema_user ('aklaver', 'public');
DEALLOCATE schema_user ;
Thanks!
On Thu, Aug 2, 2018 at 12:58 PM, Tom Lane <tgl@sss.pgh.pa.us
<mailto:tgl@sss.pgh.pa.us>> wrote:Suresh Raja <suresh.rajaabc@gmail.com
<mailto:suresh.rajaabc@gmail.com>> writes:I'm looking for query which can list all users who have access to a
particular schema.Something involving
SELECT ... FROM pg_user
WHERE has_schema_privilege(usename, 'schema-of-interest', 'usage');would probably be what you want.
regards, tom lane
--
Adrian Klaver
adrian.klaver@aklaver.com
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