Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TvcV1-0004Na-Rq for pgsql-hackers@arkaria.postgresql.org; Wed, 16 Jan 2013 23:34:28 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1TvcV0-0005dm-Ps for pgsql-hackers@arkaria.postgresql.org; Wed, 16 Jan 2013 23:34:26 +0000 Received: from makus.postgresql.org ([98.129.198.125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TvcOE-0008CY-Q1; Wed, 16 Jan 2013 23:27:27 +0000 Received: from mail-wi0-f181.google.com ([209.85.212.181]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TvcOC-0004qe-UK; Wed, 16 Jan 2013 23:27:26 +0000 Received: by mail-wi0-f181.google.com with SMTP id hq4so1791958wib.2 for ; Wed, 16 Jan 2013 15:27:23 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:x-received:in-reply-to:references:date:message-id :subject:from:to:cc:content-type; bh=PmE1RwtAIJnRVfzrTvvug4az2yQYsZO3A2sVRURb5qk=; b=XSl/7VH1BOBEJwDva5/ZvCOqV+8hQ1q82jFZ1GlpBPiFzsGuAju/k8vX8EzLEI3JC5 VdJ+3MIZ9yBPTrs5waCuYVX8PyIWphhBsrdbpnQfErwsdKU3NTsU2tY6qc3QTnW7yPmT kJNnOZeqkPIWr1Ge0BiGp9cQN4c4IFjoVhggxVZuxsLWTMcGvThpeGyh/vM2xrZ3J7f4 WhAOydO+4eJFBVbwyv1TrZecUcCXu1/cZm9NRWUXZ5ktgnKFKWbvG7rYgBZqkVRhloU2 cViuMCjHZez+4ZvFruZY9hYz1N2PH7OZXPZvmalpvsYZtoOfNABfsNCs6u62E65apR3g 026g== MIME-Version: 1.0 X-Received: by 10.194.20.231 with SMTP id q7mr5052915wje.44.1358378843555; Wed, 16 Jan 2013 15:27:23 -0800 (PST) Received: by 10.227.13.66 with HTTP; Wed, 16 Jan 2013 15:27:23 -0800 (PST) In-Reply-To: <25791.1358189619@sss.pgh.pa.us> References: <11650.1357782995@sss.pgh.pa.us> <25791.1358189619@sss.pgh.pa.us> Date: Thu, 17 Jan 2013 08:27:23 +0900 Message-ID: Subject: Re: Re: Privileges for INFORMATION_SCHEMA.SCHEMATA (was Re: [DOCS] Small clarification in "34.41. schemata") From: Ian Lawrence Barwick To: Tom Lane Cc: Casey Allen Shobe , pgsql-docs@postgresql.org, pgsql-hackers , Peter Eisentraut Content-Type: text/plain; charset=UTF-8 X-Pg-Spam-Score: -2.6 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-hackers Precedence: bulk Sender: pgsql-hackers-owner@postgresql.org 2013/1/15 Tom Lane : > Casey Allen Shobe writes: >> On Wed, Jan 9, 2013 at 8:56 PM, Tom Lane wrote: >>> However, it seems to me that this behavior is actually wrong for our >>> purposes, as it represents a too-literal reading of the spec. The SQL >>> standard has no concept of privileges on schemas, only ownership. >>> We do have privileges on schemas, so it seems to me that the consistent >>> thing would be for this view to show any schema that you either own or >>> have some privilege on. > >> IMHO, schemata should follow the standard as it does today. Other >> platforms have privileges on schemas as well, and this sort of thing seems >> to fall into the same bucket as other platform compatibilities outside the >> scope of what the standard thinks about, which means you use pg_catalog to >> access that information rather than information_schema, which should be >> expected to work consistently on all platforms that implement it. > > Meh. To me, standards compliance requires that if you have created a > SQL-compliant database, you'd better see spec-compliant output from the > information schema. As soon as you do something outside the standard > (in this instance, grant some privileges on a schema), it becomes a > judgment call whether and how that should affect what you see in the > information schema. > > It may be that the current behavior of this view is actually the best > thing, but a standards-compliance argument doesn't do anything to > convince me. > > regards, tom lane My original assumption here was that the documentation [1] was in need of clarification. On the other hand the current output of information_schema.schemata isn't quite I was expecting, which would be as Tom writes: > the consistent thing would be for this view to show any schema that you > either own or have some privilege on. As it stands, the only way of extracting a list of visible schemas from PostgreSQL's information_schema (i.e. without relying on PostgreSQL-specific system functions) is doing something like this: SELECT DISTINCT(table_schema) FROM information_schema.tables Digging about a bit [2], it seems the only other RDBMSes with a fully-fledged information_schema are Microsoft SQL Server and MySQL. I don't have access to SQL Server; the documentation [3] says "Returns one row for each schema in the current database", which also strikes me as incorrect (can someone confirm this behaviour?). For MySQL, the documentation [4] indicates that their implementation shows all schemas (in MySQL: databases) visible to the current user, and I've confirmed this behaviour with MySQL 5.5. Personally I'd support modifying PostgreSQL's information_schema.schemata to show all schemas the current user owns/has privileges on, providing it's not an egregious violation of the SQL standard. It seems I'm not the only user who has been stymied by this issue [5][6][7]; also, resolving it would also make it consistent with MySQL's output [8] [1] http://www.postgresql.org/docs/9.2/static/infoschema-schemata.html [2] http://en.wikipedia.org/wiki/Information_schema [3] http://msdn.microsoft.com/en-us/library/ms182642.aspx [4] http://dev.mysql.com/doc/refman/5.5/en/schemata-table.html [5] http://www.postgresql.org/message-id/CAFjNrYv4MrkbXi-usroCqNiaSyEAzvJ7GjtsEJW2RK7-R=8hiw@mail.gmail.com [6] http://www.postgresql.org/message-id/200612211146.kBLBkLqA001218@wwwmaster.postgresql.org [7] http://www.postgresql.org/message-id/50AFF3FE.4030502@gmail.com [8] Not that I'm claiming MySQL's implementation is authoritative or anything Regards Ian Lawrence Barwick -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers