From: | Rod Taylor <rod(dot)taylor(at)gmail(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org, Stephen Frost <sfrost(at)snowman(dot)net> |
Subject: | Row Level Security UPDATE Confusion |
Date: | 2017-04-06 20:05:33 |
Message-ID: | CAHz80e7Pd4FgDmR=UUDij+oCSf92BKkosRV=12Ra7xMrwQ11YA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
I'm a little confused on why a SELECT policy fires against the NEW record
for an UPDATE when using multiple FOR policies. The ALL policy doesn't seem
to have that restriction.
DROP TABLE IF EXISTS t;
CREATE USER simple;
CREATE USER split;
CREATE TABLE t(value int);
grant select, update on table t to simple, split;
INSERT INTO t values (1), (2);
ALTER TABLE t ENABLE ROW LEVEL SECURITY;
CREATE POLICY simple_all ON t TO simple USING (value > 0) WITH CHECK (true);
CREATE POLICY split_select ON t FOR SELECT TO split USING (value > 0);
CREATE POLICY split_update ON t FOR UPDATE TO split USING (true) WITH CHECK
(true);
SET session authorization simple;
SELECT * FROM t;
UPDATE t SET value = value * -1 WHERE value = 1;
SET session authorization split;
SELECT * FROM t;
UPDATE t SET value = value * -1 WHERE value = 2;
/* UPDATE fails with below error:
psql:/tmp/t.sql:24: ERROR: 42501: new row violates row-level security
policy for table "t"
LOCATION: ExecWithCheckOptions, execMain.c:2045
*/
SET session authorization default;
SELECT * FROM t;
This seems consistent in both Pg 9.5 and upcoming Pg 10.
--
Rod Taylor
From | Date | Subject | |
---|---|---|---|
Next Message | Álvaro Hernández Tortosa | 2017-04-06 20:15:43 | Re: Letting the client choose the protocol to use during a SASL exchange |
Previous Message | Tom Lane | 2017-04-06 20:05:32 | Re: Letting the client choose the protocol to use during a SASL exchange |