Обсуждение: Fwd: not able to give usage access to public schema

Поиск
Список
Период
Сортировка

Fwd: not able to give usage access to public schema

От
sekhar chandra
Дата:
I am not able to give usage permission to public schema. below are the steps.


Logged in as super user
created a new user as user1
grant usage on public to user1

command completed successfully , but verification statement showing he doesnt have usage permission.
SELECT rolnamehas_schema_privilege(rolname, 'public', 'usage') from pg_roles;  

this is strage. what could go wrong. 

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

От
Adrian Klaver
Дата:
On 6/12/20 3:56 PM, sekhar chandra wrote:
> I am not able to give usage permission to public schema. below are the 
> steps.
> 
> 
> Logged in as super user
> created a new user as user1
> grant usage on public to user1

Either the above is a cut and paste error or you got an error:

grant usage on public to role_test ;
ERROR:  relation "public" does not exist

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


> 
> command completed successfully , but verification statement showing he 
> doesnt have usage permission.
> SELECT rolname, has_schema_privilege(rolname, 'public', 'usage') from 
> pg_roles;
> 
> this is strage. what could go wrong.


-- 
Adrian Klaver
adrian.klaver@aklaver.com



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

От
sekhar chandra
Дата:
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 

On Fri, Jun 12, 2020 at 10:11 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 6/12/20 3:56 PM, sekhar chandra wrote:
> I am not able to give usage permission to public schema. below are the
> steps.
>
>
> Logged in as super user
> created a new user as user1
> grant usage on public to user1

Either the above is a cut and paste error or you got an error:

grant usage on public to role_test ;
ERROR:  relation "public" does not exist

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


>
> command completed successfully , but verification statement showing he
> doesnt have usage permission.
> SELECT rolname, has_schema_privilege(rolname, 'public', 'usage') from
> pg_roles;
>
> this is strage. what could go wrong.


--
Adrian Klaver
adrian.klaver@aklaver.com

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

От
Adrian Klaver
Дата:
On 6/13/20 10:33 AM, sekhar chandra wrote:
> 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

Postgres version and where are you running this e.g. cloud service?

What does \dn+ public show before and after you redo commands as asked 
below?

Can you start over and provide complete sequence for above including 
CREATE ROLE and what user you are doing the above as?



> 
> On Fri, Jun 12, 2020 at 10:11 PM Adrian Klaver 
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
> 
>     On 6/12/20 3:56 PM, sekhar chandra wrote:
>      > I am not able to give usage permission to public schema. below
>     are the
>      > steps.
>      >
>      >
>      > Logged in as super user
>      > created a new user as user1
>      > grant usage on public to user1
> 
>     Either the above is a cut and paste error or you got an error:
> 
>     grant usage on public to role_test ;
>     ERROR:  relation "public" does not exist
> 
>     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 | t
> 
> 
>      >
>      > command completed successfully , but verification statement
>     showing he
>      > doesnt have usage permission.
>      > SELECT rolname, has_schema_privilege(rolname, 'public', 'usage')
>     from
>      > pg_roles;
>      >
>      > this is strage. what could go wrong.
> 
> 
>     -- 
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com



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

От
Adrian Klaver
Дата:
On 6/13/20 10:33 AM, sekhar chandra wrote:
> 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
> 

Might be a good time to point out :) :

https://www.postgresql.org/docs/12/ddl-schemas.html#DDL-SCHEMAS-PUBLIC

" Note that by default, everyone has CREATE and USAGE privileges on the 
schema public. This allows all users that are able to connect to a given 
database to create objects in its public schema. "


-- 
Adrian Klaver
adrian.klaver@aklaver.com



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

От
Tom Lane
Дата:
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



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

От
Adrian Klaver
Дата:
On 6/13/20 7:15 PM, Tom Lane wrote:
> 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

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@aklaver.com