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 RLE storageuser NSUPERUSER NINHERIT NCREATEDB NCREATERLE;
-- Create the storageadmin group
CREATE RLE storageadmin NSUPERUSER NINHERIT NCREATEDB NCREATERLE;
-- Add the admins to the user group
GRANT storageuser T storageadmin;
-- Create testuser and add to storageuser group
CREATE RLE testuser LGIN NSUPERUSER NINHERIT NCREATEDB NCREATERLE;
GRANT storageuser T testuser;
-- Create testadmin and add to storageadmin group
CREATE RLE testadmin LGIN NSUPERUSER NINHERIT NCREATEDB NCREATERLE;
GRANT storageadmin T testadmin;
-- Allow storageadmin members to create tables etc
GRANT ALL N DATABASE test T storageadmin;
-- Create schema and allow storageadmin to create tables etc in the schema
CREATE SCHEMA storage AUTHRIZATIN postgres;
GRANT ALL N SCHEMA storage T postgres;
GRANT ALL N SCHEMA storage T 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 NT NULL, data bytea
NT NULL);
ERRR: permission denied for schema storage
test=>
snip
(end of broadcast)
TIP 6: explain analyze is your friend