Cannot Create Objects

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.

So what arguments do you give to createuser and what does it show when
you add -e to the command?

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

Who are doing the below as?

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;

Well the above only GRANTs on the database not objects within it. For
more information see:

http://www.postgresql.org/docs/9.4/interactive/sql-grant.html

For databases that means CREATE and CONNECT

Since you already REVOKed ALL on schema public FROM PUBLIC and did not
GRANT SCHEMA privileges to dbo_xxx on schema public, I am pretty sure
that is where your problem is. To get a clearer idea of what is going on
can you show:

\l my_db

and in my_db

\dn+ public

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general