Fix the description of what Schema Usage controls

From: "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>
To: PostgreSQL Bug List <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Subject: Fix the description of what Schema Usage controls
Date: 2024-10-03 04:19:49
Message-ID: CAKFQuwbrjS6zDaZ+pKiKE0oXynjDgKzFPKmvpbE66XPfQ5qHGQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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.

Browse pgsql-bugs by date

  From Date Subject
Next Message Andrei Lepikhov 2024-10-03 04:23:14 Re: BUG #18643: EXPLAIN estimated rows mismatch
Previous Message Tom Lane 2024-10-03 03:19:03 Re: BUG #18637: CREATE INDEX won't look up operator classes in search_path if PARTITION BY is specified