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

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