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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Ian Lawrence Barwick <barwick(at)gmail(dot)com>
Cc: pgsql-docs(at)postgresql(dot)org, pgsql-hackers(at)postgresql(dot)org, Peter Eisentraut <peter_e(at)gmx(dot)net>
Subject: Privileges for INFORMATION_SCHEMA.SCHEMATA (was Re: Small clarification in "34.41. schemata")
Date: 2013-01-10 01:56:35
Message-ID: 11650.1357782995@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-docs pgsql-hackers

Ian Lawrence Barwick <barwick(at)gmail(dot)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

In response to

Responses

Browse pgsql-docs by date

  From Date Subject
Next Message Yuto HAYAMIZU 2013-01-10 03:28:52 Re: PostgreSQL docs in ePub format
Previous Message Ian Lawrence Barwick 2013-01-10 00:35:10 Small clarification in "34.41. schemata"

Browse pgsql-hackers by date

  From Date Subject
Next Message Bruce Momjian 2013-01-10 02:22:48 Lock levels for ALTER TABLE
Previous Message Noah Misch 2013-01-10 01:49:09 Re: lazy_vacuum_heap()'s removal of HEAPTUPLE_DEAD tuples