Make SQL SECURITY views an optimization barrier - #112847

Changelog category (leave one):

• Critical Bug Fix (crash, data loss, RBAC)

Changelog entry (a user-readable short description of the changes that goes into CHANGELOG.md):

A view with SQL SECURITY DEFINER or SQL SECURITY NONE is now an optimization barrier, so an expression in the query reading the view is never evaluated on rows that the view itself filters out. Previously such an expression could observe the hidden rows through an exception, and a view used to restrict which rows a user may see did not actually restrict them.

The problem

SQL SECURITY DEFINER is widely used to build a view that restricts which rows a user may see:

CREATE VIEW user_query_log DEFINER = default SQL SECURITY DEFINER
AS SELECT * FROM system.query_log WHERE user = currentUser();
GRANT SELECT ON user_query_log TO alice;

alice has no grant on the source table, only on the view. But the outer WHERE and the view's own WHERE are merged into a single filter over the source table, and nothing guarantees which of the two decides first. Any function that can signal through a side channel therefore observes the rows the view is supposed to hide:

-- as alice, whose only grant is SELECT ON user_query_log
SELECT event_time FROM user_query_log
WHERE throwIf(query LIKE '%some probe%', 'DISCLOSED');
-- DB::Exception: DISCLOSED ... While executing MergeTreeSelect

That is a one-bit oracle per query. Exception messages carry the offending value, which turns it into a plain read of the hidden rows:

SELECT * FROM v_secrets WHERE if(owner = 'alice', 1, toUInt8(secret)) = 1;
-- DB::Exception: Cannot parse string 'TOP-SECRET-BOB-42' as UInt8

Both work on SQL SECURITY NONE as well, and both work with enable_analyzer = 0.

Row policies on the source tables are not affected: such a row policy is a separate row_level_filter that the reading step always applies before PREWHERE and before any pushed-down filter. This PR gives a view's own filtering the same standing. A row policy on the view itself is applied by both planners as a Filter step above the view's subplan (StorageView takes no PREWHERE, so it is never pushed into the read), and that step is sealed as well: it decides which rows the invoker sees, so the invoker's own predicate must not merge into it.

The fix

IQueryPlanStep gets a security_barrier flag. After StorageView::readImpl builds the view's subplan, every step that can drop rows is marked, and optimizations that move a step down the plan refuse to cross a marked step:

• tryMergeExpressions, tryMergeFilters, tryPushDownFilter and tryMergeFilterIntoJoinCondition refuse when the child is a barrier;
• tryPushDownVolumeReducingFunction (default on) refuses when the parent or the child is a barrier: it splices the invoker's length / lengthUTF8 / empty / notEmpty below a Filter or Sorting step, where the function would scan the payload of the rows the step drops before the barrier;
• optimizePrewhere refuses to pull an outer filter into a barrier source — conditions are combined into the prewhere DAG with and, which gives no ordering guarantee — and transfers the barrier onto the source when it absorbs the view's own filter;
• trySplitFilter moves the flag onto the new lower FilterStep, which is the one that still drops rows.
• tryPushDownLimit refuses to move the invoker's LimitStep below a barrier step: once across the seal it would seed DistinctStep::limit_hint or a sorting limit inside the view's subplan, and optimizeLimitForAggregationInOrder walked through the seal to seed AggregatingStep::limit_hint the same way — hints that stop reading the source once enough visible rows are produced, so read_rows, progress and timing depended on the rows the view drops or collapses. Both walks, and pushLimitByIntoSort, fail closed on a barrier step.

Blocking the steps that evaluate expressions is not enough on its own, because index analysis reaches the source by a different route and skips granules by the values of the rows the view hides — read_rows then tells the invoker whether such a row exists, with no exception needed. Ten walks are fenced as well:

