[Bug] CREATE ROW POLICY with correlated EXISTS subquery silently succeeds but policy is never enforced

[ngbinh]

Labels: kind/bug-fix area/nereids area/auth

---

Version

• Doris version: 4.1.0-rc03, compute-storage decoupled (cloud) mode
• Also likely affects: 4.0.4 and later (since PR [clean](planner)row policy removes dependency on the old optimizer #51122 migrated CREATE ROW POLICY to Nereids)

---

Description

When CREATE ROW POLICY is issued with a correlated EXISTS subquery in the USING clause, the statement returns no error and appears to succeed. However, the policy is silently discarded — it is never stored, and at query time the row filter is not applied. The result is that a security boundary that the operator believes is in place does not exist.

Simple predicates (col = value) and uncorrelated subqueries work correctly. Only correlated subqueries — where the inner SELECT references a column of the outer table — are silently swallowed.

---

Steps to Reproduce

-- Setup
CREATE DATABASE IF NOT EXISTS test_db;

CREATE TABLE test_db.main_table (
    id      INT          NOT NULL,
    ref_id  INT,
    owner   VARCHAR(100)
) UNIQUE KEY (id)
DISTRIBUTED BY HASH(id) BUCKETS 1;

CREATE TABLE test_db.lookup_table (
    ref_id       INT          NOT NULL,
    allowed_user VARCHAR(100) NOT NULL
) UNIQUE KEY (ref_id, allowed_user)
DISTRIBUTED BY HASH(ref_id) BUCKETS 1;

INSERT INTO test_db.main_table VALUES
    (1, 10, 'alice'), (2, 20, 'bob'), (3, 10, 'carol'), (4, 30, 'dave');

INSERT INTO test_db.lookup_table VALUES
    (10, 'alice'), (20, 'bob'), (30, 'carol');

CREATE USER 'test_reader' IDENTIFIED BY 'test123';
GRANT SELECT_PRIV ON test_db TO 'test_reader';

-- Control: a simple predicate policy works as expected
CREATE ROW POLICY rp_simple ON test_db.main_table
    AS RESTRICTIVE TO test_reader
    USING (owner = 'alice');
-- Verify: SELECT as test_reader returns only row 1.
DROP ROW POLICY rp_simple ON test_db.main_table;

-- Bug: correlated EXISTS
CREATE ROW POLICY rp_correlated ON test_db.main_table
    AS RESTRICTIVE TO test_reader
    USING (
        EXISTS (
            SELECT 1 FROM test_db.lookup_table l
            WHERE l.allowed_user = 'alice'
              AND l.ref_id = test_db.main_table.ref_id  -- outer reference
        )
    );
-- No error is returned.

-- Proof the policy was never stored: creating it again succeeds instead of
-- failing with "already exists".
CREATE ROW POLICY rp_correlated ON test_db.main_table
    AS RESTRICTIVE TO test_reader
    USING (id = 1);
-- Expected: ERROR (duplicate policy name)
-- Actual:   SUCCESS — confirms the first CREATE was silently dropped

---

Expected Behavior

One of the following should happen:

1. Correct enforcement: The correlated EXISTS subquery is resolved at query time and rows are filtered accordingly.
2. Explicit rejection: CREATE ROW POLICY returns a clear error such as:

   Correlated subquery expressions in the USING clause are not supported for row policies.

Under no circumstances should a CREATE ROW POLICY statement silently succeed while actually storing nothing.

---

Actual Behavior

• CREATE ROW POLICY ... USING (EXISTS (...correlated...)) returns success with no warning.
• The policy is not stored (confirmed by the ability to re-create a policy with the same name).
• Queries run as test_reader return all rows — the filter is never applied.

---

Test Matrix

USING predicate pattern	Enforced?
col = value	Yes
EXISTS (uncorrelated subquery)	Yes
col IN (SELECT ...) — no outer reference	Yes
col IN (SELECT ... JOIN ...) — no outer reference	Yes
EXISTS (SELECT ... WHERE inner.col = outer_table.col) — correlated	No — silent failure

---

Root Cause Analysis

The failure is the result of two independent changes interacting badly.

1. Exists implements LeafExpression (structural issue)

Exists.java implements the LeafExpression interface, which declares zero expression children. The inner LogicalPlan (the subquery body) is stored as a plain Java field, not as a member of the expression tree's children() list.

As a result, any visitor or iterator that walks an expression tree via .foreach() / .children() does not descend into the subquery body of an Exists node.

2. CreatePolicyCommand.validate() uses .foreach() to inspect the predicate (PR #55409)

CreatePolicyCommand.java — introduced in the Nereids migration of CREATE ROW POLICY (PR #51122) — contains a validate() method that walks the wherePredicate expression tree to check that all referenced columns exist on the target table. It uses the standard .foreach() traversal.

Because Exists is a LeafExpression, .foreach() never visits the UnboundSlot nodes inside the subquery. From the validator's perspective, the predicate references no columns at all, so validation passes silently. The Exists node and its unresolved inner plan are accepted without error.

3. Policy injection at query time fails silently

When CheckPolicy injects the stored Expression into a LogicalFilter at query planning time, the Exists node carries an UnboundRelation / UnboundSlot subquery plan that was serialized without the outer table's resolution context. The Nereids analyzer cannot bind the outer reference (test_db.main_table.ref_id) because the table is no longer in scope at injection time. Rather than raising a planning error, the policy is skipped or the filter resolves to a no-op.

Note: RowPolicy.gsonPostProcess() re-parses the stored expression string via NereidsParser, which creates a fresh unbound plan — this means the subquery must be re-analyzed on every query, making the missing outer context a recurring failure point.

Affected source files

File	Relevance
fe/src/main/java/org/apache/doris/nereids/trees/expressions/Exists.java	Implements LeafExpression; subquery plan not in children
fe/src/main/java/org/apache/doris/nereids/trees/plans/commands/CreatePolicyCommand.java	validate() uses .foreach(); misses inner slots
fe/src/main/java/org/apache/doris/catalog/constraint/CheckPolicy.java	Injects policy Expression into LogicalFilter at query time
fe/src/main/java/org/apache/doris/catalog/RowPolicy.java	gsonPostProcess() re-parses expression on load

---

Impact

This is a security issue.

Row-level security policies that administrators believe are active are in fact not stored or enforced. Users who should be restricted to a filtered view of a table can read all rows. There is no log message, no error, and no indication that the policy was discarded. The operator has no way to detect this condition from the output of CREATE ROW POLICY alone.

---

Possible Fixes

Option A — Reject correlated subqueries at DDL time (safer, lower risk)

In CreatePolicyCommand.validate(), after the existing column check, add an explicit walk that detects any SubqueryExpr (or Exists / InSubquery) node whose inner plan references slots from the outer relation. Return a user-facing error if found.

Option B — Support correlated EXISTS correctly

Change Exists (and similar subquery expressions) so that the inner LogicalPlan is exposed as a proper expression child, or ensure that CreatePolicyCommand serializes a fully-bound (post-analysis) expression rather than the raw parse tree. The policy must carry enough context for CheckPolicy to re-resolve the outer reference at query time.

Option A is the safer short-term fix; Option B is the correct long-term solution.