Re: postgres issue - Mailing list pgsql-general

On 6/13/20 3:28 PM, Shailesh Rangani wrote:
> \dn+ public
>                                            List of schemas
>              Name             |     Owner     |       Access privileges  
>        |      Description
>
-----------------------------+---------------+--------------------------------+-----------------------------------------
> publicrdsadmindailypay_root=UC/rdsadmin dailypay=U/rdsadmin    standard 
> public schema


In the docx file you showed a CREATE TABLE permissions error for the 
public schema. Having USAGE will not fix that. To allow a user to do 
that they need CREATE(C) privilege on the schema.

For more information on privileges see:
https://www.postgresql.org/docs/12/ddl-priv.html

What database in the cluster are trying to GRANT schema privileges in?

Are you doing the GRANT as the rdsadmin user?

And are you in that database when you run has_schema_privilege()?

What does the below show?

\l+

> 
> 
> 
> 
> 
> 
> On Saturday, June 13, 2020, 05:12:32 PM CDT, Adrian Klaver 
> <adrian.klaver@aklaver.com> wrote:
> 
> 
> On 6/13/20 2:52 PM, Shailesh Rangani wrote:
>  > + Community DL.
>  >
>  >
>  >
>  >
>  >
>  > On Saturday, June 13, 2020, 04:39:01 PM CDT, Shailesh Rangani
>  > <shailesh.rangani@yahoo.com <mailto:shailesh.rangani@yahoo.com>> wrote:
>  >
>  >
>  > Hi Adrian,
>  >
>  > Please find the attached sequence.
> 
> Please post output of command below as text.
> 
> What does \dn+ public show?
> 
>  >
>  > Regards
>  > Shailesh
>  >
>  >
>  >
>  >
>  >
>  > ------ Forwarded message ---------
>  >
>  > From: *Adrian Klaver* <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>
>  > <mailto:adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>>
>  > Date: Sat, Jun 13, 2020 at 10:43 AM
>  > Subject: Re: Fwd: not able to give usage access to public schema
>  > To: sekhar chandra <sekharclouddbengineer@gmail.com 
> <mailto:sekharclouddbengineer@gmail.com>
>  > <mailto:sekharclouddbengineer@gmail.com 
> <mailto:sekharclouddbengineer@gmail.com>>>
>  > Cc: <pgsql-general@postgresql.org 
> <mailto:pgsql-general@postgresql.org> 
> <mailto:pgsql-general@postgresql.org <mailto:pgsql-general@postgresql.org>>>
>  >
>  >
>  >
>  > 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> 
> <mailto:adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>
>  > <mailto:adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> 
> <mailto: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> 
> <mailto:adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>
>  > <mailto:adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> 
> <mailto:adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>>
>  >  >
>  >
>  >
>  > --
>  > Adrian Klaver
>  > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> 
> <mailto:adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>
>  >
>  > //Datavail/Internal - Limited External Distribution
>  >
>  > This email (including any attachments) is for the use of the intended
>  > recipient(s) only and may contain confidential or proprietary
>  > information. If you have received this email in error, please notify the
>  > sender immediately and then delete it. If you are not the intended
>  > recipient, you must not keep, use, disclose, copy or distribute this
>  > email without the author's prior permission. //Datavail/Business -
>  > Limited External Distribution
> 
> 
> 
> -- 
> Adrian Klaver
> adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>


-- 
Adrian Klaver
adrian.klaver@aklaver.com