• optimizePrimaryKeyConditionAndLimit walks up from the reading step and hands every FilterStep it meets to the source's key condition; it now stops once it has consumed a barrier. The barrier's own condition still reaches the source — it is the definer's, and it is what decides which rows exist for the view — but nothing above it does.
• StorageView::readImpl forwarded query_info.filter_actions_dag into the view's inner analyzer, where Planner::collectFiltersForAnalysis injects it into the inner plan and the filters it collects reach the inner tables' index analysis. It is no longer forwarded for a barrier view, which costs such a view over Distributed its shard skipping on the outer predicate.
• buildSortingDAG in the read-in-order analysis descended through the view subplan and pulled outer FilterStep predicates into the fixed columns and the merged DAG, so an outer ORDER BY / GROUP BY / DISTINCT / LIMIT BY could still shape how the source below the barrier reads. It now reports a barrier in the chain and every consumer (sorting, aggregation, DISTINCT, LIMIT BY, the normal-projection choice, top-K, and the Merge child-plan walk) skips the analysis for that chain. Unlike the primary-key walk, the barrier step cannot be consumed here: the sort description belongs to the top of the chain, and a DAG missing the steps above the barrier could resolve a renamed column by name to the wrong source column and change the result order — so the analysis is skipped entirely, which is fail-closed and keeps correctness because the sorting step stays in the plan.
• tryOptimizeTopK rewrites an ORDER BY ... LIMIT into a dynamic __topKFilter PREWHERE and minmax-skip-index granule pruning on the source, walking LimitStep → SortingStep → ExpressionStep → FilterStep → ReadFromMergeTree — and both rewrites are on by default. The walk now fails closed on the first barrier step it meets, including a reading step that carries the barrier after optimizePrewhere absorbed the view's own filter.
• tryTopKThroughJoin peels the expression chain between the invoker's Sorting and a Join and grafts the invoker's Sort + Limit onto the join's preserved input, re-running the optimization passes on that subtree. For a barrier view whose inner query is a join, the graft landed below the seal — verified live pre-fix on both analyzers. The pass now fails closed on a marked Limit/Sorting (the pattern lies inside a view), a marked peeled expression (the seal), and a marked join (a join of a barrier view is always marked, not being row-preserving). Grafting above the seal of the invoker's own join input stays allowed: the inserted Sort consumes its whole input and the re-run passes are individually fenced.
• registerLeftSideIndexAnalysisSecondPass of the join runtime filters walked from the __applyFilter step (which the fenced filter pushdown correctly keeps above the seal) down through every single-child expression or filter step — sealed or not — to the ReadFromMergeTree inside the view, and registered the invoker's build-side keys for granule pruning there. The walk now fails closed on the first barrier step. A live disclosure could not be constructed — the descriptor's key name has to match the read's namespace and the consumption path declined in every configuration tried — so this fence makes the contract structural rather than incidental. The filter itself is now refused as well: tryAddJoinRuntimeFilter fails closed when either side's subtree carries a barrier. Besides the disclosure (the keys of the rows a view hides leaving the seal, and the view's read_rows depending on the invoker's expressions), that is a wrong-result hazard — the seal keeps the view's read local while the other side is read by parallel replicas, so the filter is built from the coordinated side's own share of the ranges only and drops every row of the sealed side whose key went to another replica.
• The vector search rewrites — the vector-similarity-index pass and the quantized-codes shortlist — walk the same chain and prune the source to the top-N candidates of the invoker's ORDER BY. Both now fail closed on a barrier step the same way.
• Projection planning: QueryDAG::build in projectionsCommon.cpp collects every filter of the chain below the aggregation — the invoker's predicates together with the view's own filtering — and both optimizeUseAggregateProjections (including minmax_count_projection) and optimizeUseNormalProjections prune parts and marks with it, so a projection-enabled table under a filtering barrier view still let the invoker's predicate shape the read. The DAG build now fails closed on the first barrier step, which makes the projection optimizations decline the read entirely.
• Join reordering: addChildQueryGraph in optimizeJoin.cpp peeled the sealing step — as a trivial pass-through step, or by merging it into the child join, which query_plan_merge_expression_into_join enables by default — and then flattened the join inside the view into the join graph of the invoker, where the relations are reordered by their estimated sizes. A sealed subplan is now one opaque relation of the graph: the seal is neither peeled nor merged, a barrier child join is neither flattened nor pre-optimized for statistics, and the two walks that mirror this one (the overlapping-column-name pre-check and the exposed-column check) are fenced identically so that they keep agreeing with it.
• optimizeJoinByShards (setting query_plan_join_shard_by_pk_ranges) merged the expression and filter DAGs of the chain through the seal and could then make the reading inside the view emit one output port per primary-key range of the invoker's join keys. The walk now produces no result for a barrier step, so nothing propagates above the seal; the sharding of the joins the view itself contains, which the invoker does not control, is still applied.

One more family reaches the source without going through its index analysis at all. optimizeDistinctPerPartition, optimizeLimitByPerPartition and optimizeAggregationPerPartition walk down through the sealing step and ask the reading to output each partition through a separate port, and applyStreamDisjointness carries the resulting partition disjointness back up across the seal, so the invoker's DISTINCT / LIMIT BY / GROUP BY skips its stream merging as well. Read scheduling, progress and resource consumption below the view then depend on how the rows the view drops are spread over the partitions, and all three allow_*_partitions_independently settings default to 1. Both directions now fail closed on a barrier step.

Five passes endanger the barrier by rebuilding steps rather than by walking past them, and each now fails closed on a barrier step:

• lazy materialization splits every Expression / Filter step of the chain into a main and a lazy half, and the rebuilt steps do not carry the barrier flag, so the post-lazy tryMergeExpressions / tryMergeFilters passes saw an unmarked chain and could merge an invoker predicate into the view's own filtering — reopening the exception oracle itself, not just the read-shaping one;
• tryLiftUpUnion rebuilds the UnionStep and clones the parent step into the branches as fresh unmarked steps, so a barrier view over UNION ALL lost its seal and tryPushDownFilter could then duplicate an invoker predicate into the branches;
• tryExecuteFunctionsAfterSorting replaces the expression under a SortingStep with two new unmarked steps, which would strip the seal of a wrapper view and let the read-in-order and top-K walks descend through it again.
• the GROUP BY top-K optimization (tryOptimizeGroupByTopK, default on) walks Limit → Sorting → Expression → Aggregating with the seal matched as the expression, turns the view's own aggregation into a bounded heap sized by the invoker's LIMIT, and for a bare LIMIT inserts a synthesized, unmarked sorting step below the seal — live on both analyzers.
• the sibling bucket top-K optimization (tryPushBucketTopKIntoAggregation, query_plan_aggregation_bucket_top_k, default on) walks Limit → Sorting → Expression* → Aggregating and turns on AggregatingStep::enableBucketTopK for the view's own aggregation, sized and directed by the invoker's ORDER BY count() LIMIT — live on the analyzer.
• tryLiftUpArrayJoin splits the expression or filter above an ArrayJoinStep, moves one half below the ARRAY JOIN, and rebuilds both halves as fresh unmarked steps. When the parent is the seal of a view whose plan contains ARRAY JOIN (the seal is non-trivial whenever the view declares explicit column names or types), the invoker's predicate descended below the ArrayJoinStep and was evaluated on rows hidden by empty arrays — a live disclosure through the exception oracle, on both analyzers.
• tryLowerArrayJoinFunction (opt-in through query_plan_lower_array_join_function) rebuilds a filter or expression containing arrayJoin into Expression → ArrayJoin → Filter as fresh unmarked steps, and the default-on tryFuseFilterIntoArrayJoin then replaces the filter with an unmarked pass-through ExpressionStep. A view-keyed additional_table_filters entry such as arrayJoin(tags) = 'public' lost its barrier this way, the invoker's WHERE merged into the rebuilt filter and descended below the ArrayJoinStep, and throwIf fired on the hidden rows on both planners. Both rewrites now put the flag on every step they create — the pieces of the lowered filter and the ArrayJoinStep that carries the fused element filter — so the rewrite still happens and nothing from above can merge into or cross it.
• tryPushDownFilter refused to push an outer filter below a barrier child, but when the FilterStep being pushed was itself the barrier — the WHERE of the view — every step it was rebuilt into came out unmarked: the pushed FilterStep and the replacement ExpressionStep of addNewFilterStepOrThrow and of the join pushdown, the copies made for the branches of a UnionStep, and the plain FilterStep that ReadFromMerge::addFilter and ReadFromLocalParallelReplicaStep::addFilter inject into their separately optimized child plans. For a view over a Merge table the optimized plan ended up with no barrier step at all. The flag now travels through every one of these rewrites, and a ReadFromMerge / ReadFromLocalParallelReplicaStep step that has swallowed a barrier filter becomes a barrier itself. Contract hardening rather than a measured leak: in every shape measured the seal or a neighbouring fence still blocked the invoker's predicate.

