Обсуждение: postgres issue

On 9/01/2010 10:53 PM, Amy Smith wrote:
> all
> For dba, do you create a dba user and grant all privilege to 'dba user',
> so you can create table, etc ?
> For a team, do you create user for all team member and tie to a group ?
> Not sure the set up for postgres

However you prefer, really.

I prefer to create a role that owns a particular database and GRANT
people who administrate that database access to that role, but you can
just as easily do DBA work as a superuser if you trust your DBAs to
access _all_ your databases.

> also what is best way to create a production and test region  using the
> same database ?

If possible, don't.

Use separate databases in the same PostgreSQL instance or even a
different instance.

> How to use mount for differnt region.

I do not understand this question. Can you explain what you mean a bit more?

--
Craig Ringer

Craig Ringer wrote:
> On 9/01/2010 10:53 PM, Amy Smith wrote:

<snip />

>> How to use mount for differnt region.
>
> I do not understand this question. Can you explain what you mean a bit
> more?

Probably wants tablespaces?


\\||/
Rod
--

On 6/13/20 2:55 PM, Shailesh Rangani wrote:
> 
> 
> + Community DL.
> 
> 
> 
> 
> 
> On Saturday, June 13, 2020, 04:39:01 PM CDT, Shailesh Rangani 
> <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
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

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