723 -- Violates USING qual for UPDATE policy p3.
724 --
725 -- UPDATE path is taken, but UPDATE fails purely because *existing* row to be
726 -- updated is not a "novel"/cid 11 (row is not leaked, even though we have
727 -- SELECT privileges sufficient to see the row in this instance):
728 INSERT INTO document VALUES (33, 22, 1, 'regress_rls_bob', 'okay science fiction'); -- preparation for next statement
729 INSERT INTO document VALUES (33, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_bob', 'Some novel, replaces sci-fi') -- takes UPDATE path
730 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle;
731 -- Fine (we UPDATE, since INSERT WCOs and UPDATE security barrier quals + WCOs
732 -- not violated):
733 INSERT INTO document VALUES (2, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_bob', 'my first novel')
734 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle RETURNING *;
POLICY "p3" FOR UPDATE
USING ((cid = ( SELECT category.cid
FROM category
WHERE (category.cname = 'novel'::text))))
WITH CHECK ((dauthor = CURRENT_USER))
row level security p3, if I understand correctly: the dauthor shall be currernt_user
and updated cid value shall not be 11
The proposed record must have a dauthor value equal to current_user. That is the only restriction.
However, the only rows eligible for updating are novels (cid = 11).
With this combination it is possible for an author to update existing novels authored by someone else to instead be authored by themself.
It is not possible for them to update non-novels authored by themself, or anyone else.
If it is possible to perform the update the effect of the update must result in the dauthor value being made equal to current_user or the update will fail.
Line 729 to 730 fails. That makes sense to me. but I don't get the line 733 to 734.
Also in the comment section, what does `quals + WCOs` mean?
WCO := With Check Option clause(the word option is implicit in the actual clause name)
+ := And
Security Barrier Quals := Using clause
It is basically saying: "both RLS restriction types".
What don't you get about 733/734?
Inserting a row 2 (with valid dauthor value), row 2 exists so move to on conflict update, row 2 is visible to the update because its cid is 11, perform update since the new row will have a valid dauthor value.
David J.