Schema authorization proplems

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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 -------


[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux