On Mon, 27 Oct 2025 at 05:03, jian he <[email protected]> wrote: > > v2-0001 looks good to me.
Thanks. I've pushed that one. > > A recent commit reminded me that COPY ... TO also applies RLS SELECT > > policies (and so does TABLE, though I doubt many people use that), so > > I think it's worth testing and documenting those too. Updated patches > > attached. > > other Utility commands will also invoke the SELECT/UPDATE policy. > The below several commands will invoke SELECT or UPDATE policy, > if rls_test_src have SELECT or UPDATE policy on it. I don't think it's worth documenting every single command that includes a SELECT somewhere in it. Adding too much to the docs makes them harder to read, not easier, and I think it's pretty clear that these examples end up executing a SELECT, and so it should be clear that they apply the RLS SELECT/UPDATE policies. In fact, on reflection, I don't think it's worth mentioning TABLE here either, since it's not really a separate command. It doesn't have its own doc page, but instead is only mentioned on the SELECT doc page, which says that it's equivalent to SELECT * FROM table, so it should be clear that it applies SELECT policies. So I think I'll stick to just mentioning COPY .. TO, since it might not otherwise be obvious that it does apply RLS SELECT policies. > While at it, I found out that > table "Policies Applied by Command Type" was missing SELECT FOR NO KEY UPDATE > and SELECT FOR KEY SHARE. I don't think that's necessary. We could try to say something like "SELECT ... FOR [NO KEY] UPDATE / [KEY] SHARE", but I think that would make it harder to read, given the lack of space in that table. Several other places in the documentation already use the text "FOR UPDATE/SHARE" to include all 4 variants of SELECT row locking, including the SELECT doc page itself, so I think it should be sufficient here too. > While at it create_policy.sgml, I am not sure the below sentence is > not fully accurate. > "" > If an INSERT or UPDATE command attempts to add rows to the table that do not > pass the ALL policy's WITH CHECK expression, the entire command will be > aborted. > "" > The above sentence fails to mention the case when the WITH CHECK > expression does not exist. Hmm, the sentence immediately before that explains that the USING expression will be used to check new rows, if there is no WITH CHECK expression, but that's using UPDATE as an example, so I guess it's worth being clear that the same applies to an INSERT. Updated patch attached. Regards, Dean
From 8b5c0e3db182809c21694f313333f260f2a19258 Mon Sep 17 00:00:00 2001 From: Dean Rasheed <[email protected]> Date: Mon, 27 Oct 2025 11:09:35 +0000 Subject: [PATCH v3] doc: Improve the "Policies Applied by Command Type" table. This table was missing MERGE ... THEN DELETE and some of the policies applied during INSERT ... ON CONFLICT and MERGE. Fix that, and try to improve readability by listing the various MERGE cases separately, rather than together with INSERT/UPDATE/DELETE. In addition, mention COPY ... TO along with SELECT, since it behaves in the same way. In addition, correct the paragraph above the table to note that an INSERT ... ON CONFLICT DO NOTHING/UPDATE checks all new rows against the INSERT policy expressions, regardless of whether the rows end up being inserted. Also, in the description of ALL policies, clarify that an ALL policy applied to an INSERT will use the policy's USING expression to check new rows, if it lacks a WITH CHECK expression. Author: Dean Rasheed <[email protected]> Reviewed-by: Viktor Holmberg <[email protected]> Reviewed-by: Jian He <[email protected]> Discussion: https://postgr.es/m/CAEZATCWqnfeChjK=n1v_dyzt4rt4mnq+ybf9c0qxdytvmsy...@mail.gmail.com --- doc/src/sgml/ref/create_policy.sgml | 76 +++++++++++++++++++++++------ 1 file changed, 61 insertions(+), 15 deletions(-) diff --git a/doc/src/sgml/ref/create_policy.sgml b/doc/src/sgml/ref/create_policy.sgml index e76c342d3da..13adb5ecae8 100644 --- a/doc/src/sgml/ref/create_policy.sgml +++ b/doc/src/sgml/ref/create_policy.sgml @@ -251,7 +251,9 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable otherwise). If an <command>INSERT</command> or <command>UPDATE</command> command attempts to add rows to the table that do not pass the <literal>ALL</literal> - policy's <literal>WITH CHECK</literal> expression, the entire + policy's <literal>WITH CHECK</literal> expression (or its + <literal>USING</literal> expression, if it does not have a + <literal>WITH CHECK</literal> expression), the entire command will be aborted. </para> </listitem> @@ -293,9 +295,9 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable </para> <para> Note that <literal>INSERT</literal> with <literal>ON CONFLICT DO - UPDATE</literal> checks <literal>INSERT</literal> policies' - <literal>WITH CHECK</literal> expressions only for rows appended - to the relation by the <literal>INSERT</literal> path. + NOTHING/UPDATE</literal> checks <literal>INSERT</literal> policies' + <literal>WITH CHECK</literal> expressions for all rows, regardless + of whether they end up being inserted. </para> </listitem> </varlistentry> @@ -424,7 +426,7 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable </thead> <tbody> <row> - <entry><command>SELECT</command></entry> + <entry><command>SELECT</command> / <command>COPY ... TO</command></entry> <entry>Existing row</entry> <entry>—</entry> <entry>—</entry> @@ -440,15 +442,7 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable <entry>—</entry> </row> <row> - <entry><command>INSERT</command> / <command>MERGE ... THEN INSERT</command></entry> - <entry>—</entry> - <entry>New row</entry> - <entry>—</entry> - <entry>—</entry> - <entry>—</entry> - </row> - <row> - <entry><command>INSERT ... RETURNING</command></entry> + <entry><command>INSERT</command></entry> <entry> New row <footnote id="rls-select-priv"> <para> @@ -464,7 +458,7 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable <entry>—</entry> </row> <row> - <entry><command>UPDATE</command> / <command>MERGE ... THEN UPDATE</command></entry> + <entry><command>UPDATE</command></entry> <entry> Existing & new rows <footnoteref linkend="rls-select-priv"/> </entry> @@ -483,14 +477,66 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable <entry>—</entry> <entry>Existing row</entry> </row> + <row> + <entry><command>INSERT ... ON CONFLICT</command></entry> + <entry>New row</entry> + <entry>New row</entry> + <entry>—</entry> + <entry>—</entry> + <entry>—</entry> + </row> <row> <entry><command>ON CONFLICT DO UPDATE</command></entry> + <entry> + Existing & new rows <footnote id="rls-on-conflict-update-priv"> + <para> + New row of the auxiliary <command>UPDATE</command> command, which + might be different from the new row of the original + <command>INSERT</command> command. + </para> + </footnote> + </entry> + <entry>—</entry> + <entry>Existing row</entry> + <entry> + New row <footnoteref linkend="rls-on-conflict-update-priv"/> + </entry> + <entry>—</entry> + </row> + <row> + <entry><command>MERGE</command></entry> + <entry>All candidate source & target rows</entry> + <entry>—</entry> + <entry>—</entry> + <entry>—</entry> + <entry>—</entry> + </row> + <row> + <entry><command>MERGE ... THEN INSERT</command></entry> + <entry> + New row <footnoteref linkend="rls-select-priv"/> + </entry> + <entry>New row</entry> + <entry>—</entry> + <entry>—</entry> + <entry>—</entry> + </row> + <row> + <entry><command>MERGE ... THEN UPDATE</command></entry> <entry>Existing & new rows</entry> <entry>—</entry> <entry>Existing row</entry> <entry>New row</entry> <entry>—</entry> </row> + <row> + <entry><command>MERGE ... THEN DELETE</command></entry> + <entry>Existing row</entry> + <entry>—</entry> + <entry>—</entry> + <entry>—</entry> + <entry>Existing row</entry> + </row> </tbody> </tgroup> </table> -- 2.51.0
