Marti Raudsepp <marti@juffo.org> writes:
> The documentation says: The view schemata contains all schemas in the
> current database that are owned by a currently enabled role.
> In other words: this view only displays schemas that are *owned* by
> your user, or roles that your current user inherits from (superuser
> sees everything of course). Sadly it doesn't list visible/accessible
> schemas.
> I think this is pretty surprising; not sure if it's just bad legacy or
> if there is some good reason for this behavior. I couldn't find any
> justification in the source code.
The justification is that the SQL standard requires the view to act that
way.
20.46 SCHEMATA view
Function
Identify the schemata in a catalog that are owned by a given user.
Definition
CREATE VIEW SCHEMATA AS
SELECT CATALOG_NAME, SCHEMA_NAME, SCHEMA_OWNER,
DEFAULT_CHARACTER_SET_CATALOG, DEFAULT_CHARACTER_SET_
SCHEMA,
DEFAULT_CHARACTER_SET_NAME, SQL_PATH
FROM DEFINITION_SCHEMA.SCHEMATA
WHERE ( SCHEMA_OWNER = CURRENT_USER
OR
SCHEMA_OWNER IN
( SELECT ROLE_NAME
FROM ENABLED_ROLES ) )
AND
CATALOG_NAME
= ( SELECT CATALOG_NAME
FROM INFORMATION_SCHEMA_CATALOG_NAME );
GRANT SELECT ON TABLE SCHEMATA
TO PUBLIC WITH GRANT OPTION;
> I think we should add a TODO item for fixing this?
Waste of breath. You could try lobbying the SQL committee to change the
standard, perhaps.
regards, tom lane
