public inbox for [email protected]
help / color / mirror / Atom feedFrom: Ian Lawrence Barwick <[email protected]>
To: Tom Lane <[email protected]>
Cc: Casey Allen Shobe <[email protected]>
Cc: [email protected]
Cc: pgsql-hackers <[email protected]>
Cc: Peter Eisentraut <[email protected]>
Subject: Re: Re: Privileges for INFORMATION_SCHEMA.SCHEMATA (was Re: [DOCS] Small clarification in "34.41. schemata")
Date: Thu, 17 Jan 2013 08:27:23 +0900
Message-ID: <CAB8KJ=j7LTykWUr1OGtKxgsja9ixgyiXSbnR_k=spLtGzQid1g@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAB8KJ=g317u8GVyoi+E8cByOGVa+iNVsriTgC+XFA-k2wqpCTA@mail.gmail.com>
<[email protected]>
<CAFmVg3ietJ_drX2QgTVX3Ba6bMpbBErx0YJ+DEEztF6hWC-_DA@mail.gmail.com>
<[email protected]>
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgsql-hackers>
2013/1/15 Tom Lane <[email protected]>:
> Casey Allen Shobe <[email protected]> writes:
>> On Wed, Jan 9, 2013 at 8:56 PM, Tom Lane <[email protected]> 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/[email protected]....
[6] http://www.postgresql.org/message-id/[email protected]
[7] http://www.postgresql.org/message-id/[email protected]
[8] Not that I'm claiming MySQL's implementation is authoritative or anything
Regards
Ian Lawrence Barwick
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
view thread (8+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Re: Privileges for INFORMATION_SCHEMA.SCHEMATA (was Re: [DOCS] Small clarification in "34.41. schemata")
In-Reply-To: <CAB8KJ=j7LTykWUr1OGtKxgsja9ixgyiXSbnR_k=spLtGzQid1g@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox