From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Alex Magnum <magnum11200(at)gmail(dot)com>, Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Cannot Create Objects |
Date: | 2016-02-04 20:25:45 |
Message-ID: | 56B3B3C9.4000904@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 02/04/2016 11:42 AM, Alex Magnum wrote:
> Hi,
> I am having a few problems with access permissions.
>
> When I create a new role with NOCREATEUSER and then create a database
> for that role I can connect to the DB but when trying to create a db
> object I will get the ERROR: permission denied for schema public.
>
> Strangely though, if the role is created with CREATEUSERS I don't have
> any problems.
>
> Here is what I want to do:
>
> 1. Create a DBO role e.g. dbo_xxx NOCREATEDB NOCREATEUSER
> 2. Create a db mydb WITH OWNER db_xxx
> 3. REVOKE all connection rights from public
> 4. GRANT only rights to dbo_xxx
> 5. GRANT all create rights on mydb TO dbo_xxx ; allowing the user to
> load the db schema
>
> This is what I tried
> REVOKE ALL ON SCHEMA public FROM PUBLIC;
> CREATE USER dbo_xxx WITH PASSWORD 'mypass' NOCREATEDB NOCREATEUSER;
Just realized I should have asked where the above took place?
>
> CREATE DATABASE my_db WITH OWNER dbo_xxx ENCODING 'UTF8';
> REVOKE CONNECT ON DATABASE my_db FROM PUBLIC;
> GRANT CONNECT ON DATABASE my_db TO dbo_xxx;
> GRANT ALL PRIVILEGES ON DATABASE my_db TO dbo_xxx;
> -- After schema is loaded
> CREATE USER read_only WITH PASSWORD 'mypass' NOCREATEDB NOCREATEUSER;
> REVOKE ALL ON ALL TABLES IN SCHEMA public FROM PUBLIC ;
> GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only ;
>
> But i end up with permission denied errors.
>
> Anyone having a suggestion how to get this to work? Did I mess up
> permissions in public schema?
>
> Any help and suggestion is greatly appreciated.
>
> Alex
>
>
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | drum.lucas@gmail.com | 2016-02-04 21:35:12 | Re: Fatal error when not numeric value - PostgreSQL 9.2 |
Previous Message | Adrian Klaver | 2016-02-04 20:18:41 | Re: Cannot Create Objects |