Skip site navigation (1) Skip section navigation (2)

Re: Re: Privileges for INFORMATION_SCHEMA.SCHEMATA (was Re: [DOCS] Small clarification in "34.41. schemata")

From: Ian Lawrence Barwick <barwick(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Casey Allen Shobe <casey(at)shobe(dot)info>, pgsql-docs(at)postgresql(dot)org, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>, Peter Eisentraut <peter_e(at)gmx(dot)net>
Subject: Re: Re: Privileges for INFORMATION_SCHEMA.SCHEMATA (was Re: [DOCS] Small clarification in "34.41. schemata")
Date: 2013-01-16 23:27:23
Message-ID: CAB8KJ=j7LTykWUr1OGtKxgsja9ixgyiXSbnR_k=spLtGzQid1g@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-docspgsql-hackers
2013/1/15 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Casey Allen Shobe <casey(at)shobe(dot)info> writes:
>> On Wed, Jan 9, 2013 at 8:56 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> 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


In response to

pgsql-docs by date

Next:From: Magnus HaganderDate: 2013-01-17 13:54:29
Subject: Re: streaming replication confusion
Previous:From: Tom LaneDate: 2013-01-14 18:53:39
Subject: Re: [HACKERS] Re: Privileges for INFORMATION_SCHEMA.SCHEMATA (was Re: Small clarification in "34.41. schemata")

pgsql-hackers by date

Next:From: Stephen FrostDate: 2013-01-16 23:31:44
Subject: Re: [PATCH] COPY .. COMPRESSED
Previous:From: Bruce MomjianDate: 2013-01-16 23:25:49
Subject: Re: [PATCH] COPY .. COMPRESSED

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group