Measured on a DEFINER view that exposes no row, over 100000 rows sorted by key, reading WHERE key = <a key only a hidden row has> against WHERE key = <a key nothing has>: 576 rows read against 0 without this, and 1000000 against 1000000 with it, on both enable_analyzer = 1 and enable_analyzer = 0.

EXPLAIN SYNTAX is left alone. It builds InterpreterSelectQuery with only_analyze, whose plan reads from ReadNothingStep, so no expression of the outer query is ever evaluated on a row and the view is still inlined for the diagnostic.

Two paths substitute the view into the outer query before a plan exists, so a plan-level barrier cannot see them, and both are closed the same way — such a view is not inlined and is read through StorageView::read, which keeps the outer predicate in a step above it:

• with enable_analyzer = 0, InterpreterSelectQuery replaces the view with a subquery and TreeRewriter merges the predicates;
• with analyzer_inline_views = 1, QueryAnalyzer::inlineViewSubqueryIfNeeded does the same in the query tree.

Without this, SET enable_analyzer = 0 or SET analyzer_inline_views = 1 would bypass the fix entirely.

The flag also travels with a serialized query plan. A distributed worker deserializes a fragment and optimizes it again, so a barrier it does not know about is a barrier it will optimize across. QueryPlan::serialize writes the flag per step and fails closed when the negotiated query plan serialization version predates it (the version is bumped to 15), rather than sending a plan that silently loses its protection. The Cascades optimizer (make_distributed_plan = 1 together with enable_cascades_optimizer = 1) rebuilds the plan from its own memo, and its rules neither see nor preserve the flag, so it stays off for a plan that carries a barrier; the rule-based distributed passes, which are fenced step by step, run instead.

Task-based parallel replicas bypass the plan entirely: they ship the view's inner query as SQL text to the other replicas, where it is re-planned under the connection's own identity — the replica applies the row policies of its connecting user and of the initial user (the invoker), but the definer is neither of those, so the definer's row policies on the inner tables and the definer profile's additional_table_filters were silently dropped and the rows they hide came back through the union into the invoker's plan, above the barrier (deterministic with parallel_replicas_local_plan = 0, a scheduling race otherwise — which is how the ParallelReplicas CI configuration caught it). StorageView::readImpl now reads the inner query of a row-hiding barrier view without parallel replicas, failing closed. This costs cross-node parallelism for such views, which is why 04545_parameterized_view_sql_security now asserts the fence for its filtering DEFINER view and keeps its "parallel replicas are really used" guard on a new parameterized DEFINER view that provably hides no rows.

Plan-based parallel replicas (parallel_replicas_plan_based = 1) do not go through that fence: the planner builds a plain local plan, and applyParallelReplicas then plants a ParallelReplicasSplitStep above every eligible MergeTree read, lifts it through the expression and filter steps above, and ships the fragment to be deserialized and executed on the other replicas under the connection's identity. It found the ReadFromMergeTree below a view's barrier just like any other read and lifted the split through the barrier steps, so the view's inner query left the initiator anyway. The pass now stops at a security barrier: collectReadsToDistribute returns nothing below a barrier step (the view's root is sealed, so its whole subplan stays local), the Merge expansion skips barrier subtrees, the union and join lifters refuse a barrier node, and a split marker reaching a barrier parent is a logical error. The join lifter also refuses to lift a split when the broadcast side of the join contains a barrier: that side is not coordinated, so it carries no split marker of its own, but it is cloned into the fragment and executed in full by every replica, which would ship a protected view sitting on the non-coordinated side of a LEFT / RIGHT join. 05100_sql_security_view_barrier_plan_based_parallel_replicas checks that the invoker's plan over a DEFINER / NONE view has no remote parallel replicas read while the INVOKER control over the same table does.

