On 6/13/20 7:15 PM, Tom Lane wrote:
sekhar chandra <sekharclouddbengineer@xxxxxxxxx> 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.klaver@xxxxxxxxxxx
