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

Ian Lawrence Barwick <barwick@gmail.com> writes:
> The documentation says:
>   The view schemata contains all schemas in the current database that
> are owned by a currently enabled role.
> ( http://www.postgresql.org/docs/9.2/static/infoschema-schemata.html )
> However it shows all schemas if the user is a superuser, regardless of
> whether the schema is owned by the superuser.
> Does the documentation need clarifying? I'd suggest something like:
>   The view schemata contains all schemas in the current database that
> are owned by a currently enabled role, or all schemas if the currently
> enabled role is a superuser.

Well, that's wrong anyway, or at least it only represents touching a
small portion of the elephant.  The actual test, per
information_schema.sql, is

    pg_has_role(n.nspowner, 'USAGE');

so you can see any schemas owned by roles you have the ability to SET
ROLE to.  Superusers have that ability a fortiori; there is no special
case involved here.

The SQL standard says "Identify the schemata in a catalog that are owned
by a given user or role", and gives the pseudocode

WHERE ( SCHEMA_OWNER = CURRENT_USER
          OR
            SCHEMA_OWNER IN
            ( SELECT ROLE_NAME
              FROM ENABLED_ROLES ) )

so this behavior conforms with the spec, modulo the fact that there's
nothing about superusers in the spec.

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.  That is the test should be more like

    pg_has_role(n.nspowner, 'USAGE')
    OR has_schema_privilege(n.oid, 'CREATE, USAGE')

As things stand, a non-superuser won't see "public", "pg_catalog",
nor even "information_schema" itself in this view, which seems a
tad silly.

Thoughts?

            regards, tom lane

Casey Allen Shobe <casey@shobe.info> writes:
> On Wed, Jan 9, 2013 at 8:56 PM, Tom Lane <tgl@sss.pgh.pa.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

2013/1/15 Tom Lane <tgl@sss.pgh.pa.us>:
> Casey Allen Shobe <casey@shobe.info> writes:
>> On Wed, Jan 9, 2013 at 8:56 PM, Tom Lane <tgl@sss.pgh.pa.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

On 1/9/13 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.  That is the test should be more like
>
>     pg_has_role(n.nspowner, 'USAGE')
>     OR has_schema_privilege(n.oid, 'CREATE, USAGE')
>
> As things stand, a non-superuser won't see "public", "pg_catalog",
> nor even "information_schema" itself in this view, which seems a
> tad silly.

I agree it would make sense to change this.

On Thu, Jan 31, 2013 at 03:49:36PM -0500, Peter Eisentraut wrote:
> On 1/9/13 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.  That is the test should be more like
> >
> >     pg_has_role(n.nspowner, 'USAGE')
> >     OR has_schema_privilege(n.oid, 'CREATE, USAGE')
> >
> > As things stand, a non-superuser won't see "public", "pg_catalog",
> > nor even "information_schema" itself in this view, which seems a
> > tad silly.
>
> I agree it would make sense to change this.

Is this the patch you want applied?  The docs are fine?

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

On Sat, 2013-09-07 at 14:01 -0400, Bruce Momjian wrote:
> > >     pg_has_role(n.nspowner, 'USAGE')
> > >     OR has_schema_privilege(n.oid, 'CREATE, USAGE')
> > > 
> > > As things stand, a non-superuser won't see "public", "pg_catalog",
> > > nor even "information_schema" itself in this view, which seems a
> > > tad silly.
> > 
> > I agree it would make sense to change this.
> 
> Is this the patch you want applied?  The docs are fine? 

I have committed it with a documentation update.