column masking policy: add test coverage for Common Table Expression (CTE) scenarios
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Enhancement Summary
The column masking policy feature lacks test coverage for Common Table Expression (CTE) scenarios. While the code implementation should handle CTEs correctly due to recursive plan tree traversal, there are no automated tests to verify this behavior.
## Background
### How Masking Works
Masking is applied **AT RESULT** in the planner layer. The function `extractMaskingPolicyCandidateNamesFromOutputColumn` (in `pkg/planner/core/masking_policy_restrict.go:76-150`) recursively traverses the plan tree to find underlying columns:
```go
walkByIndex = func(plan base.LogicalPlan, colIdx int) {
// For LogicalProjection (which Views use), extract underlying columns
if proj, ok := plan.(*logicalop.LogicalProjection); ok {
for _, sourceCol := range expression.ExtractColumns(proj.Exprs[colIdx]) {
walkByColumn(proj.Children()[0], sourceCol)
}
}
// Continue traversing child plans
for _, child := range plan.Children() {
walkByIndex(child, childColIdx)
}
}
```
This recursive approach should work for CTEs as well since CTEs are also represented as plan nodes in the tree.
### Current Test Coverage
| Scenario | Covered? | Test Case |
|----------|----------|-----------|
| Direct table query | ✅ Yes | `IT-MASK-P0-001` |
| View query | ✅ Yes | `IT-MASK-P1-008 TestColumnMaskPolicyViewIntersection` |
| **CTE query** | ❌ **No** | - |
## Missing Test Scenarios
### 1. Basic CTE with masking
```sql
CREATE TABLE t1(id INT, c VARCHAR(20));
INSERT INTO t1 VALUES (1, 'secret'), (2, 'public');
CREATE MASKING POLICY p_mask ON t1(c)
AS CASE WHEN current_user() = 'root@%' THEN c ELSE MASK_FULL(c, '*') END ENABLE;
-- Should return masked values
WITH cte AS (SELECT c FROM t1) SELECT c FROM cte;
```
### 2. CTE with WHERE clause (verify predicate uses masked values)
```sql
-- Should return 0 rows (predicate compares masked values)
WITH cte AS (SELECT c FROM t1) SELECT COUNT(*) FROM cte WHERE c = 'secret';
```
### 3. CTE with JOIN
```sql
CREATE TABLE t2(id INT, name VARCHAR(20));
INSERT INTO t2 VALUES (1, 'alice');
-- Should apply masking correctly in CTE JOIN scenario
WITH cte AS (SELECT id, c FROM t1)
SELECT t2.name, cte.c FROM t2 JOIN cte ON t2.id = cte.id;
```
### 4. Nested CTE
```sql
WITH cte1 AS (SELECT c FROM t1),
cte2 AS (SELECT c FROM cte1)
SELECT c FROM cte2;
```
### 5. CTE with aggregation (GROUP BY should use raw values per design)
```sql
-- Per design doc: JOIN/WHERE/GROUP BY use raw values
WITH cte AS (SELECT c FROM t1)
SELECT c, COUNT(*) FROM cte GROUP BY c;
```
### 6. CTE with RESTRICT ON operations
```sql
CREATE MASKING POLICY p_restrict ON t1(c)
AS CASE WHEN current_user() = 'root@%' THEN c ELSE MASK_FULL(c, '*') END
RESTRICT ON (INSERT_INTO_SELECT) ENABLE;
CREATE TABLE t3(c VARCHAR(20));
-- Should error with non-root user due to RESTRICT ON
-- Error 8274: Access denied to masked column 'c'
WITH cte AS (SELECT c FROM t1) INSERT INTO t3 SELECT c FROM cte;
```
## Expected Behavior
Based on the implementation:
1. **CTE SELECT**: Masking should apply to results returned from CTE
2. **CTE WHERE/GROUP BY**: Should use raw values (per design doc semantics)
3. **CTE with RESTRICT ON**: Should enforce restrictions correctly
4. **Nested CTE**: Should propagate masking through all levels
## Proposed Test Addition
Add a new test case `IT-MASK-P1-010 TestColumnMaskPolicyCTE` to cover:
- Basic CTE with masking
- CTE with WHERE clause (predicate behavior)
- CTE with JOIN
- Nested CTE
- CTE with aggregation
- CTE with RESTRICT ON operations
## Component
- `component/privilege`
- `sig/planner`
## Related Files
- Test file: `tests/integrationtest/t/privilege/column_masking_policy.test`
- Implementation: `pkg/planner/core/masking_policy_restrict.go`
- Implementation: `pkg/planner/core/logical_plan_builder.go`
- Design doc: `docs/design/2026-02-27-column-level-masking.md`
- Related issue: #67217 (policy name uniqueness)
Contributor guide
Assessment
This issue has not been assessed yet.