cockroachdb / cockroachdb/cockroach

sql: RLS SELECT policies should not filter rows if SET, WHERE, or RETURNING clause does not read existing column values

Open
#145,894 3 comments 0 reactions 0 assignees View on GitHub
A-sql-privileges branch-release-25.2 C-bug docs-known-limitation O-qa T-sql-foundations
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

Simple example:

```sql
DROP TABLE IF EXISTS t;
DROP ROLE IF EXISTS alice;

CREATE ROLE alice;

CREATE TABLE t (
k INT PRIMARY KEY,
a INT
);

INSERT INTO t VALUES (1, 0);

GRANT SELECT, INSERT, UPDATE, DELETE ON t TO alice;

ALTER TABLE t ENABLE ROW LEVEL SECURITY;

CREATE POLICY select_policy_alice
ON t
FOR SELECT
TO alice
USING (a > 0);

CREATE POLICY update_policy_alice
ON t
FOR UPDATE
TO alice
USING (true);

SET ROLE alice;

-- a appears in the SET clause but it denotes a new value to write, and it does
-- not read the existing value.
UPDATE t SET a = -1 WHERE true;
-- In Postgres, this update succeeds:
-- UPDATE 1
--
-- In CRDB with #144943, the update affects no rows.
-- UPDATE 0

SET ROLE demo;
-- SET ROLE marcus;

SELECT * FROM t;
-- Postgres:
-- k | a
-- ---+----
-- 1 | -1
-- (1 row)
--
-- CRDB:
-- k | a
-- ---+----
-- 1 | 0
-- (1 row)
```

Example of with `SET` clause and implicit join:

```sql
DROP TABLE IF EXISTS t;
DROP TABLE IF EXISTS t2;
DROP ROLE IF EXISTS alice;

CREATE ROLE alice;

CREATE TABLE t (
k INT PRIMARY KEY,
a INT
);

INSERT INTO t VALUES (1, 0);

GRANT SELECT, INSERT, UPDATE, DELETE ON t TO alice;

ALTER TABLE t ENABLE ROW LEVEL SECURITY;

CREATE POLICY select_policy_alice
ON t
FOR SELECT
TO alice
USING (a > 0);

CREATE POLICY update_policy_alice
ON t
FOR UPDATE
TO alice
USING (true);

CREATE TABLE t2 (k INT PRIMARY KEY, a INT);

INSERT INTO t2 VALUES (1, -1);

GRANT SELECT, INSERT, UPDATE, DELETE ON t2 TO alice;

SET ROLE alice;

UPDATE t SET a = t2.a FROM t2 WHERE true;
-- In Postgres, this update succeeds:
-- UPDATE 1
--
-- In CRDB with #144943, the update affects no rows.
-- UPDATE 0

-- SET ROLE demo;
SET ROLE marcus;

SELECT * FROM t;
-- Postgres:
-- k | a
-- ---+----
-- 1 | -1
-- (1 row)
--
-- CRDB:
-- k | a
-- ---+----
-- 1 | 0
-- (1 row)
```

Example with with `WHERE` clause and implicit join:

```sql
DROP TABLE IF EXISTS t;
DROP TABLE IF EXISTS t2;
DROP ROLE IF EXISTS alice;

CREATE ROLE alice;

CREATE TABLE t (
k INT PRIMARY KEY,
a INT
);

INSERT INTO t VALUES (1, 0);

GRANT SELECT, INSERT, UPDATE, DELETE ON t TO alice;

ALTER TABLE t ENABLE ROW LEVEL SECURITY;

CREATE POLICY select_policy_alice
ON t
FOR SELECT
TO alice
USING (a > 0);

CREATE POLICY update_policy_alice
ON t
FOR UPDATE
TO alice
USING (true);

CREATE TABLE t2 (k INT PRIMARY KEY, a INT);

INSERT INTO t2 VALUES (1, -1);

GRANT SELECT, INSERT, UPDATE, DELETE ON t2 TO alice;

SET ROLE alice;

UPDATE t SET a = -2 FROM t2 WHERE t2.k = 1;
-- In Postgres, this update succeeds:
-- UPDATE 1
--
-- In CRDB with #144943, the update affects no rows.
-- UPDATE 0

SET ROLE demo;
-- SET ROLE marcus;

SELECT * FROM t;
-- Postgres:
-- k | a
-- ---+----
-- 1 | -2
-- (1 row)
--
-- CRDB:
-- k | a
-- ---+----
-- 1 | 0
-- (1 row)
```

Jira issue: CRDB-50273

Epic CRDB-52152

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.