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

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 (view raw or flat)
Thread:
Lists: pgsql-docspgsql-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

pgsql-docs by date

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

pgsql-hackers by date

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

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