Ian Lawrence Barwick <barw...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers