Re: Fwd: not able to give usage access to public schema

sekhar chandra <sekharclouddbengineer(at)gmail(dot)com> writes:
> Adrian - when I follow the same steps what you did . in my case , the
> result is false.

> grant usage on schema public to role_test ;
> GRANT

> SELECT rolname, has_schema_privilege('role_test', 'public', 'usage')
> from pg_roles where rolname = 'role_test';
> rolname | has_schema_privilege
> -----------+----------------------
> role_test | f

This is verging on impossible to believe. To start with, in a standard
installation rights on the public schema are granted to PUBLIC, so that
any role should *already* have usage privilege as soon as it's created.
Thus:

regression=# create user role_test;
CREATE ROLE
regression=# select has_schema_privilege('role_test', 'public', 'usage');
has_schema_privilege
----------------------
t
(1 row)

Even if you'd revoked that public grant, manually granting should
certainly have worked. So my thoughts are running towards maybe
you have created a nonstandard version of has_schema_privilege()
that doesn't do what you think.

Anyway, I'd suggest removing some variables from the equation by
looking directly at the catalog:

postgres=# table pg_namespace;
oid | nspname | nspowner | nspacl
-------+--------------------+----------+-------------------------------------
...
2200 | public | 10 | {postgres=UC/postgres,=UC/postgres}
...

That's what I get in a default installation. If I manually GRANT, it
changes to

2200 | public | 10 | {postgres=UC/postgres,=UC/postgres,role_test=U/postgres}

What do you see?

regards, tom lane