cockroachdb / cockroachdb/cockroach
sql: RLS SELECT policies should not filter rows if SET, WHERE, or RETURNING clause does not read existing column values
- 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
Assessment
This issue has not been assessed yet.