Обсуждение: 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_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
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:
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:
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 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
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