Schema authorization proplems
От | bill.mair@t-online.de (Bill Mair) |
---|---|
Тема | Schema authorization proplems |
Дата | |
Msg-id | 4475E87C.6080509@web.de обсуждение исходный текст |
Список | pgsql-admin |
I'm trying to use schema and roles but I'm not having much success (using PostgreSQL Database Server 8.1.2). I want to set up a test area in the database using the schema "storage" in the database "test". There are 2 groups "storageadmin" and "storageuser". "storageadmin" members should be able to create/delete/etc. tables in the schema space and grant access to those tables. "storageuser" members should be able to use those tables. User "testadmin" is a member of the "storageadmin" group. User "testuser" is a member of the "storageuser" group. I think I'm doing it the right way but the problem is that "testadmin" can't even create a table in the schema, any ideas why ? Here is how I set it up: ------- snip ------- -- Create the storageuser group CREATE ROLE storageuser NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE; -- Create the storageadmin group CREATE ROLE storageadmin NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE; -- Add the admins to the user group GRANT storageuser TO storageadmin; -- Create testuser and add to storageuser group CREATE ROLE testuser LOGIN NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE; GRANT storageuser TO testuser; -- Create testadmin and add to storageadmin group CREATE ROLE testadmin LOGIN NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE; GRANT storageadmin TO testadmin; -- Allow storageadmin members to create tables etc GRANT ALL ON DATABASE test TO storageadmin; -- Create schema and allow storageadmin to create tables etc in the schema CREATE SCHEMA storage AUTHORIZATION postgres; GRANT ALL ON SCHEMA storage TO postgres; GRANT ALL ON SCHEMA storage TO storageadmin; ------- snip ------- Now when I logon as testadmin and try to create a table this is what I get: test=> CREATE TABLE storage.directory (key VARCHAR NOT NULL, data bytea NOT NULL); ERROR: permission denied for schema storage test=> ------- snip -------
В списке pgsql-admin по дате отправления: