Re: Fwd: not able to give usage access to public schema - Mailing list pgsql-general

sekhar chandra <sekharclouddbengineer@gmail.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