public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tom Lane <[email protected]>
To: Ian Lawrence Barwick <[email protected]>
Cc: [email protected]
Cc: [email protected]
Cc: Peter Eisentraut <[email protected]>
Subject: Privileges for INFORMATION_SCHEMA.SCHEMATA (was Re: Small clarification in "34.41. schemata")
Date: Wed, 09 Jan 2013 20:56:35 -0500
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAB8KJ=g317u8GVyoi+E8cByOGVa+iNVsriTgC+XFA-k2wqpCTA@mail.gmail.com>
References: <CAB8KJ=g317u8GVyoi+E8cByOGVa+iNVsriTgC+XFA-k2wqpCTA@mail.gmail.com>
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgsql-docs>

Ian Lawrence Barwick <[email protected]> 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


-- 
Sent via pgsql-docs mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs

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]
  Subject: Re: Privileges for INFORMATION_SCHEMA.SCHEMATA (was Re: Small clarification in "34.41. schemata")
  In-Reply-To: <[email protected]>

* 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