cockroachdb / cockroachdb/cockroach
sql: support security_barrier option for views
- 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
Assessment
This issue has not been assessed yet.