cockroachdb / cockroachdb/cockroach

sql: support security_barrier option for views

Open
#162,585 0 comments 0 reactions 0 assignees View on GitHub
A-sql-pgcompat C-enhancement T-sql-queries
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

## Summary

CockroachDB does not support the `security_barrier` view option from PostgreSQL. Attempting to use `WITH (security_barrier)` in a `CREATE VIEW` statement results in a syntax error.

## PostgreSQL Behavior

In PostgreSQL, `security_barrier` is a boolean view option that provides row-level security by controlling the order in which `WHERE` conditions are evaluated:

```sql
CREATE VIEW phone_number WITH (security_barrier) AS
SELECT person, phone FROM phone_data WHERE phone NOT LIKE '412%';
```

When `security_barrier` is set:
- The view's `WHERE` conditions are always evaluated **before** any user-supplied conditions.
- Only functions marked `LEAKPROOF` may be pushed past the view's security quals.
- This prevents user-defined functions with side effects (e.g., `RAISE NOTICE`) from leaking filtered-out rows.

Without it, the optimizer may reorder predicates and push cheap user-defined functions before the view's filter, allowing an attacker to observe rows that should be invisible.

Reference: https://www.postgresql.org/docs/current/sql-createview.html, https://www.postgresql.org/docs/current/rules-privileges.html

## CockroachDB Current State

- The parser does not recognize `security_barrier` as a token.
- The `ViewOptions` AST struct (`pkg/sql/sem/tree/create.go`) only contains `SecurityInvoker`; there is no `SecurityBarrier` field.
- Using `WITH (security_barrier)` produces a syntax error.
- Note from an [internal thread](https://cockroachlabs.slack.com/archives/C083W9NK34H/p1747256835678479?thread_ts=1747248865.895809&cid=C083W9NK34H): we already have a way to add optimization barriers within the optimizer. To use those, we'd have to make sure the barriers we already have cover all the cases we need: https://github.com/cockroachdb/cockroach/blob/ca3b6ab037c2f10725c49ae14a6271bf8118bfda/pkg/sql/opt/ops/relational.opt#L1532-L1538

## Requested Behavior

Support the `WITH (security_barrier)` option on `CREATE VIEW`, matching PostgreSQL semantics:
1. Parse and store the `security_barrier` boolean option.
2. During query optimization, prevent non-`LEAKPROOF` user-supplied predicates from being pushed below the view's qualifying conditions.

Jira issue: CRDB-59541

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.