A third parallel-replicas path reached below the barrier without touching the view's plan at all: with parallel_replicas_allow_view_over_mergetree = 1 the planner looks through a "simple" view and reads the MergeTree table below it, StorageView::getUnderlyingMergeTreeStorageForParallelReplicas being the decision. The storage it returns is announced and read under the outer query's context and identity, so the view's filtering — its own row policies, the definer's row policies on the inner table, the definer profile's filters — is not part of that read. It now fails closed for a security barrier view that can hide rows, the same rule the other pre-plan decisions use, which also covers findParallelReplicasQuery and the getViewContext fast path since all of them go through that one function; a view that provably hides nothing keeps the optimization. 05104_sql_security_view_barrier_view_over_mergetree checks that the invoker's plan over a DEFINER / NONE view carries no parallel-replicas read with the setting on, while the INVOKER control over the same table does — with sql_security_views_are_optimization_barriers = 0 both barrier views distribute, so the check does not pass vacuously.

The same shortcut also produced a wrong result, not only a disclosure. It only decides that the query can use parallel replicas; the query the replicas receive still reads the view, and a replica plans it on its own. When an additional_table_filters entry of the invoker applies to the view, the barrier keeps the view a table expression instead of inlining it, so the replica reads it through StorageView::readImpl, which switches parallel replicas off for the inner query — every replica then read the whole view with no coordination and the result was the union of all of them, one copy of every row per replica. getUnderlyingMergeTreeStorageForParallelReplicas now declines the shortcut exactly when the replica would decline to inline the view: when an entry applies to the view by its name or by the alias the outer query gives it, the same hasAdditionalTableFilter rule as in QueryAnalyzer::inlineViewSubqueryIfNeeded (the callers hand the table expression's original alias over; an entry keyed to an internal __table<N> alias counts too, since that is how the shipped query text names the view; a caller that does not know the alias fails closed on any entry). An entry keyed to an unrelated table cannot make a replica take that path, so a projection-only barrier view keeps the shortcut with it, like its INVOKER twin.

Views that hide nothing are left alone

Only a view that can actually drop rows becomes a barrier. A step is row-preserving if it is an ExpressionStep, a SortingStep without a limit, or a source step without PREWHERE; if none of the view's steps is anything else, nothing is marked and the plan is what it is today.

The pre-plan paths make the same distinction. StorageView::canHideRows proves over the view's definition that the inner query preserves every row of a plainly readable source, and only a view for which that proof fails loses inlining and the forwarded outer filter. The proof fails closed: filters, limits, aggregation, DISTINCT, joins, ARRAY JOIN, SAMPLE/FINAL, multi-select unions, table functions, and a FROM that is itself a view or a view-wrapping engine (Merge, Buffer, anything remote) all count as able to hide rows. The expression-level carriers are found behind SQL user-defined functions too: the classifier runs before UDF expansion, so it follows the lambda body of every SQL UDF it meets, recursively, and fails closed on a recursive definition, on a nesting deeper than 16, and on a body that is not a SQL lambda, so CREATE FUNCTION f AS (a) -> arrayJoin(a) cannot disguise a row-hiding view as projection-only. The proof classifies the storage that actually serves the read, not the object the name resolves to: proxy layers (a lazily loaded table of a database with lazy_load_tables = 1, or a table created from a table function) and Alias tables are unwrapped first, failing closed on a chain that cannot be resolved, and a storage that rewrites its own reads with FINAL and a _sign filter (MaterializedPostgreSQL) counts as able to hide rows even without another wrapper. The row-policy check then covers every table of that chain, not only the one that serves the read: a read through an Alias combines the policies of the Alias and of its target, so a policy defined on the Alias alone hides rows too. A SETTINGS clause of the view's own query is inspected rather than rejected outright: only settings from a fixed allowlist of execution tuning (max_threads, max_block_size, memory and read-method knobs, plan optimization switches, logging) leave the proof intact, while any other change — final, limit, max_rows_to_read under a read_overflow_mode = 'break' profile, prefer_column_name_to_alias, a reset to DEFAULT, query parameters — counts as able to hide rows. The proof also runs against the view's effective security context, where the settings-only part of it lives in StorageView::effectiveContextCanHideRows: a definer profile that sets limit / offset, additional_result_filter, final, or any quota-like limit paired with a non-throwing overflow mode (max_rows_to_read / max_bytes_to_read and their _leaf twins, max_execution_time and its _leaf twin), and a row policy on the source table that applies to the definer, count as hiding rows as well. The limits of GROUP BY, sorting and DISTINCT (max_rows_to_group_by, max_rows_to_sort / max_bytes_to_sort, max_rows_in_distinct / max_bytes_in_distinct with their overflow modes) hide rows only of a query that contains the corresponding operator, so they live in StorageView::shapeDependentOverflowCanHideRows instead and are applied once the shape of the query is known — a projection-only view under a definer profile that merely carries group_by_overflow_mode stays fully optimizable. The SETTINGS clause of the view's own query gets the same split: settingsClauseCanHideRows takes the shape flags and accepts those overflow settings only for a query that provably lacks the operator (a FROM subquery fails closed on all of them, as its shape is not inspected at that level), so SELECT owner, secret FROM t SETTINGS max_rows_to_group_by = 1, group_by_overflow_mode = 'any' is projection-only as well. max_result_rows / max_result_bytes are not part of that set: getViewSubqueryContext resets them for the view's own subquery, so they never truncate the inner query's result. additional_table_filters is shape-dependent in the same way, but on the sources rather than on the operators: an entry only grows a filter step below the view when it names one of the view's own sources, by alias, by unqualified name under the current database, or fully qualified — the three forms parseAdditionalFilterAstIfNeeded and parseAdditionalFilterConditionForTable match at execution time. canHideRows matches the entries against the source table once it has resolved it (StorageView::additionalTableFiltersApplyTo, shared with the view-keyed check hasAdditionalTableFilter): the name the view's query uses, its alias, and the storage a proxy or Alias table forwards the read to, with a malformed value counting as applying. The same proof serves the view's own SETTINGS additional_table_filters clause, which settingsClauseCanHideRows now hands back to the caller instead of rejecting by name; a query whose source is not a single plainly named table (a FROM subquery, no FROM) has nothing to match the clause against and fails closed on it. So a definer whose profile filters some unrelated table of their own no longer turns every one of their SQL SECURITY projection views into a barrier. The analyzer-time ORDER BY ... LIMIT pushdown into a view (pushOrderByIntoView) consults the same effective context: additional_result_filter grows a filter step on top of the inner query's result after the inner plan is built, so an injected inner LIMIT truncated the rows before the filter dropped its share and the view returned fewer rows than the filtered top-N — a wrong result; the pushdown now calls StorageView::effectiveContextCanHideRows itself, so it rejects exactly the same set and the two guards cannot drift apart. It also calls StorageView::shapeDependentOverflowCanHideRows with has_sort, because the rewrite injects a sort into the view's inner query itself, so a definer profile sort_overflow_mode = 'break' with a sort limit would truncate the injected top-N even where the view has no ORDER BY of its own. additional_table_filters needs no guard there: an entry of the definer profile keyed by the view's source table is applied at the source read, below the injected ORDER BY ... LIMIT, exactly like a WHERE of the view's query (which the pushdown allows), and an entry keyed by the underlying Distributed table is forwarded to the shards by parseAdditionalFilterAstIfNeeded; a SETTINGS additional_table_filters clause of the view's own query still fails closed there, as the pushdown does not resolve the source. Its shape guard is now complete as well: a GROUP BY ALL view body keeps the groupBy() expression list empty and only raises the group_by_all flag, so an aggregating view still got the rewrite and every shard aggregated its own rows with a Top-K on the group keys instead of the coordinator merging the aggregate states of all rows first — a shard could drop a group of the global top-N. The flag, the WITH TOTALS / ROLLUP / CUBE / GROUPING SETS markers, limit_by_all / order_by_all and the LIMIT BY payload are all checked now, mirroring the shape test of the trivial-view pushdown path. So a projection-only DEFINER view produces exactly the plan of the same view declared SQL SECURITY INVOKER on every path, which the test pins byte-for-byte. An additional_table_filters entry (from the definer's profile or from the view's own SETTINGS clause) counts as a filter of the view only when it is keyed the way the filter-application paths match it — by the name the view's query uses, by its alias, or by the storage id that name resolves to. For a source that is an Alias or a lazy proxy that is the Alias / proxy itself: StorageAlias::read and StorageProxy::read forward the already parsed filter without matching it again, so an entry keyed by the target of the Alias never filters anything and does not turn a projection-only view into a barrier (the row policies of every table of the chain are still honoured).

The caller's own additional_table_filters / additional_result_filter no longer reach the view's inner query at all. StorageInMemoryMetadata::getSQLSecurityOverriddenContext used to replay every changed setting of the caller into the definer's (or, for SQL SECURITY NONE, the unrestricted) context, so a filter keyed on the view's inner table had its expression — scalar subqueries and table functions included — evaluated with the definer's privileges over columns the view does not expose, and a caller's additional_result_filter replaced the one of the definer's profile. Both settings are now dropped from the replayed changes, as system.user_query_log already does; the caller's filters still apply to the caller's own query, and a filter keyed by the view itself keeps reaching the inner query as query_info.additional_filter_ast.

optimize_trivial_view_pushdown_to_distributed is the last of those pre-plan paths. It replaces a trivial view over a Distributed table with the view's inner query and reads the Distributed table directly, so StorageView::readImpl never runs and the plan has no sealing step at all: the invoker's predicate is merged with the view's own WHERE and evaluated on the shards below it. StorageView::tryGetUnderlyingDistributed used to reject only SQL SECURITY DEFINER, so a row-hiding SQL SECURITY NONE view still took the rewrite; it now declines it for any barrier view that canHideRows. The proof runs with one relaxation on this path, remote_source_is_read_identically: the alternative to the rewrite reads the very same Distributed table through StorageDistributed::read, and whatever a shard resolves that table to is subject to that shard's own barrier, so a remote source is not by itself a reason to give up on the view - without the relaxation the guard would also drop the pushdown for the projection-only views the optimization exists for. It is not propagated into nested subqueries.

Measured on 20M rows with SELECT sum(length(payload)) FROM v WHERE tag = 'RARE', from system.query_log:

A projection-only view is unaffected. A filtering view does pay: PREWHERE then holds only the view's own condition, so it no longer skips granules on the outer predicate. That is the inherent price of the guarantee — PostgreSQL's security_barrier views behave the same way — and it applies only to views that restrict rows, which are exactly the ones where it matters.

The new server setting sql_security_views_are_optimization_barriers (default 1) turns it off. It is deliberately a server setting and not a user setting: a user setting would be turned off by the very query that is trying to read the hidden rows.

Testing

04758_sql_security_view_barrier_read_rows covers the read_rows oracle through index analysis, on both analyzers, and prints DISCLOSED on both with the setting off. 04670_sql_security_view_barrier covers the leak on DEFINER and on NONE, with enable_analyzer = 1, with enable_analyzer = 0 and with analyzer_inline_views = 1, the value leak through a cast error message, the same oracle through a shard with serialize_query_plan = 1, that a projection-only DEFINER view and an INVOKER view still have the outer predicate merged into the view's own filter, and that a projection-only view keeps PREWHERE. 04813_sql_security_view_barrier_read_in_order pins the read-in-order fence: the INVOKER twin and a projection-only DEFINER view read InOrder, a filtering DEFINER view does not, under both analyzers, with unchanged results. 04817_sql_security_view_barrier_top_k pins the top-K fence: the INVOKER twin gets the __topKFilter, the filtering DEFINER view does not, and read_rows of an ORDER BY ... LIMIT 1 over twin views is identical whether or not the hidden row holds the extreme minimum of the sort column that minmax pruning would rank first. 04818_sql_security_view_barrier_lazy_materialization pins the lazy-materialization fence the same way and checks that an invoker predicate over the view is never evaluated on the hidden row. 04821_sql_security_view_barrier_projections pins the projection fence: the INVOKER twin uses both a normal and an aggregate projection, the filtering DEFINER view uses neither, and read_rows of a predicate probe over twin views is identical whether or not the hidden row matches it. 04825_sql_security_view_barrier_union pins that an outer predicate over a filtering DEFINER view on UNION ALL stays in a single filter above the union — before the tryLiftUpUnion fix it was duplicated into the branches — while the INVOKER twin keeps the pushdown. 04826_sql_security_view_barrier_functions_after_sorting pins that an ORDER BY ... LIMIT over a wrapper DEFINER view (a Merge table over a nested filtering view) produces no in-order reading and no __topKFilter with query_plan_execute_functions_after_sorting on, while the INVOKER twin exploits the source order. 04827_sql_security_view_barrier_masked_wrappers pins that the classification survives engine masking: a DEFINER view over a Merge wrapper behind a lazy TableProxy (re-masked before every round, since planning materializes the proxy) or behind an Alias table plans differently from its INVOKER twin on both analyzers and with analyzer_inline_views = 1. 04832_sql_security_view_barrier_limit_pushdown pins the LIMIT fence: over a DISTINCT DEFINER view the invoker's LimitStep stays above the sealing step on both analyzers — before the fix it crossed the seal and sat directly on the DistinctStep, where it seeds the hint — and read_rows of an ORDER BY ... LIMIT 1 over twin in-order GROUP BY views is identical whether the first group holds one raw row or almost all of them. 04837_sql_security_view_barrier_per_partition pins the per-partition fence, with the allow_*_partitions_independently settings pinned to their defaults (the harness randomizes them): an outer DISTINCT / LIMIT BY over a filtering DEFINER view produces none of the Skip stream merging / Read each partition through separate port markers its INVOKER twin gets, and the disjointness of a view whose own inner DISTINCT legitimately requests per-partition reading does not propagate across the seal into the invoker's GROUP BY / LIMIT BY — before the fix every DEFINER case was identical to its twin. 04840_sql_security_view_barrier_array_join pins the ARRAY JOIN lift-up fence with an exception oracle on both analyzers: the INVOKER twin's predicate legitimately descends below the ARRAY JOIN and throws on the row an empty array hides, while the DEFINER view counts without throwing and its plan keeps every throwIf line above the ArrayJoin step — before the fix the DEFINER view threw as well. 04891_sql_security_view_barrier_top_k_through_join pins the top-K-through-join fence: the INVOKER twin of a view over a LEFT JOIN gets the preserved-side Sort + Limit graft below the join, the DEFINER twin keeps its join input untouched — before the fix the DEFINER plan got the graft below the seal. 04892_sql_security_view_barrier_join_runtime_filter pins the join-runtime-filter contract: with enable_join_runtime_filters_index_analysis = 1, twin filtering DEFINER views over tables identical except for the hidden row's primary-key value read exactly the same number of rows, while the INVOKER control is pruned by the build-side key. Every setting the plan shape depends on is pinned, because the test also runs with randomized settings. 04893_sql_security_view_barrier_join_reorder pins the join-reordering fence: with query_plan_merge_expression_into_join = 1 and no overlapping column names between the relations, the INVOKER twin of a view over an INNER JOIN with a WHERE is flattened and reordered into a three-relation graph with no step left converting the view subquery result, while the DEFINER twin keeps that step and stays one relation, with equal results. 04894_sql_security_view_barrier_join_shard_by_pk pins the join-sharding fence: with query_plan_join_shard_by_pk_ranges = 1 and join_algorithm = 'full_sorting_merge', plain tables, a DEFINER view that hides nothing and the INVOKER twin of the filtering view all get a Sharding line, while the filtering DEFINER view gets none, with equal row counts. Those two fences are contract-made-real rather than live leaks: with the fence removed the DEFINER twin behaves the same, because a neighbouring fence of this PR happens to block the pass first — which is exactly why the barrier is checked explicitly. With sql_security_views_are_optimization_barriers = 0 every one of those lines changes, so none of them passes vacuously. 05042_sql_security_view_barrier_group_by_top_k pins the GROUP BY top-K fence on both analyzers: the INVOKER twin of a GROUP BY view under a bare LIMIT gets the Top-K heap and the synthesized Sorting for GROUP BY top-K step, the DEFINER view gets neither — before the fix it got both. 05211_sql_security_view_barrier_aggregation_bucket_top_k pins the bucket top-K fence: under query_plan_aggregation_bucket_top_k = 1 the INVOKER twin carries Bucket top-K, the DEFINER and NONE views do not — before the fix they did. 05043_sql_security_view_barrier_additional_result_filter pins the effective-context fence: an invoker predicate over a projection-only DEFINER view whose definer profile sets additional_result_filter never observes the rows the filter hides, under both analyzers and with analyzer_inline_views = 1 — before the fix the throwIf oracle disclosed them on every path. 05059_sql_security_view_barrier_parallel_replicas pins the parallel-replicas fence with the settings set explicitly (parallel_replicas_local_plan = 0 made the leak deterministic before the fix): no throwIf disclosure and the correct visible rows through a DEFINER view whose definer has a row policy on the source table, on both analyzers and with analyzer_inline_views = 1. 05060_sql_security_view_barrier_order_by_pushdown pins the ORDER BY ... LIMIT pushdown guard: SELECT k FROM v ORDER BY k LIMIT 1 over a projection DEFINER view whose definer profile sets additional_result_filter = 'k = 2' returns 2 on every path — before the fix it returned no row — and the twin view without the filter still gets the inner sort pushed. 05063_trivial_view_pushdown_security_barrier pins the trivial-view pushdown fence: with optimize_trivial_view_pushdown_to_distributed = 1, a filtering SQL SECURITY NONE view over a Distributed table keeps its subquery step, while the projection-only twin is still rewritten - with sql_security_views_are_optimization_barriers = 0 the filtering view is rewritten too, so the check does not pass vacuously. 05065_sql_security_view_barrier_array_join_function pins the arrayJoin function form of the ARRAY JOIN carrier: DEFINER and NONE views that expand rows through arrayJoin in the projection keep the invoker's predicate above the expansion on both analyzers and with analyzer_inline_views = 1, while the INVOKER twin discloses the row an empty array hides. 05066_view_inline_additional_table_filters pins that analyzer_inline_views = 1 no longer drops a view-keyed additional_table_filters entry - before the fix the filtered row came back in the result of an INVOKER view. 05067_trivial_view_pushdown_additional_filter_barrier pins that a barrier view whose only row hiding comes from a view-keyed additional_table_filters entry declines the trivial Distributed pushdown, while the same view without the entry still takes it. 05097_sql_security_view_caller_additional_filters pins that a caller's additional_table_filters entry keyed on the inner table of a DEFINER / NONE view is not evaluated inside the view (the throwIf probe on the unexposed column never fires) and that a caller's additional_result_filter does not replace the definer profile's own, on both analyzers and with analyzer_inline_views = 1, while a filter keyed by the view and the caller's result filter on the caller's own query are still honoured. 05098_sql_security_view_barrier_cascades pins the Cascades fence: the plan of a GROUP BY ... ORDER BY over a filtering DEFINER / NONE view is identical with and without enable_cascades_optimizer, while the INVOKER twin is rebuilt. 05101_sql_security_view_barrier_row_policy_filter pins that the row policy filter of a projection-only DEFINER / NONE view keeps its own Filter (Row-level security filter) step on both analyzers while the INVOKER control merges it with the outer WHERE, and that throwIf without short-circuit evaluation never fires on the hidden row. 05102_sql_security_view_barrier_volume_reducing_functions pins the volume-reducing fence: the [volume-reducing functions] marker appears for an INVOKER view whose row policy filter reads the function argument and not for the DEFINER / NONE twins, for length and notEmpty. 05104_sql_security_view_barrier_view_over_mergetree pins the parallel_replicas_allow_view_over_mergetree fence: with query-based parallel replicas and the setting on, the plan of a DEFINER view whose definer has a row policy on the source table and of a filtering NONE view has no parallel-replicas read, while the INVOKER twin still distributes. 05105_sql_security_view_barrier_additional_table_filters pins what an invoker's additional_table_filters predicate does to a barrier view under parallel_replicas_allow_view_over_mergetree = 1, with serialize_query_plan pinned to 0 and to 1: matched by the view's qualified name or by its alias, the query leaves the parallel-replicas path for a DEFINER / NONE view and returns each row once — before the fence every row came back once per replica — while the "no filter" line is the control showing the shortcut is engaged for this shape. The alias-keyed entry is not asserted for the INVOKER twin: dropping it and returning the rows once per replica reproduces for a plain MergeTree table read with a shipped plan too, which has nothing to do with a view. 05213_sql_security_view_barrier_unrelated_filter_view_over_mergetree pins the other side: with an entry keyed to an unrelated table, qualified or by bare name, the DEFINER / NONE views merge the aggregation on the initiator like the INVOKER twin under serialize_query_plan = 1 (MergingAggregated; 0 before the fix), the view-name and alias entries still decline the shortcut, and every row comes back once. 05215_sql_security_view_barrier_user_table_prefixed_filter pins that the internal-alias rule matches exactly __table<digits>: an entry keyed to a user table named __table_prod, bare or qualified, is an unrelated entry and keeps the shortcut for the DEFINER / NONE views (0 for the bare key before the fix), while a __table1 entry still declines it. 05214_sql_security_view_barrier_alias_row_policy pins the Alias half of the row-policy check: a projection-only DEFINER view over an Alias whose SELECT policy is defined on the Alias only plans differently from its INVOKER twin and reads the same number of rows whether or not the hidden row matches the invoker's key predicate, on both planners — before the fix the read_rows oracle printed DISCLOSED on both. 05216_sql_security_view_barrier_lowered_array_join_filter pins the two arrayJoin rewrites: with query_plan_lower_array_join_function = 1, with and without filter fusion, a view-keyed additional_table_filters entry using arrayJoin(tags) keeps the invoker's WHERE in a separate step above it and an outer throwIf on a hidden row never fires for the DEFINER / NONE views on both planners, while the INVOKER twin shows the merge and the exception — before the fix the barrier views behaved like the INVOKER one. 05217_sql_security_view_barrier_alias_target_filter pins the Alias half of the additional_table_filters proof: projection-only DEFINER / NONE views over an Alias table whose SETTINGS clause keys an entry to the target of the Alias return every row and plan exactly like their INVOKER twin on both planners, while the same entry keyed to the Alias itself filters the read and seals the view. 05218_sql_security_view_barrier_merge_pushed_filter pins the pushdown propagation over a Merge table on both planners: the invoker's predicate stays out of the child plan's PREWHERE (the INVOKER twin shows it there), read_rows does not depend on whether a hidden row matches, and an additional_result_filter of the invoker never sees a hidden row. 05106_sql_security_view_barrier_effective_context_settings pins the settings inherited from the definer's profile rather than written in the view's AST, on all three paths: a profile of pure execution tuning still plans exactly like the INVOKER twin, a profile max_rows_to_read with read_overflow_mode = 'break' no longer does, a profile final = 1 plans exactly like the same final in the view's own SETTINGS clause, and the version it hides is never observed. 05103_sql_security_view_barrier_settings_clause pins that a DEFINER / NONE view with only execution settings in its SETTINGS clause plans byte-identically to its INVOKER twin on all three paths and keeps PREWHERE, while views with final, max_rows_to_read or prefer_column_name_to_alias in the clause still differ from their twins and the version hidden by final is never observed. 05107_view_orderby_pushdown_settings_clause pins that the ORDER BY ... LIMIT pushdown into a view over a Distributed table declines a view whose own SETTINGS clause carries additional_result_filter, additional_table_filters, final, a limit with a non-throwing overflow mode or a reset to a default, while a clause of pure execution tuning keeps the pushdown — the guard reuses the very allowlist StorageView::canHideRows applies, so the two cannot drift apart. 05108_view_orderby_pushdown_group_by_all pins the shape guard of the same pushdown: a GROUP BY ALL view body — which leaves the groupBy() list empty and only raises the group_by_all flag — gets no per-shard Top-K on the group keys and keeps the counts of both shards, like its explicit GROUP BY twin and the WITH TOTALS form, while the plain projection view still takes the pushdown; before the fence the GROUP BY ALL plan carried Top-K: limit=3 in both shard Aggregating steps. 05142_sql_security_view_barrier_join_broadcast_side pins the broadcast-side fence of plan-based parallel replicas: for plain LEFT JOIN view and for the RIGHT mirror the plan root of a barrier view stays the local Join, with the Union of the local plan and the remote read on the coordinated side only, while the SQL SECURITY INVOKER control still ships the whole join - before the fence the barrier view shipped as well. 05143_sql_security_view_barrier_prefer_column_name_to_alias pins that the inner query of a DEFINER view is resolved with the view's effective context even where the projection-only path still inlines it: with a definer profile setting prefer_column_name_to_alias = 1, the column leak of SELECT secret AS public, public AS leak FROM t is the source column public on all three analyzer paths, while the INVOKER twin binds it to the alias under the caller's default. 05182_sql_security_view_barrier_join_runtime_filter pins the runtime-filter fence: with query_plan_join_swap_table = true, which puts the view on the probe side, the barrier view's plan carries no Apply runtime join filter step and returns every visible row, while the SQL SECURITY INVOKER twin still gets the filter. 05183_sql_security_view_barrier_limit_range pins the LIMIT n AFTER expr UNTIL expr carrier, which canHideRows now fails closed on like LIMIT / OFFSET: on all three analyzer paths the view exposes only the rows of its range, an invoker expression never runs on a row outside of it, and the view keeps the sealing Convert VIEW subquery result to VIEW table structure step with nothing merged into it, while the projection-only twin keeps the merge and is inlined away with analyzer_inline_views = 1. 05184_sql_security_view_barrier_shape_overflow_settings pins the shape-dependent half of the effective-context proof: a definer profile carrying only max_rows_to_group_by, max_rows_in_distinct or max_rows_to_sort with a non-throwing overflow mode leaves a projection-only DEFINER view planning exactly like its SQL SECURITY INVOKER twin on all three analyzer paths — those limits cannot drop a row of a query that never aggregates, sorts or deduplicates — while the same sort limit over a view that does have an inner ORDER BY still fails closed. 05212_sql_security_view_barrier_shape_overflow_settings_clause pins the same for the view's own SETTINGS clause: the three clause forms leave the projection-only DEFINER view byte-identical to its INVOKER twin on all three analyzer paths, while the sort limit over a sorting view and a max_rows_to_read under read_overflow_mode = 'break' in the clause still fail closed. 05210_sql_security_view_barrier_unrelated_additional_table_filters pins the source-aware half: a definer profile whose additional_table_filters names a table the view never reads (by qualified or by bare name), or the same entry in the view's own SETTINGS clause, leaves a projection-only DEFINER view planning exactly like its SQL SECURITY INVOKER twin on all three analyzer paths, while the same setting naming the view's own source — by qualified name, by bare name under the current database, by the alias the view's query gives it, or in the view's own SETTINGS clause — still fails closed and still hides the rows it names.

Ran 542 existing tests matching view, prewhere, push_down, pushdown, row_policy, sql_security and definer. 60 fail in my local environment, and the identical 60 fail with the barrier disabled on the same binary, so this introduces no regressions among them.

Not covered here

• The serialized-plan fence is defensive. With the serialization change reverted I could not make the barrier loss observable — neither the throwIf nor the failing-cast oracle leaks through serialize_query_plan = 1, make_distributed_plan = 1, or a Distributed table, because the initiator optimizes the fragment before shipping it. The flag is serialized so that the guarantee does not depend on that.

Workflow [PR]
Sync PR [sync-upstream/pr/112847]