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
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', 'p
On Thursday, August 2, 2018, Suresh Raja 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.
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 wrote:
> Suresh Raja writes:
> > I'm looking for query which can list all users who have access to a
> > particular schema.
>
> Somethin
Suresh Raja 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
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_r
