Re: Authorization semantics and candidate pruning in GRAPH_TABLE

Hi Wataru,

On Tue, Sep 8, 2026 at 10:19 AM Wataru Naotsuka
<naotsukawataru(at)gmail(dot)com> wrote:
>
> Hello pgsql-hackers,
>
> I would like to ask about authorization behavior for unlabeled patterns in Property Graph / GRAPH_TABLE, in particular whether the current permission error behavior is an intentional authorization semantic or mainly a consequence of the current rewrite implementation.
>
> As I understand it, a user executing GRAPH_TABLE needs SELECT privileges on the property graph itself, as well as on the underlying base relations that are actually referenced. I also understand the motivation for using security-invoker semantics, in order to avoid unintended privilege escalation through the graph owner's privileges.
>
> However, unlabeled patterns seem to have an interesting consequence.
>
> My understanding of the current rewrite flow is roughly:
>
> candidate
> ↓
> does it form a valid path?
> ├─ no → does not remain as a Query/RTE
> └─ yes → generate RTE → permission check
>
> For example, suppose the possible vertex candidates are A, B, and C. The current user has SELECT privilege on A and C, but not on B.
>
> If a path containing B is structurally valid and therefore rewritten into an RTE, the entire query appears to fail with permission denied, even if that path would never actually return any rows.
>
> The first thing I would like to clarify is whether the following behavior is intentional:
>
> If any valid path candidate internally generated by unlabeled expansion contains an element for which the current user lacks the required privilege, the entire query is rejected.
>
> Is this an intended authorization semantic, or is it primarily a consequence of applying the normal RTE permission checks to the current rewrite result?
>
> I would also be interested to know whether SQL/PGQ or the SQL standard provides any requirement or guidance on this point.
>
> Relation to future candidate pruning
>
> One reason I am interested in clarifying this now is that I think there is room for more static candidate pruning before DFS or during path generation, in order to reduce the combinatorial explosion of possible path candidates.
>
> Even today, candidates that cannot form a structurally valid path are not retained as final Query/RTEs. In the future, it may also be possible to eliminate candidates earlier based on information such as available properties or WHERE predicates.
>
> For example, suppose a future optimization reduces:
>
> [A, B(!permitted), C]
> ↓ static pruning
> [C]
> ↓
> DFS → Query / RTE
>
> Before such an optimization, B may be materialized as an RTE and cause permission denied. After the optimization, B may disappear before RTE generation, and the same query may succeed.
>
> What I would especially like to avoid is a situation where, for semantically equivalent queries, whether permission denied is raised depends on implementation details of the rewrite, such as which candidates happen to be eliminated before RTE generation.
>
> For that reason, I wonder whether it would be better to establish the authorization boundary explicitly before introducing more candidate-pruning optimizations.
>
> I see two broad possibilities.
>
> 1. Make all-or-error an explicit semantic
>
> [A, B(!permitted), C]
> ↓ authorization
> permission denied
>
> Privileges would be checked against the semantic candidate set before optimization. If any required element is not accessible, the query would fail.
>
> This would preserve the current behavior while making the authorization result independent of later candidate pruning.
>
> 2. Filter candidates by the current user's privileges first
>
> [A, B(!permitted), C]
> ↓ authorization-based filtering
> [A, C]
> ↓ static pruning
> [C]
> ↓
> DFS → Query / RTE
>
> Personally, I am interested in exploring this direction.
>
> Under this model, GRAPH_TABLE could be viewed as querying the part of the graph visible to the current user. If authorization determines the candidate set first, then later pruning based on connectivity, properties, WHERE predicates, or other static information would be less likely to affect authorization behavior.
>
> This filtering step would also reduce the number of candidates passed to DFS. As a possible future step, I would like to explore optimizations such as eliminating an element candidate early when a property referenced by a restrictive predicate is known not to exist on that element. I am not proposing that optimization in this discussion; my main goal here is to clarify the authorization boundary first.
>
> Of course, visible-subgraph semantics also has trade-offs. Reachability, shortest paths, aggregates, and similar results may differ depending on which part of the graph is visible to the current user, so such semantics would need to be clearly defined and documented.
>
> There are also related questions that would need further discussion, for example:
>
> whether the same visible-subgraph semantics should apply when labels are explicitly specified, or only for unlabeled expansion;
>
> how column-level SELECT privileges should be handled;
>
> and where exactly such authorization filtering should occur in the rewrite pipeline.
>
> I am not proposing security-definer semantics or using the graph owner's privileges to access underlying tables. I agree with the motivation for security-invoker behavior. The question I am interested in is instead at what stage the set of elements visible to the current user should be determined.
>
> If the current all-or-error behavior is intentional, I would like to understand the reasoning and would then consider making authorization explicit before candidate pruning.
>
> If, on the other hand, the current behavior is mainly a consequence of the rewrite implementation and permission-based candidate filtering would be acceptable, I would be interested in exploring that as a first step toward more general static candidate pruning.
>
> I would appreciate any thoughts on the intended authorization semantics here, especially regarding SQL/PGQ and what authorization boundary would be preferable in view of future rewrite optimizations.

SQL/PGQ patch has been reverted now, but let me answer your question.
In order to know whether a relation would produce rows or not, it
needs to be read, which needs appropriate permissions. If a relation
can be pruned because the path it contains is incomplete, it is not
converted into an RTE and hence does not undergo permission check
since it will not be read. In case of a join or union we don't
selectively eliminate a query arm because of lack of permissions.
Similar rationale applies to the queries generated by SQL/PGQ.

--
Best Wishes,
Ashutosh Bapat