X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 377CE11F6637 for ; Mon, 3 Apr 2006 18:28:51 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 18528-06 for ; Mon, 3 Apr 2006 18:28:48 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from hyperion.stayhosted.com (hyperion.stayhosted.com [70.84.193.98]) by postgresql.org (Postfix) with ESMTP id 5C76011F6C35 for ; Mon, 3 Apr 2006 18:28:46 -0300 (ADT) Received: from host86-143-156-152.range86-143.btcentralplus.com ([86.143.156.152]) by hyperion.stayhosted.com with esmtpa (Exim 4.52) id 1FQWbS-00067f-Op for pgsql-admin@postgresql.org; Mon, 03 Apr 2006 21:28:52 +0000 Message-ID: <4431937F.808@gmail.com> Date: Mon, 03 Apr 2006 22:28:31 +0100 From: Milen Dzhumerov User-Agent: Thunderbird 1.5 (Windows/20051201) MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: Permissions Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - hyperion.stayhosted.com X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [0 0] / [47 12] X-AntiAbuse: Sender Address Domain - gmail.com X-Source: X-Source-Args: X-Source-Dir: X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200604/34 X-Sequence-Number: 21203 Hello all, I've been trying to figure a few things lately but I could not so I'm asking on the list. I need to programmatically (through JDBC): 1. Figure out if the currently logged in user can create a schema 2. Figure out if a role has USAGE privileges on a schema 3. Figure out if a role has CREATE privileges on a schema 4. Figure out if a user can create a table in a schema I've been reading the online docs but I could nowhere see any examples of finding out that information through the information_schema schema. I was advised not to poke around with the pg_* schemas since they can change frequently. What I need to do is basically ensure that a user can create a schema, then create some tables in that schema and ensure that other users can access these later. Is is possible that a user can be given for example SELECT privileges to a table in a schema but he/she would not be able to access the table because they don't have USAGE privileges on the schema which the table belongs to? I have found out that I can retrieve all the necessary permissions for tables from the information_schema.table_privileges table with no problems but I'm puzzled about database & schema privileges. Kind regards, gamehack