From: bill.mair@t-online.de
To: pgsql-admin@postgresql.org
Subject: Schema authorization proplems
Date: Thu, 25 May 2006 19:25:16 +0200
Message-ID: <4475E87C.6080509@web.de> (raw)
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 -------
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: bill.mair@t-online.de
Subject: Re: Schema authorization proplems
In-Reply-To: <4475E87C.6080509@web.de>
References: <4475E87C.6080509@web.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox