Thread: Cannot Create Objects
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:
- Create a DBO role e.g. dbo_xxx NOCREATEDB NOCREATEUSER
- Create a db mydb WITH OWNER db_xxx
- REVOKE all connection rights from public
- GRANT only rights to dbo_xxx
- 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
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
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