From 0bed7a33e1a3911c03eb259fee16c66b5efa6a6c Mon Sep 17 00:00:00 2001 From: Peter Geoghegan Date: Tue, 6 Jan 2015 16:32:21 -0800 Subject: [PATCH] RLS support for ON CONFLICT UPDATE This commit establishes that only insert policies are enforced on post-insert tuples (as before), only update USING/security barrier quals are enforced (as WCOs, with errors thrown) on existing TARGET.* tuples (before ON CONFLICT UPDATE), and post-update tuple only has WCO enforcement (without USING/security barrier qual enforcement). In general, the implementation treats RLS with INSERT ... ON CONFLICT UPDATE as close to regular INSERT/UPDATE behavior as possible. The consistent enforcement of USING/security barrier qualifications as WCOs is the only real deviation. Note to committer to authoritative master branch: This commit is intended to be squashed into the main ON CONFLICT UPDATE commit. It has only been split out for easier review by subject matter experts. --- doc/src/sgml/ref/create_policy.sgml | 63 +++++++---- src/backend/executor/execMain.c | 6 ++ src/backend/executor/nodeModifyTable.c | 30 ++++++ src/backend/rewrite/rowsecurity.c | 55 ++++++++++ src/include/nodes/parsenodes.h | 3 +- src/test/regress/expected/rowsecurity.out | 123 ++++++++++++++++++++++ src/test/regress/sql/rowsecurity.sql | 108 +++++++++++++++++++ 7 files changed, 368 insertions(+), 20 deletions(-) diff --git a/doc/src/sgml/ref/create_policy.sgml b/doc/src/sgml/ref/create_policy.sgml index 49eaadc259..9e8dc430a6 100644 --- a/doc/src/sgml/ref/create_policy.sgml +++ b/doc/src/sgml/ref/create_policy.sgml @@ -78,11 +78,13 @@ CREATE POLICY name ON ON CONFLICT + UPDATE and INSERT policies are not combined in this way, but + rather enforced as noted at each stage of ON CONFLICT execution). + Further, for commands which can have both USING and WITH CHECK policies (ALL + and UPDATE), if no WITH CHECK policy is defined then the USING policy will be + used for both what rows are visible (normal USING case) and which rows will + be allowed to be added (WITH CHECK case). @@ -263,6 +265,12 @@ CREATE POLICY name ON + + Note that INSERT with ON CONFLICT + UPDATE requires that any INSERT policy + WITH CHECK expression passes for any rows appended to the relation by + the INSERT path only. + @@ -271,22 +279,39 @@ CREATE POLICY name ON Using UPDATE for a policy means that it will apply - to UPDATE commands. As UPDATE - involves pulling an existing record and then making changes to some - portion (but possibly not all) of the record, the - UPDATE policy accepts both a USING expression and - a WITH CHECK expression. The USING expression will be used to - determine which records the UPDATE command will - see to operate against, while the WITH CHECK - expression defines what rows are allowed to be added back into the - relation (similar to the INSERT policy). - Any rows whose resulting values do not pass the - WITH CHECK expression will cause an ERROR and the - entire command will be aborted. Note that if only a - USING clause is specified then that clause will be - used for both USING and + to UPDATE commands (or auxiliary ON + CONFLICT UPDATE clauses of INSERT + commands). As UPDATE involves pulling an existing + record and then making changes to some portion (but possibly not all) + of the record, the UPDATE policy accepts both a + USING expression and a WITH CHECK + expression. The USING expression will be used to + determine which records the UPDATE command will see + to operate against, while the WITH CHECK expression + defines what rows are allowed to be added back into the relation + (similar to the INSERT policy). Any rows whose + resulting values do not pass the WITH CHECK + expression will cause an ERROR and the entire command will be aborted. + Note that if only a USING clause is specified then + that clause will be used for both USING and WITH CHECK cases. + + Note, however, that INSERT with ON CONFLICT + UPDATE requires that an UPDATE policy + USING expression always be enforced as a + WITH CHECK expression. This + UPDATE policy must always pass when the + UPDATE path is taken. Any existing row that + necessitates that the UPDATE path be taken must pass + the (UPDATE or ALL) USING qualifications (combined + using OR), which are always enforced as WTIH CHECK + options in this context (the UPDATE path will + never be silently avoided; an error will be thrown + instead). Finally, the final row appended to the relation must pass + any WITH CHECK options that a conventional + UPDATE is required to pass. + diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c index 6563386e64..e18845fad2 100644 --- a/src/backend/executor/execMain.c +++ b/src/backend/executor/execMain.c @@ -1793,6 +1793,12 @@ ExecWithCheckOptions(WCOKind kind, ResultRelInfo *resultRelInfo, errmsg("new row violates row level security policy for \"%s\"", wco->relname))); break; + case WCO_RLS_CONFLICT_CHECK: + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("new row violates row level security policy (USING expression) for \"%s\"", + wco->relname))); + break; default: elog(ERROR, "unrecognized WCO kind: %u", wco->kind); break; diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c index 88b683cf44..7d82d04937 100644 --- a/src/backend/executor/nodeModifyTable.c +++ b/src/backend/executor/nodeModifyTable.c @@ -1184,6 +1184,36 @@ ExecOnConflictUpdate(ResultRelInfo *resultRelInfo, */ slot = EvalPlanQualNext(&onConflict->mt_epqstate); + if (resultRelInfo->ri_WithCheckOptions != NIL) + { + TupleTableSlot *opts; + + /* Construct temp slot for locked tuple from target */ + opts = MakeSingleTupleTableSlot(slot->tts_tupleDescriptor); + ExecStoreTuple(copyTuple, opts, InvalidBuffer, false); + + /* + * Check existing/TARGET.* tuple against UPDATE-applicable + * USING security barrier quals (if any), enforced here as RLS + * checks/WCOs. + * + * The rewriter creates UPDATE RLS checks/WCOs for UPDATE + * security quals, and stores them as WCOs of "kind" + * WCO_RLS_CONFLICT_CHECK, but that's almost the extent of its + * special handling for ON CONFLICT UPDATE. + * + * Because the auxiliary query shares its parent's target RTE + * (and because auxiliary planstate shares its parent's + * resultRelinfo), the rewriter will also have associated + * UPDATE applicable straight RLS checks/WCOs for the benefit + * of ExecUpdate(). INSERTs and UPDATEs naturally have + * mutually exclusive WCO kinds, so there is no danger of + * spurious over-enforcement in the INSERT or UPDATE path. + */ + ExecWithCheckOptions(WCO_RLS_CONFLICT_CHECK, resultRelInfo, + slot, onConflict->ps.state); + } + if (!TupIsNull(slot)) *returning = ExecUpdate(&tuple.t_data->t_ctid, NULL, slot, planSlot, diff --git a/src/backend/rewrite/rowsecurity.c b/src/backend/rewrite/rowsecurity.c index b0b308118f..5fd63ae5d5 100644 --- a/src/backend/rewrite/rowsecurity.c +++ b/src/backend/rewrite/rowsecurity.c @@ -319,6 +319,61 @@ get_row_security_policies(Query* root, RangeTblEntry* rte, int rt_index, wco->cascaded = false; *withCheckOptions = lappend(*withCheckOptions, wco); } + + /* + * ON CONFLICT UPDATE has an RTE that is subject to both INSERT and UPDATE + * RLS enforcement. Security quals on the auxiliary UPDATE seem + * inappropriate, so those are enforced (as a special, distinct kind of + * WCO) on the target tuple. + * + * Make a second, recursive pass over the RTE for this, gathering + * UPDATE-applicable RLS checks/WCOs, and gathering and converting + * UPDATE-applicable security quals into WCO_RLS_CONFLICT_CHECK RLS + * checks/WCOs. Finally, these distinct kinds of RLS checks/WCOs are + * concatenated with our own INSERT-applicable list. + */ + if (root->specClause == SPEC_INSERT) + { + Query *onConflict = (Query *) root->onConflict; + List *conflictSecurityQuals = NIL; + List *conflictWCOs = NIL; + ListCell *item; + bool conflictHasRowSecurity = false; + bool conflictHasSublinks = false; + + /* Assume that RTE is target resultRelation */ + get_row_security_policies(onConflict, rte, + onConflict->resultRelation, + &conflictSecurityQuals, &conflictWCOs, + &conflictHasRowSecurity, + &conflictHasSublinks); + + if (conflictHasRowSecurity) + *hasRowSecurity = true; + if (conflictHasSublinks) + *hasSubLinks = true; + + /* + * Append WITH CHECK OPTIONs/RLS checks, which should not conflict + * between this INSERT and the auxiliary UPDATE + */ + *withCheckOptions = list_concat(*withCheckOptions, + conflictWCOs); + + foreach(item, conflictSecurityQuals) + { + Expr *conflict_rowsec_expr = (Expr *) lfirst(item); + WithCheckOption *wco; + + wco = (WithCheckOption *) makeNode(WithCheckOption); + + wco->kind = WCO_RLS_CONFLICT_CHECK; + wco->relname = pstrdup(RelationGetRelationName(rel)); + wco->qual = (Node *) copyObject(conflict_rowsec_expr); + wco->cascaded = false; + *withCheckOptions = lappend(*withCheckOptions, wco); + } + } } /* For SELECT, UPDATE, and DELETE, set the security quals */ diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index e0ac1e1669..8c2472911a 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -886,7 +886,8 @@ typedef enum WCOKind { WCO_VIEW_CHECK, /* WCO on an auto-updatable view */ WCO_RLS_INSERT_CHECK, /* RLS INSERT WITH CHECK policy */ - WCO_RLS_UPDATE_CHECK /* RLS UPDATE WITH CHECK policy */ + WCO_RLS_UPDATE_CHECK, /* RLS UPDATE WITH CHECK policy */ + WCO_RLS_CONFLICT_CHECK /* RLS ON CONFLICT UPDATE USING policy */ } WCOKind; typedef struct WithCheckOption diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out index 1ea65a7d8a..c45aa6c5d1 100644 --- a/src/test/regress/expected/rowsecurity.out +++ b/src/test/regress/expected/rowsecurity.out @@ -1490,6 +1490,129 @@ SELECT * FROM b1; 4 | yyy (21 rows) +-- +-- INSERT ... ON CONFLICT UPDATE and Row-level security +-- +SET SESSION AUTHORIZATION rls_regress_user0; +DROP POLICY p1 ON document; +CREATE POLICY p1 ON document FOR SELECT USING (true); +CREATE POLICY p2 ON document FOR INSERT WITH CHECK (dauthor = current_user); +CREATE POLICY p3 ON document FOR UPDATE + USING (cid = (SELECT cid from category WHERE cname = 'novel')) + WITH CHECK (dauthor = current_user); +SET SESSION AUTHORIZATION rls_regress_user1; +-- Exists... +SELECT * FROM document WHERE did = 2; + did | cid | dlevel | dauthor | dtitle +-----+-----+--------+-------------------+----------------- + 2 | 11 | 2 | rls_regress_user1 | my second novel +(1 row) + +-- ...so violates actual WITH CHECK OPTION within UPDATE (not INSERT, since +-- alternative UPDATE path happens to be taken): +INSERT INTO document VALUES (2, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user2', 'my first novel') + ON CONFLICT (did) UPDATE SET dtitle = EXCLUDED.dtitle, dauthor = EXCLUDED.dauthor; +ERROR: new row violates row level security policy for "document" +-- Violates USING qual for UPDATE policy p3. +-- +-- UPDATE path is taken, but UPDATE fails purely because *existing* row to be +-- updated is not a "novel"/cid 11 (row is not leaked, even though we have +-- SELECT privileges sufficient to see the row in this instance): +INSERT INTO document VALUES (33, 22, 1, 'rls_regress_user1', 'okay science fiction'); -- preparation for next statement +INSERT INTO document VALUES (33, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user1', 'Some novel, replaces sci-fi') -- takes UPDATE path + ON CONFLICT (did) UPDATE SET dtitle = EXCLUDED.dtitle; +ERROR: new row violates row level security policy (USING expression) for "document" +-- Fine (we UPDATE, since INSERT WCOs and UPDATE security barrier quals + WCOs +-- not violated): +INSERT INTO document VALUES (2, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user1', 'my first novel') + ON CONFLICT (did) UPDATE SET dtitle = EXCLUDED.dtitle RETURNING *; + did | cid | dlevel | dauthor | dtitle +-----+-----+--------+-------------------+---------------- + 2 | 11 | 2 | rls_regress_user1 | my first novel +(1 row) + +-- Fine (we INSERT, so "cid = 33" ("technology") isn't evaluated): +INSERT INTO document VALUES (78, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user1', 'some technology novel') + ON CONFLICT (did) UPDATE SET dtitle = EXCLUDED.dtitle, cid = 33 RETURNING *; + did | cid | dlevel | dauthor | dtitle +-----+-----+--------+-------------------+----------------------- + 78 | 11 | 1 | rls_regress_user1 | some technology novel +(1 row) + +-- Fails (same query, but we UPDATE, so "cid = 33", ("technology") is evaluated +-- at start of UPDATE): +INSERT INTO document VALUES (78, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user1', 'some technology novel') + ON CONFLICT (did) UPDATE SET dtitle = EXCLUDED.dtitle, cid = 33 RETURNING *; +ERROR: new row violates row level security policy (USING expression) for "document" +-- Don't fail just because INSERT doesn't satisfy WITH CHECK option that +-- originated as a barrier/USING() qual from the UPDATE. Note that the UPDATE +-- path *isn't* taken, and so UPDATE-related policy does not apply: +INSERT INTO document VALUES (79, (SELECT cid from category WHERE cname = 'technology'), 1, 'rls_regress_user1', 'technology book, can only insert') + ON CONFLICT (did) UPDATE SET dtitle = EXCLUDED.dtitle RETURNING *; + did | cid | dlevel | dauthor | dtitle +-----+-----+--------+-------------------+---------------------------------- + 79 | 33 | 1 | rls_regress_user1 | technology book, can only insert +(1 row) + +-- But this time, the same statement fails, because the UPDATE path is taken, +-- and updating the row just inserted falls afoul of security barrier qual +-- (enforced as WCO) -- what we might have updated target tuple to is +-- irrelevant, in fact. +INSERT INTO document VALUES (79, (SELECT cid from category WHERE cname = 'technology'), 1, 'rls_regress_user1', 'technology book, can only insert') + ON CONFLICT (did) UPDATE SET dtitle = EXCLUDED.dtitle RETURNING *; +ERROR: new row violates row level security policy (USING expression) for "document" +-- Test default USING qual enforced as WCO +SET SESSION AUTHORIZATION rls_regress_user0; +DROP POLICY p1 ON document; +DROP POLICY p2 ON document; +DROP POLICY p3 ON document; +CREATE POLICY p3_with_default ON document FOR UPDATE + USING (cid = (SELECT cid from category WHERE cname = 'novel')); +SET SESSION AUTHORIZATION rls_regress_user1; +-- Just because WCO-style enforcement of USING quals occurs with +-- existing/target tuple does not mean that the implementation can be allowed +-- to fail to also enforce this qual against the final tuple appended to +-- relation (since in the absence of an explicit WCO, this is also interpreted +-- as an UPDATE/ALL WCO in general). +-- +-- UPDATE path is taken here (fails due to existing tuple). Note that this is +-- not reported as a "USING expression", because it's an RLS UPDATE check that originated as +-- a USING qual for the purposes of RLS in general, as opposed to an explicit +-- USING qual that is ordinarily a security barrier. We leave it up to the +-- UPDATE to make this fail: +INSERT INTO document VALUES (79, (SELECT cid from category WHERE cname = 'technology'), 1, 'rls_regress_user1', 'technology book, can only insert') + ON CONFLICT (did) UPDATE SET dtitle = EXCLUDED.dtitle RETURNING *; +ERROR: new row violates row level security policy for "document" +-- UPDATE path is taken here. Existing tuple passes, since it's cid +-- corresponds to "novel", but default USING qual is enforced against +-- post-UPDATE tuple too (as always when updating with a policy that lacks an +-- explicit WCO), and so this fails: +INSERT INTO document VALUES (2, (SELECT cid from category WHERE cname = 'technology'), 1, 'rls_regress_user1', 'my first novel') + ON CONFLICT (did) UPDATE SET cid = EXCLUDED.cid, dtitle = EXCLUDED.dtitle RETURNING *; +ERROR: new row violates row level security policy for "document" +SET SESSION AUTHORIZATION rls_regress_user0; +DROP POLICY p3_with_default ON document; +-- +-- Test ALL policies with ON CONFLICT UPDATE (much the same as existing UPDATE +-- tests) +-- +CREATE POLICY p3_with_all ON document FOR ALL + USING (cid = (SELECT cid from category WHERE cname = 'novel')) + WITH CHECK (dauthor = current_user); +SET SESSION AUTHORIZATION rls_regress_user1; +-- Fails, since ALL WCO is enforced in insert path: +INSERT INTO document VALUES (80, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user2', 'my first novel') + ON CONFLICT (did) UPDATE SET dtitle = EXCLUDED.dtitle, cid = 33; +ERROR: new row violates row level security policy for "document" +-- Fails, since ALL policy USING qual is enforced (existing, target tuple is in +-- violation, since it has the "manga" cid): +INSERT INTO document VALUES (4, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user1', 'my first novel') + ON CONFLICT (did) UPDATE SET dtitle = EXCLUDED.dtitle; +ERROR: new row violates row level security policy (USING expression) for "document" +-- Fails, since ALL WCO are enforced: +INSERT INTO document VALUES (1, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user1', 'my first novel') + ON CONFLICT (did) UPDATE SET dauthor = 'rls_regress_user2'; +ERROR: new row violates row level security policy for "document" -- -- ROLE/GROUP -- diff --git a/src/test/regress/sql/rowsecurity.sql b/src/test/regress/sql/rowsecurity.sql index f38b4438fd..94d5b8636a 100644 --- a/src/test/regress/sql/rowsecurity.sql +++ b/src/test/regress/sql/rowsecurity.sql @@ -511,6 +511,114 @@ DELETE FROM bv1 WHERE a = 6 AND f_leak(b); SET SESSION AUTHORIZATION rls_regress_user0; SELECT * FROM b1; +-- +-- INSERT ... ON CONFLICT UPDATE and Row-level security +-- + +SET SESSION AUTHORIZATION rls_regress_user0; +DROP POLICY p1 ON document; + +CREATE POLICY p1 ON document FOR SELECT USING (true); +CREATE POLICY p2 ON document FOR INSERT WITH CHECK (dauthor = current_user); +CREATE POLICY p3 ON document FOR UPDATE + USING (cid = (SELECT cid from category WHERE cname = 'novel')) + WITH CHECK (dauthor = current_user); + +SET SESSION AUTHORIZATION rls_regress_user1; + +-- Exists... +SELECT * FROM document WHERE did = 2; + +-- ...so violates actual WITH CHECK OPTION within UPDATE (not INSERT, since +-- alternative UPDATE path happens to be taken): +INSERT INTO document VALUES (2, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user2', 'my first novel') + ON CONFLICT (did) UPDATE SET dtitle = EXCLUDED.dtitle, dauthor = EXCLUDED.dauthor; + +-- Violates USING qual for UPDATE policy p3. +-- +-- UPDATE path is taken, but UPDATE fails purely because *existing* row to be +-- updated is not a "novel"/cid 11 (row is not leaked, even though we have +-- SELECT privileges sufficient to see the row in this instance): +INSERT INTO document VALUES (33, 22, 1, 'rls_regress_user1', 'okay science fiction'); -- preparation for next statement +INSERT INTO document VALUES (33, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user1', 'Some novel, replaces sci-fi') -- takes UPDATE path + ON CONFLICT (did) UPDATE SET dtitle = EXCLUDED.dtitle; +-- Fine (we UPDATE, since INSERT WCOs and UPDATE security barrier quals + WCOs +-- not violated): +INSERT INTO document VALUES (2, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user1', 'my first novel') + ON CONFLICT (did) UPDATE SET dtitle = EXCLUDED.dtitle RETURNING *; +-- Fine (we INSERT, so "cid = 33" ("technology") isn't evaluated): +INSERT INTO document VALUES (78, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user1', 'some technology novel') + ON CONFLICT (did) UPDATE SET dtitle = EXCLUDED.dtitle, cid = 33 RETURNING *; +-- Fails (same query, but we UPDATE, so "cid = 33", ("technology") is evaluated +-- at start of UPDATE): +INSERT INTO document VALUES (78, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user1', 'some technology novel') + ON CONFLICT (did) UPDATE SET dtitle = EXCLUDED.dtitle, cid = 33 RETURNING *; +-- Don't fail just because INSERT doesn't satisfy WITH CHECK option that +-- originated as a barrier/USING() qual from the UPDATE. Note that the UPDATE +-- path *isn't* taken, and so UPDATE-related policy does not apply: +INSERT INTO document VALUES (79, (SELECT cid from category WHERE cname = 'technology'), 1, 'rls_regress_user1', 'technology book, can only insert') + ON CONFLICT (did) UPDATE SET dtitle = EXCLUDED.dtitle RETURNING *; +-- But this time, the same statement fails, because the UPDATE path is taken, +-- and updating the row just inserted falls afoul of security barrier qual +-- (enforced as WCO) -- what we might have updated target tuple to is +-- irrelevant, in fact. +INSERT INTO document VALUES (79, (SELECT cid from category WHERE cname = 'technology'), 1, 'rls_regress_user1', 'technology book, can only insert') + ON CONFLICT (did) UPDATE SET dtitle = EXCLUDED.dtitle RETURNING *; + +-- Test default USING qual enforced as WCO +SET SESSION AUTHORIZATION rls_regress_user0; +DROP POLICY p1 ON document; +DROP POLICY p2 ON document; +DROP POLICY p3 ON document; + +CREATE POLICY p3_with_default ON document FOR UPDATE + USING (cid = (SELECT cid from category WHERE cname = 'novel')); + +SET SESSION AUTHORIZATION rls_regress_user1; +-- Just because WCO-style enforcement of USING quals occurs with +-- existing/target tuple does not mean that the implementation can be allowed +-- to fail to also enforce this qual against the final tuple appended to +-- relation (since in the absence of an explicit WCO, this is also interpreted +-- as an UPDATE/ALL WCO in general). +-- +-- UPDATE path is taken here (fails due to existing tuple). Note that this is +-- not reported as a "USING expression", because it's an RLS UPDATE check that originated as +-- a USING qual for the purposes of RLS in general, as opposed to an explicit +-- USING qual that is ordinarily a security barrier. We leave it up to the +-- UPDATE to make this fail: +INSERT INTO document VALUES (79, (SELECT cid from category WHERE cname = 'technology'), 1, 'rls_regress_user1', 'technology book, can only insert') + ON CONFLICT (did) UPDATE SET dtitle = EXCLUDED.dtitle RETURNING *; + +-- UPDATE path is taken here. Existing tuple passes, since it's cid +-- corresponds to "novel", but default USING qual is enforced against +-- post-UPDATE tuple too (as always when updating with a policy that lacks an +-- explicit WCO), and so this fails: +INSERT INTO document VALUES (2, (SELECT cid from category WHERE cname = 'technology'), 1, 'rls_regress_user1', 'my first novel') + ON CONFLICT (did) UPDATE SET cid = EXCLUDED.cid, dtitle = EXCLUDED.dtitle RETURNING *; + +SET SESSION AUTHORIZATION rls_regress_user0; +DROP POLICY p3_with_default ON document; + +-- +-- Test ALL policies with ON CONFLICT UPDATE (much the same as existing UPDATE +-- tests) +-- +CREATE POLICY p3_with_all ON document FOR ALL + USING (cid = (SELECT cid from category WHERE cname = 'novel')) + WITH CHECK (dauthor = current_user); + +SET SESSION AUTHORIZATION rls_regress_user1; + +-- Fails, since ALL WCO is enforced in insert path: +INSERT INTO document VALUES (80, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user2', 'my first novel') + ON CONFLICT (did) UPDATE SET dtitle = EXCLUDED.dtitle, cid = 33; +-- Fails, since ALL policy USING qual is enforced (existing, target tuple is in +-- violation, since it has the "manga" cid): +INSERT INTO document VALUES (4, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user1', 'my first novel') + ON CONFLICT (did) UPDATE SET dtitle = EXCLUDED.dtitle; +-- Fails, since ALL WCO are enforced: +INSERT INTO document VALUES (1, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user1', 'my first novel') + ON CONFLICT (did) UPDATE SET dauthor = 'rls_regress_user2'; -- -- ROLE/GROUP -- 2.39.5