Postgres Pro
Downloads
Home   >   mailing lists

Thread: List user who have access to schema

List user who have access to schema

From
Suresh Raja
Date:
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



Re: List user who have access to schema

From
Tom Lane
Date:
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

Re: List user who have access to schema

From
Tom Lane
Date:
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

Re: List user who have access to schema

From
Suresh Raja
Date:
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

Re: List user who have access to schema

From
Suresh Raja
Date:
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

Re: List user who have access to schema

From
"David G. Johnston"
Date:
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.

Re: List user who have access to schema

From
"David G. Johnston"
Date:
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.

Re: List user who have access to schema

From
Adrian Klaver
Date:
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

Re: List user who have access to schema

From
Adrian Klaver
Date:
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

Re: List user who have access to schema

From
pinker
Date:
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