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
