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

On 6/13/20 7:15 PM, Tom Lane wrote:
> 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

I'm wrestling with the same thing. What also is in the file that Sekhar
sent was the fact this is happening in an AWS Aurora instance. I've gone
over the Aurora docs and can't see anything that says the behavior is
different. Still it is another data point.

> 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
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com