X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id A25D09FA317 for ; Thu, 25 May 2006 14:25:51 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 99596-01 for ; Thu, 25 May 2006 14:25:42 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id 6E8A69F94EA for ; Thu, 25 May 2006 14:25:42 -0300 (ADT) Received: from mailout10.sul.t-online.com (mailout10.sul.t-online.com [194.25.134.21]) by svr4.postgresql.org (Postfix) with ESMTP id A873E5B0890 for ; Thu, 25 May 2006 17:25:41 +0000 (GMT) Received: from fwd32.aul.t-online.de by mailout10.sul.t-online.com with smtp id 1FjJac-000402-06; Thu, 25 May 2006 19:25:38 +0200 Received: from [10.0.0.12] (GvEfzoZA8eBZefuWFgT9VfZckoMef7vaRcE8FCLbNbcl2sLYFYjQ6k@[80.129.110.42]) by fwd32.sul.t-online.de with esmtp id 1FjJab-0QV1Dk0; Thu, 25 May 2006 19:25:37 +0200 Message-ID: <4475E87C.6080509@web.de> Date: Thu, 25 May 2006 19:25:16 +0200 From: bill.mair@t-online.de (Bill Mair) User-Agent: Thunderbird 1.5.0.2 (Windows/20060308) MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: Schema authorization proplems Content-Type: text/plain; charset=ISO-8859-15; format=flowed Content-Transfer-Encoding: 7bit X-ID: GvEfzoZA8eBZefuWFgT9VfZckoMef7vaRcE8FCLbNbcl2sLYFYjQ6k X-TOI-MSGID: b8b92ab6-d1ae-40ad-8d78-53a1d07c47ff X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/423 X-Sequence-Number: 21955 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 -------