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
