Cannot Create Objects

From: Alex Magnum <magnum11200(at)gmail(dot)com>
To: Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: Cannot Create Objects
Date: 2016-02-04 19:42:49
Message-ID: CA+cR4zcrdxFGZyWPGxk4p2_ZwC+GAQujFptzn9dT1gqAkkWFtQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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;

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David G. Johnston 2016-02-04 19:45:48 Re: Recursive CTE in function problem
Previous Message Doug Kyle 2016-02-04 19:37:00 Recursive CTE in function problem