Thread: Fix the description of what Schema Usage controls

Hi,

The following description is arguably saying that the absence of the usage privilege on a schema prevents a role from accessing objects within that schema.  This is untrue.

"For schemas, allows access to objects contained in the schema (assuming that the objects' own privilege requirements are also met). Essentially this allows the grantee to “look up” objects within the schema. Without this permission, it is still possible to see the object names, e.g., by querying system catalogs. Also, after revoking this permission, existing sessions might have statements that have previously performed this lookup, so this is not a completely secure way to prevent object access."

This needs to make clear that usage basically only promises to block naming the schema and its objects in the text of the submitted SQL Command, and that the rewriter may introduce objects to the query without consulting the usage privilege for the command author.  Thus I suggest something like the following:

"For schemas, allows the grantee to name objects contained in the schema in the text of their SQL Command.  This permission is not required to interact with objects in the schema, however, for two reasons.  First, the system may introduce objects to an execution plan not present in the command text.  In particular, by naming views created using security_invoker.  Second, the checks against the text happen only during query parsing, which is skipped during the execution of prepared or cached plans; and revoking usage from a role does not invalidate existing plans."

I feel like there could be even more nuance here (not sure about functions atm), but a complaint from a user using usage to deny access to tables where select privilege was granted to public in order to reduce the number of explicit grants in the system motivated me to at least get this text updated to deal with existing view behavior (point 1) and frame up the existing comment about cached plans in a similar manner (point 2).

I'm inclined to remove mention of our overall policy regarding being able to look up the definitions of all objects here, it seems not relevant to the discussion at hand which already is complicated.  That material should be (is?) covered separately as a capability all roles have by virtue of having logged into a database.  Not within the discussion of a grantable privilege.

David J.