row level security on conflict do update - Mailing list pgsql-general
| From | alias |
|---|---|
| Subject | row level security on conflict do update |
| Date | |
| Msg-id | CAJA4AWSmZH9rPvCKeYNTpJo=j7cr9pCtXc20s2yvtgMzgmJbLg@mail.gmail.com Whole thread Raw |
| Responses |
Re: row level security on conflict do update
("David G. Johnston" <david.g.johnston@gmail.com>)
|
| List | pgsql-general |
58 CREATE TABLE category (
59 cid int primary key,
60 cname text
61 );
62 GRANT ALL ON category TO public;
63 INSERT INTO category VALUES
64 (11, 'novel'),
65 (22, 'science fiction'),
66 (33, 'technology'),
67 (44, 'manga');
68
69 CREATE TABLE document (
70 did int primary key,
71 cid int references category(cid),
72 dlevel int not null,
73 dauthor name,
74 dtitle text
75 );
76 GRANT ALL ON document TO public;
77 INSERT INTO document VALUES
78 ( 1, 11, 1, 'regress_rls_bob', 'my first novel'),
79 ( 2, 11, 2, 'regress_rls_bob', 'my second novel'),
80 ( 3, 22, 2, 'regress_rls_bob', 'my science fiction'),
81 ( 4, 44, 1, 'regress_rls_bob', 'my first manga'),
82 ( 5, 44, 2, 'regress_rls_bob', 'my second manga'),
83 ( 6, 22, 1, 'regress_rls_carol', 'great science fiction'),
84 ( 7, 33, 2, 'regress_rls_carol', 'great technology book'),
85 ( 8, 44, 1, 'regress_rls_carol', 'great manga'),
86 ( 9, 22, 1, 'regress_rls_dave', 'awesome science fiction'),
87 (10, 33, 2, 'regress_rls_dave', 'awesome technology book');
88
89 ALTER TABLE document ENABLE ROW LEVEL SECURITY;
707 CREATE POLICY p1 ON document FOR SELECT USING (true);
708 CREATE POLICY p2 ON document FOR INSERT WITH CHECK (dauthor = current_user);
709 CREATE POLICY p3 ON document FOR UPDATE
710 USING (cid = (SELECT cid from category WHERE cname = 'novel'))
711 WITH CHECK (dauthor = current_user);
712
713 SET SESSION AUTHORIZATION regress_rls_bob;
714
715 -- Exists...
716 SELECT * FROM document WHERE did = 2;
717
718 -- ...so violates actual WITH CHECK OPTION within UPDATE (not INSERT, since
719 -- alternative UPDATE path happens to be taken):
720 INSERT INTO document VALUES (2, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_carol', 'my first novel')
721 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle, dauthor = EXCLUDED.dauthor;
722
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 *;
SELECT cid from category WHERE cname = 'novel' == 11
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
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?
pgsql-general by date: