Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wkAcM-000Vm8-0M for pgsql-hackers@arkaria.postgresql.org; Thu, 16 Jul 2026 01:10:26 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1wkAcJ-00AXu9-25 for pgsql-hackers@arkaria.postgresql.org; Thu, 16 Jul 2026 01:10:23 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wkAcJ-00AXu1-0M for pgsql-hackers@lists.postgresql.org; Thu, 16 Jul 2026 01:10:23 +0000 Received: from mail-pg1-x52a.google.com ([2607:f8b0:4864:20::52a]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1wkAcF-00000000Qvq-45BU for pgsql-hackers@postgresql.org; Thu, 16 Jul 2026 01:10:22 +0000 Received: by mail-pg1-x52a.google.com with SMTP id 41be03b00d2f7-ca766c1c9ccso3526830a12.0 for ; Wed, 15 Jul 2026 18:10:20 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20251104; t=1784164218; x=1784769018; darn=postgresql.org; h=references:to:cc:in-reply-to:date:subject:mime-version:content-type :message-id:from:from:to:cc:subject:date:message-id:reply-to :content-type; bh=cKDssjwA/oc1+Y1Sw+89U4jD+XOvDsd1z7Xcr20Tb9Q=; b=mlJNajPCEMiCBYsoN/1+eQosOL6J0J5ZYqkXDN/4jTP5tk9j7UMyyl5v2X4dQq/Afr 1p58wm4gZReXxTrO2AlrxDKellcILNUtHyqYzgWFjnJHHrrIOSATGjF5t7Vzj0t0vNP8 i2SHv0geJRYthJ9KA1weZuF1zzXZcO91UZYKGSxTamaC1bG/D3fbwARMW+x3uXqQiJt/ jiem1PDxU3e+FP06nfQluHtx9NhRnlj2YU5w930yY5Kfdr3J1OfJ82Gt7gkXVORBwcRJ GACyE+x/7UZp24em88AaODf/ZzG7djxx81rIXOO+EwIX/A7AJ4iXnbW1I/HlOEOATVo5 No+Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20251104; t=1784164218; x=1784769018; h=references:to:cc:in-reply-to:date:subject:mime-version:content-type :message-id:from:x-gm-gg:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to:content-type; bh=cKDssjwA/oc1+Y1Sw+89U4jD+XOvDsd1z7Xcr20Tb9Q=; b=JIv2Z55LRPGy01/y68QIh8acK6E1Zy9WmjC5T1stTxrP3fbzWQacXMSJJZFKvySiEp jes5kfuHayseUdlXx3+dBLA7Um0ft7hEBTA5HRe/j4g/vHPCjtNfV3QvDKsF5Gcbzr0p pVPZ+WP0CshKsSchMMFxdG6N3yWcGfUjNV9kXIsxO1kpC3u951GLrP/zrjZsB3j+jpnm zQuwq1uyGm9RESAiJ47hVBf51hXxL/lDYrDPhLBf/bj6RWvWk5b+T5UJs8YLsYOV2V6B Ab7Gu4qx4+xK68aLlWdPVUsxWImJeZ6e2mOsrTTq7MqGMXGHXdw2ZlviLtNJ5XkJ2OP8 klUg== X-Forwarded-Encrypted: i=1; AHgh+Ro4WOW0TGM14JK0Pakkmco9abHzPffy1BsLfbUsA1rbfnTnY4Y4kZLodu5ddz7A9uShihH/8DyD2U6ysGfi@postgresql.org X-Gm-Message-State: AOJu0YyFkR8AJfB6+ZvY8pUg7leen20J9tSM+4wdLrONSV+wEypn9ePm kNxLr1C7PV5g/tiojumald4R8dhb7jps2pOfF0WfXK7rdKCjXqszruP4 X-Gm-Gg: AfdE7ckIpRbRMi2OtViTKyXud6ibItJgaBy/yHyBJjkxssaid/OvN/ORNEizGl8o/qn x3c48iLm2YFxdgdYBV6oRDw8AP0Hvhj/VaVHDiaHUUvhhCTDjxtiZCQLtUcswENt9KOur6aHs/r MBVUR4fdNlYQmrKhxZrByEufcr1ThB92/FlNxN9hLoW3Z7IlQA5SLi2Gv45kJRaY0r1qMNzoAA/ L1QQjuXURnqYVWVTo3J+YBFZYG0rJOiI3dsep/prxPFBCY4itpbDPTq35oXiKtzn1iWqGwkm2uD GQVNntz2AAcQDSpxB8xE+ITSxfHyUKVSgMs8H4ewDEb58u7g9ntKzk1dHPARFR/Ey9XHq1b4sew PIi2BjNoJNKZxvIFKS8h0IuGTkYlWnipFV/Yy2QIOFy3Jms0JLGPJaFCCCI853hPDWYiTuvBMmy AwFSOd5AI/ZjA= X-Received: by 2002:a05:6a21:4d81:b0:3bf:baed:c797 with SMTP id adf61e73a8af0-3c36c2ed69amr5392475637.49.1784164218000; Wed, 15 Jul 2026 18:10:18 -0700 (PDT) Received: from smtpclient.apple ([185.135.79.125]) by smtp.gmail.com with ESMTPSA id 41be03b00d2f7-cb25902b597sm971429a12.32.2026.07.15.18.10.13 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Wed, 15 Jul 2026 18:10:15 -0700 (PDT) From: Chao Li Message-Id: <49247968-DB60-4FF3-892F-CF910E63ADBA@gmail.com> Content-Type: multipart/mixed; boundary="Apple-Mail=_A59BC863-D757-4D70-9092-976B2A473F90" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3864.600.51.1.1\)) Subject: Re: Fix RLS checks for UPDATE/DELETE FOR PORTION OF leftover rows Date: Thu, 16 Jul 2026 09:09:40 +0800 In-Reply-To: Cc: Dean Rasheed , Ayush Tiwari , PostgreSQL-development , Peter Eisentraut To: Paul A Jungwirth References: <6C34A987-AC50-4477-BD71-2D4AFEE1A589@gmail.com> <07EF5F4D-4AA3-4C21-8868-9ACC0D8B6655@gmail.com> <20DF964A-C291-491A-9126-62753DCBD7CE@gmail.com> X-Mailer: Apple Mail (2.3864.600.51.1.1) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_A59BC863-D757-4D70-9092-976B2A473F90 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > On Jul 16, 2026, at 08:26, Paul A Jungwirth = wrote: >=20 > On Wed, Jul 15, 2026 at 4:40=E2=80=AFPM Dean Rasheed = wrote: >>=20 >> On Wed, 15 Jul 2026 at 22:02, Paul A Jungwirth >> wrote: >>>=20 >>> Thanks for taking a look! Here is a v4 with those additions. >>>=20 >>=20 >> + UPDATE ... FOR PORTION OF >> + >> + Filter existing row  & >> + check new row  >> + >> + >>=20 >> The footnote "rls-select-priv" reads "If read access is required to >> either the existing or new row (for example, a WHERE or RETURNING >> clause that refers to columns from the relation)." However, FOR >> PORTION OF is guaranteed to require read access to the table, so as >> with a few other entries in that column, the link to this footnote is >> not needed -- it will unconditionally apply the SELECT policy to the >> existing and new rows. Similarly for DELETE ... FOR PORTION OF. >=20 > Just to confirm: you're saying that because FOR PORTION OF always > filters rows, the "if" in the footnote is misleading? I can see that > argument, so I removed the footnote after "filter existing row" and > "check new row" from the new entries. Do you think I should add a > different footnote explaining that it is unconditional? >=20 >> + /* >> + * As with regular INSERT/UPDATE above, if SELECT rights are = needed >> + * for the statement, ensure the leftover row remains = visible. >> + */ >> + if (perminfo->requiredPerms & ACL_SELECT) >> + { >> + List *select_permissive_policies; >> + List *select_restrictive_policies; >> + >> + get_policies_for_relation(rel, CMD_SELECT, user_id, >> + &select_permissive_policies, >> + &select_restrictive_policies); >> + add_with_check_options(rel, rt_index, >> + WCO_RLS_INSERT_CHECK, >> + select_permissive_policies, >> + select_restrictive_policies, >> + withCheckOptions, >> + hasSubLinks, >> + true); >> + } >>=20 >> Thinking some more about this, I'm not so sure it's right. For a = plain >> INSERT, we apply SELECT policies if SELECT permissions are required, >> which only happens if there is a RETURNING clause. The reason is to >> ensure the user has rights to see the row returned by the RETURNING >> clause. However, for the auxiliary INSERTS for leftover portions in >> UPDATE/DELETE ... FOR PORTION OF, even if there is a RETURNING = clause, >> it will only return the updated/deleted row, not the leftover rows. >> The command never reads the leftover rows, so why do we need the >> leftover rows to satisfy the SELECT policies? >=20 > I think you are right. I replaced that section with a comment. >=20 > v5 attached. >=20 > Yours, >=20 > --=20 > Paul ~{:-) > pj@illuminatedcomputing.com > Thanks Dean for the reviews. Thanks Paul for updating the patch. Actually, when I received v4, I was also working on addressing Dean=E2=80=99= s comments. I reviewed both v4 and v5, made some small adjustments, and here is v6: * For the create_policy doc change, I changed =E2=80=9Ccheck new row=E2=80= =9D to =E2=80=9Ccheck leftover rows=E2=80=9D. * For the =E2=80=9CSELECT rights=E2=80=9D comment, I moved it into the = block comment, because its position in v5 looked like a placeholder. * For the tests, v4 and v5 added the new cases in the MERGE section. I = added the test in a more direct section. * For the tests, v4 and v5 granted INSERT privilege. V6 intentionally = does not, because the test runs UPDATE/DELETE FOR PORTION OF, and INSERT = privilege is not required for inserting leftover rows by design. I also updated the commit message to add Paul as a co-author and Dean as = a reviewer. Best regards, -- Chao Li (Evan) HighGo Software Co., Ltd. https://www.highgo.com/ --Apple-Mail=_A59BC863-D757-4D70-9092-976B2A473F90 Content-Disposition: attachment; filename=v6-0001-Fix-RLS-checks-for-FOR-PORTION-OF-leftover-rows.patch Content-Type: application/octet-stream; x-unix-mode=0644; name="v6-0001-Fix-RLS-checks-for-FOR-PORTION-OF-leftover-rows.patch" Content-Transfer-Encoding: quoted-printable =46rom=2083e9c5bf761661068819f25ab91a6fd3c1297a27=20Mon=20Sep=2017=20= 00:00:00=202001=0AFrom:=20"Chao=20Li=20(Evan)"=20=0A= Date:=20Tue,=2030=20Jun=202026=2015:22:29=20+0800=0ASubject:=20[PATCH=20= v6]=20Fix=20RLS=20checks=20for=20FOR=20PORTION=20OF=20leftover=20rows=0A=0A= UPDATE/DELETE=20FOR=20PORTION=20OF=20may=20insert=20leftover=20rows=20to=20= preserve=20the=0Aparts=20of=20the=20old=20row=20that=20are=20outside=20= the=20target=20range.=20Those=20inserts=20go=0Athrough=20ExecInsert(),=20= which=20checks=20RLS=20policies=20using=0AWCO_RLS_INSERT_CHECK.=0A=0A= However,=20the=20rewriter=20only=20added=20RLS=20WITH=20CHECK=20options=20= for=20the=20original=0Astatement=20command.=20For=20UPDATE,=20that=20= meant=20only=20WCO_RLS_UPDATE_CHECK=0Aoptions=20were=20available,=20so=20= ExecInsert()=20skipped=20them.=20For=20DELETE,=20no=20RLS=0AWITH=20CHECK=20= options=20were=20added=20at=20all.=20As=20a=20result,=20leftover=20rows=20= could=20be=0Ainserted=20even=20when=20they=20violated=20INSERT=20RLS=20= policies.=0A=0AFix=20this=20by=20adding=20INSERT=20RLS=20WITH=20CHECK=20= options=20for=20UPDATE/DELETE=20FOR=0APORTION=20OF=20target=20relations.=20= Also=20add=20regression=20coverage=20for=20both=20UPDATE=0Aand=20DELETE,=20= including=20cases=20where=20allowed=20leftovers=20still=20succeed=20and=0A= disallowed=20leftovers=20are=20rejected.=0A=0AAuthor:=20Chao=20Li=20= =0ACo-authored-by:=20Paul=20A=20Jungwirth=20= =0AReviewed-by:=20Paul=20A=20Jungwirth=20= =0AReviewed-by:=20Ayush=20Tiwari=20= =0AReviewed-by:=20Dean=20Rasheed=20= =0ADiscussion:=20= https://postgr.es/m/6C34A987-AC50-4477-BD71-2D4AFEE1A589@gmail.com=0A= Discussion:=20= https://postgr.es/m/CAJTYsWWdeBkoH5g8D-k9LDw9ciqsMxb21EJSiFXAzP4J=3DXyxOQ@= mail.gmail.com=0A---=0A=20doc/src/sgml/ref/create_policy.sgml=20=20=20=20= =20=20=20=20=20=20|=2032=20+++++++++=0A=20doc/src/sgml/ref/delete.sgml=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20|=20=203=20+-=0A=20= doc/src/sgml/ref/update.sgml=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20|=20=203=20+-=0A=20src/backend/rewrite/rowsecurity.c=20=20=20=20=20= =20=20=20=20=20=20=20|=2025=20+++++++=0A=20= src/test/regress/expected/for_portion_of.out=20|=2075=20= ++++++++++++++++++++=0A=20src/test/regress/expected/rowsecurity.out=20=20= =20=20|=2039=20+++++++++-=0A=20src/test/regress/sql/for_portion_of.sql=20= =20=20=20=20=20|=2058=20+++++++++++++++=0A=20= src/test/regress/sql/rowsecurity.sql=20=20=20=20=20=20=20=20=20|=2033=20= ++++++++-=0A=208=20files=20changed,=20264=20insertions(+),=204=20= deletions(-)=0A=0Adiff=20--git=20a/doc/src/sgml/ref/create_policy.sgml=20= b/doc/src/sgml/ref/create_policy.sgml=0Aindex=20d8a036739c0..0a1699185c9=20= 100644=0A---=20a/doc/src/sgml/ref/create_policy.sgml=0A+++=20= b/doc/src/sgml/ref/create_policy.sgml=0A@@=20-530,6=20+530,26=20@@=20= CREATE=20POLICY=20name=20= ON=20Check=20new=20= row=0A=20=20=20=20=20=20=20=20—=0A=20=20=20=20= =20=20=20=0A+=20=20=20=20=20=20=0A+=20=20=20=20=20=20=20= UPDATE=20...=20FOR=20PORTION=20OF=0A+=20= =20=20=20=20=20=20=0A+=20=20=20=20=20=20=20=20Filter=20existing=20= row=20&=20check=20new=20row=0A+=20=20=20=20=20=20=20=0A+=20=20= =20=20=20=20=20=0A+=20=20=20=20=20=20=20=20Check=20leftover=20= rows =0A+=20=20=20=20=20=20=20=20= =20=0A+=20=20=20=20=20=20=20=20=20=20FOR=20PORTION=20= OF=20re-inserts=20the=20portions=20of=20the=0A+=20=20=20=20=20=20= =20=20=20=20affected=20row=20that=20fall=20outside=20the=20targeted=20= range,=20to=20preserve=20them.=0A+=20=20=20=20=20=20=20=20=20=20These=20= leftover=20rows=20are=20checked=20against=20the=20= INSERT=0A+=20=20=20=20=20=20=20=20=20=20policy's=20= WITH=20CHECK=20expression,=20even=20though=20no=0A+=20= =20=20=20=20=20=20=20=20=20INSERT=20privilege=20is=20= required=20to=20store=20them.=0A+=20=20=20=20=20=20=20=20=20=0A+=20= =20=20=20=20=20=20=20=0A+=20=20=20=20=20=20=20=0A+=20=20= =20=20=20=20=20Filter=20existing=20row=0A+=20=20=20=20=20=20= =20Check=20new=20row=0A+=20=20=20=20=20=20=20= —=0A+=20=20=20=20=20=20=0A=20=20=20=20=20=20=20= =0A=20=20=20=20=20=20=20=20DELETE=0A= =20=20=20=20=20=20=20=20=0A@@=20-540,6=20+560,18=20@@=20CREATE=20= POLICY=20name=20ON=20= —=0A=20=20=20= =20=20=20=20=20Filter=20existing=20row=0A=20=20=20=20=20=20= =20=0A+=20=20=20=20=20=20=0A+=20=20=20=20=20=20=20= DELETE=20...=20FOR=20PORTION=20OF=0A+=20= =20=20=20=20=20=20=0A+=20=20=20=20=20=20=20=20Filter=20existing=20= row=0A+=20=20=20=20=20=20=20=0A+=20=20=20=20=20=20=20=0A+=20= =20=20=20=20=20=20=20Check=20leftover=20rows =0A+=20=20=20=20=20=20=20=0A+=20=20= =20=20=20=20=20—=0A+=20=20=20=20=20=20=20= —=0A+=20=20=20=20=20=20=20Filter=20existing=20= row=0A+=20=20=20=20=20=20=0A=20=20=20=20=20=20=20=0A=20= =20=20=20=20=20=20=20INSERT=20...=20ON=20= CONFLICT=0A=20=20=20=20=20=20=20=20=0Adiff=20= --git=20a/doc/src/sgml/ref/delete.sgml=20b/doc/src/sgml/ref/delete.sgml=0A= index=20ffdcd7fc4fa..3ab1f60525a=20100644=0A---=20= a/doc/src/sgml/ref/delete.sgml=0A+++=20b/doc/src/sgml/ref/delete.sgml=0A= @@=20-97,7=20+97,8=20@@=20DELETE=20FROM=20[=20ONLY=20]=20table_name=20[=20*=0A=20=20=20=20When=20= FOR=20PORTION=20OF=20is=20used,=20the=20secondary=20= inserts=20do=0A=20=20=20=20not=20require=20INSERT=20= privilege=20on=20the=20table.=20=20(This=20is=0A=20=20=20=20because=20= conceptually=20no=20new=20information=20is=20being=20added;=20the=20= inserted=20rows=0A-=20=20=20only=20preserve=20existing=20data=20about=20= the=20untargeted=20time=20period.)=0A+=20=20=20only=20preserve=20= existing=20data=20about=20the=20untargeted=20time=20period.)=20=20= Row-level=0A+=20=20=20security=20INSERT=20policies=20are=20still=20= checked=20for=20these=20leftover=20inserts.=0A=20=20=20=0A=20=20= =0A=20=0Adiff=20--git=20a/doc/src/sgml/ref/update.sgml=20= b/doc/src/sgml/ref/update.sgml=0Aindex=2021a8fd8b037..3175671d475=20= 100644=0A---=20a/doc/src/sgml/ref/update.sgml=0A+++=20= b/doc/src/sgml/ref/update.sgml=0A@@=20-101,7=20+101,8=20@@=20UPDATE=20[=20= ONLY=20]=20table_name=20= [=20*=20]=0A=20=20=20=20When=20FOR=20PORTION=20OF=20= is=20used,=20the=20secondary=20inserts=20do=0A=20=20=20=20not=20require=20= INSERT=20privilege=20on=20the=20table.=20=20(This=20= is=0A=20=20=20=20because=20conceptually=20no=20new=20information=20is=20= being=20added;=20the=20inserted=20rows=0A-=20=20=20only=20preserve=20= existing=20data=20about=20the=20untargeted=20time=20period.)=0A+=20=20=20= only=20preserve=20existing=20data=20about=20the=20untargeted=20time=20= period.)=20=20Row-level=0A+=20=20=20security=20INSERT=20policies=20are=20= still=20checked=20for=20these=20leftover=20inserts.=0A=20=20=20=0A= =20=20=0A=20=0Adiff=20--git=20= a/src/backend/rewrite/rowsecurity.c=20= b/src/backend/rewrite/rowsecurity.c=0Aindex=20e88a1bc1a89..46d88f79894=20= 100644=0A---=20a/src/backend/rewrite/rowsecurity.c=0A+++=20= b/src/backend/rewrite/rowsecurity.c=0A@@=20-393,6=20+393,31=20@@=20= get_row_security_policies(Query=20*root,=20RangeTblEntry=20*rte,=20int=20= rt_index,=0A=20=09=09}=0A=20=09}=0A=20=0A+=09/*=0A+=09=20*=20= UPDATE/DELETE=20FOR=20PORTION=20OF=20may=20insert=20leftover=20rows=20to=20= preserve=20the=0A+=09=20*=20portions=20of=20the=20old=20row=20not=20= covered=20by=20the=20target=20range.=20=20Those=20hidden=0A+=09=20*=20= inserts=20go=20through=20ExecInsert(),=20so=20they=20need=20the=20same=20= INSERT=20RLS=20WITH=0A+=09=20*=20CHECK=20options=20as=20ordinary=20= INSERTs.=20=20SELECT=20rights=20are=20never=20needed=20for=0A+=09=20*=20= the=20leftover=20rows,=20because=20they=20are=20not=20considered=20by=20= RETURNING.=0A+=09=20*/=0A+=09if=20(root->forPortionOf=20!=3D=20NULL=20&&=20= rt_index=20=3D=3D=20root->resultRelation=20&&=0A+=09=09(commandType=20=3D=3D= =20CMD_UPDATE=20||=20commandType=20=3D=3D=20CMD_DELETE))=0A+=09{=0A+=09=09= List=09=20=20=20*insert_permissive_policies;=0A+=09=09List=09=20=20=20= *insert_restrictive_policies;=0A+=0A+=09=09= get_policies_for_relation(rel,=20CMD_INSERT,=20user_id,=0A+=09=09=09=09=09= =09=09=09=20=20&insert_permissive_policies,=0A+=09=09=09=09=09=09=09=09=20= =20&insert_restrictive_policies);=0A+=09=09add_with_check_options(rel,=20= rt_index,=0A+=09=09=09=09=09=09=09=20=20=20WCO_RLS_INSERT_CHECK,=0A+=09=09= =09=09=09=09=09=20=20=20insert_permissive_policies,=0A+=09=09=09=09=09=09= =09=20=20=20insert_restrictive_policies,=0A+=09=09=09=09=09=09=09=20=20=20= withCheckOptions,=0A+=09=09=09=09=09=09=09=20=20=20hasSubLinks,=0A+=09=09= =09=09=09=09=09=20=20=20false);=0A+=09}=0A+=0A=20=09/*=0A=20=09=20*=20= FOR=20MERGE,=20we=20fetch=20policies=20for=20UPDATE,=20DELETE=20and=20= INSERT=20(and=20ALL)=0A=20=09=20*=20and=20set=20them=20up=20so=20that=20= we=20can=20enforce=20the=20appropriate=20policy=20depending=0Adiff=20= --git=20a/src/test/regress/expected/for_portion_of.out=20= b/src/test/regress/expected/for_portion_of.out=0Aindex=20= a050fc2dadf..271282c2d3b=20100644=0A---=20= a/src/test/regress/expected/for_portion_of.out=0A+++=20= b/src/test/regress/expected/for_portion_of.out=0A@@=20-2561,4=20+2561,79=20= @@=20SELECT=20*=20FROM=20fpo_cursed;=0A=20(1=20row)=0A=20=0A=20DROP=20= TABLE=20fpo_cursed;=0A+--=20UPDATE/DELETE=20FOR=20PORTION=20OF=20= leftover=20rows=20must=20satisfy=20RLS=20INSERT=20checks.=0A+CREATE=20= ROLE=20regress_fpo_rls;=0A+CREATE=20TABLE=20fpo_rls=20(=0A+=20=20id=20= int,=0A+=20=20valid_at=20int4range=0A+);=0A+ALTER=20TABLE=20fpo_rls=20= ENABLE=20ROW=20LEVEL=20SECURITY;=0A+CREATE=20POLICY=20fpo_rls_select=20= ON=20fpo_rls=0A+=20=20FOR=20SELECT=20TO=20regress_fpo_rls=0A+=20=20USING=20= (true);=0A+CREATE=20POLICY=20fpo_rls_update=20ON=20fpo_rls=0A+=20=20FOR=20= UPDATE=20TO=20regress_fpo_rls=0A+=20=20USING=20(lower(valid_at)=20<=20= 50)=0A+=20=20WITH=20CHECK=20(lower(valid_at)=20<=2050);=0A+CREATE=20= POLICY=20fpo_rls_delete=20ON=20fpo_rls=0A+=20=20FOR=20DELETE=20TO=20= regress_fpo_rls=0A+=20=20USING=20(lower(valid_at)=20<=2050);=0A+CREATE=20= POLICY=20fpo_rls_insert=20ON=20fpo_rls=0A+=20=20FOR=20INSERT=20TO=20= regress_fpo_rls=0A+=20=20WITH=20CHECK=20(lower(valid_at)=20<=2050);=0A= +GRANT=20SELECT,=20UPDATE,=20DELETE=20ON=20fpo_rls=20TO=20= regress_fpo_rls;=0A+INSERT=20INTO=20fpo_rls=20VALUES=20(1,=20= '[10,100)');=0A+SET=20ROLE=20regress_fpo_rls;=0A+UPDATE=20fpo_rls=0A+=20=20= FOR=20PORTION=20OF=20valid_at=20FROM=2030=20TO=20100=0A+=20=20SET=20id=20= =3D=202;=0A+RESET=20ROLE;=0A+SELECT=20*=20FROM=20fpo_rls=20ORDER=20BY=20= valid_at;=0A+=20id=20|=20valid_at=20=0A+----+----------=0A+=20=201=20|=20= [10,30)=0A+=20=202=20|=20[30,100)=0A+(2=20rows)=0A+=0A+TRUNCATE=20= fpo_rls;=0A+INSERT=20INTO=20fpo_rls=20VALUES=20(1,=20'[10,100)');=0A+SET=20= ROLE=20regress_fpo_rls;=0A+DELETE=20FROM=20fpo_rls=0A+=20=20FOR=20= PORTION=20OF=20valid_at=20FROM=2030=20TO=20100;=0A+RESET=20ROLE;=0A= +SELECT=20*=20FROM=20fpo_rls=20ORDER=20BY=20valid_at;=0A+=20id=20|=20= valid_at=20=0A+----+----------=0A+=20=201=20|=20[10,30)=0A+(1=20row)=0A+=0A= +TRUNCATE=20fpo_rls;=0A+INSERT=20INTO=20fpo_rls=20VALUES=20(1,=20= '[10,100)');=0A+SET=20ROLE=20regress_fpo_rls;=0A+UPDATE=20fpo_rls=0A+=20=20= FOR=20PORTION=20OF=20valid_at=20FROM=2030=20TO=2070=0A+=20=20SET=20id=20= =3D=202;=0A+ERROR:=20=20new=20row=20violates=20row-level=20security=20= policy=20for=20table=20"fpo_rls"=0A+RESET=20ROLE;=0A+SELECT=20*=20FROM=20= fpo_rls=20ORDER=20BY=20valid_at;=0A+=20id=20|=20valid_at=20=0A= +----+----------=0A+=20=201=20|=20[10,100)=0A+(1=20row)=0A+=0A+TRUNCATE=20= fpo_rls;=0A+INSERT=20INTO=20fpo_rls=20VALUES=20(1,=20'[10,100)');=0A+SET=20= ROLE=20regress_fpo_rls;=0A+DELETE=20FROM=20fpo_rls=0A+=20=20FOR=20= PORTION=20OF=20valid_at=20FROM=2030=20TO=2070;=0A+ERROR:=20=20new=20row=20= violates=20row-level=20security=20policy=20for=20table=20"fpo_rls"=0A= +RESET=20ROLE;=0A+SELECT=20*=20FROM=20fpo_rls=20ORDER=20BY=20valid_at;=0A= +=20id=20|=20valid_at=20=0A+----+----------=0A+=20=201=20|=20[10,100)=0A= +(1=20row)=0A+=0A+DROP=20TABLE=20fpo_rls;=0A+DROP=20ROLE=20= regress_fpo_rls;=0A=20RESET=20datestyle;=0Adiff=20--git=20= a/src/test/regress/expected/rowsecurity.out=20= b/src/test/regress/expected/rowsecurity.out=0Aindex=20= 3a5e82c35bd..f42085e53a0=20100644=0A---=20= a/src/test/regress/expected/rowsecurity.out=0A+++=20= b/src/test/regress/expected/rowsecurity.out=0A@@=20-68,6=20+68,20=20@@=20= CREATE=20POLICY=20upd_pol=20ON=20rls_test_tgt=20FOR=20UPDATE=0A=20=20=20= WITH=20CHECK=20(rls_test_policy_fn('UPDATE=20CHECK=20on=20rls_test_tgt',=20= rls_test_tgt));=0A=20CREATE=20POLICY=20del_pol=20ON=20rls_test_tgt=20FOR=20= DELETE=0A=20=20=20USING=20(rls_test_policy_fn('DELETE=20USING=20on=20= rls_test_tgt',=20rls_test_tgt));=0A+--=20setup=20temporal=20target=20= table=20(for=20FOR=20PORTION=20OF=20operations)=0A+CREATE=20TABLE=20= rls_test_fpo_tgt=20(a=20int,=20b=20text,=20valid_at=20int4range);=0A= +ALTER=20TABLE=20rls_test_fpo_tgt=20ENABLE=20ROW=20LEVEL=20SECURITY;=0A= +GRANT=20SELECT,=20UPDATE,=20DELETE=20ON=20rls_test_fpo_tgt=20TO=20= public;=0A+INSERT=20INTO=20rls_test_fpo_tgt=20VALUES=20(1,=20'fpo=20a',=20= '[1,10)');=0A+CREATE=20POLICY=20sel_pol=20ON=20rls_test_fpo_tgt=20FOR=20= SELECT=0A+=20=20USING=20(rls_test_policy_fn('SELECT=20USING=20on=20= rls_test_fpo_tgt',=20rls_test_fpo_tgt));=0A+CREATE=20POLICY=20ins_pol=20= ON=20rls_test_fpo_tgt=20FOR=20INSERT=0A+=20=20WITH=20CHECK=20= (rls_test_policy_fn('INSERT=20CHECK=20on=20rls_test_fpo_tgt',=20= rls_test_fpo_tgt));=0A+CREATE=20POLICY=20upd_pol=20ON=20rls_test_fpo_tgt=20= FOR=20UPDATE=0A+=20=20USING=20(rls_test_policy_fn('UPDATE=20USING=20on=20= rls_test_fpo_tgt',=20rls_test_fpo_tgt))=0A+=20=20WITH=20CHECK=20= (rls_test_policy_fn('UPDATE=20CHECK=20on=20rls_test_fpo_tgt',=20= rls_test_fpo_tgt));=0A+CREATE=20POLICY=20del_pol=20ON=20rls_test_fpo_tgt=20= FOR=20DELETE=0A+=20=20USING=20(rls_test_policy_fn('DELETE=20USING=20on=20= rls_test_fpo_tgt',=20rls_test_fpo_tgt));=0A=20--=20test=20policies=20= applied=20to=20regress_rls_bob=0A=20SET=20SESSION=20AUTHORIZATION=20= regress_rls_bob;=0A=20--=20SELECT,=20COPY=20...=20TO,=20and=20TABLE=20= should=20only=20apply=20SELECT=20USING=20policy=20clause=0A@@=20-155,6=20= +169,19=20@@=20NOTICE:=20=20SELECT=20USING=20on=20rls_test_tgt.(1,"tgt=20= d","TGT=20D")=0A=20=201=20|=20tgt=20d=20|=20TGT=20D=0A=20(1=20row)=0A=20=0A= +--=20UPDATE=20...=20FOR=20PORTION=20OF=20should=20also=20apply=20SELECT=20= USING=20policy=20clauses=20to=0A+--=20the=20old=20and=20new=20rows,=20= and=20INSERT=20CHECK=20policy=20clauses=20to=20leftover=20rows.=0A= +BEGIN;=0A+UPDATE=20rls_test_fpo_tgt=0A+=20=20FOR=20PORTION=20OF=20= valid_at=20FROM=203=20TO=207=0A+=20=20SET=20b=20=3D=20'fpo=20b';=0A= +NOTICE:=20=20UPDATE=20USING=20on=20rls_test_fpo_tgt.(1,"fpo=20= a","[1,10)")=0A+NOTICE:=20=20SELECT=20USING=20on=20= rls_test_fpo_tgt.(1,"fpo=20a","[1,10)")=0A+NOTICE:=20=20UPDATE=20CHECK=20= on=20rls_test_fpo_tgt.(1,"fpo=20b","[3,7)")=0A+NOTICE:=20=20SELECT=20= USING=20on=20rls_test_fpo_tgt.(1,"fpo=20b","[3,7)")=0A+NOTICE:=20=20= INSERT=20CHECK=20on=20rls_test_fpo_tgt.(1,"fpo=20a","[1,3)")=0A+NOTICE:=20= =20INSERT=20CHECK=20on=20rls_test_fpo_tgt.(1,"fpo=20a","[7,10)")=0A= +ROLLBACK;=0A=20--=20DELETE=20without=20WHERE=20or=20RETURNING=20should=20= only=20apply=20DELETE=20USING=20policy=20clause=0A=20BEGIN;=20DELETE=20= FROM=20rls_test_tgt;=20ROLLBACK;=0A=20NOTICE:=20=20DELETE=20USING=20on=20= rls_test_tgt.(1,"tgt=20d","TGT=20D")=0A@@=20-170,6=20+197,16=20@@=20= NOTICE:=20=20SELECT=20USING=20on=20rls_test_tgt.(1,"tgt=20d","TGT=20D")=0A= =20=201=20|=20tgt=20d=20|=20TGT=20D=0A=20(1=20row)=0A=20=0A+--=20DELETE=20= ...=20FOR=20PORTION=20OF=20should=20also=20apply=20SELECT=20USING=20= policy=20clauses=20to=0A+--=20the=20old=20row,=20and=20INSERT=20CHECK=20= policy=20clauses=20to=20leftover=20rows.=0A+BEGIN;=0A+DELETE=20FROM=20= rls_test_fpo_tgt=0A+=20=20FOR=20PORTION=20OF=20valid_at=20FROM=203=20TO=20= 7;=0A+NOTICE:=20=20DELETE=20USING=20on=20rls_test_fpo_tgt.(1,"fpo=20= a","[1,10)")=0A+NOTICE:=20=20SELECT=20USING=20on=20= rls_test_fpo_tgt.(1,"fpo=20a","[1,10)")=0A+NOTICE:=20=20INSERT=20CHECK=20= on=20rls_test_fpo_tgt.(1,"fpo=20a","[1,3)")=0A+NOTICE:=20=20INSERT=20= CHECK=20on=20rls_test_fpo_tgt.(1,"fpo=20a","[7,10)")=0A+ROLLBACK;=0A=20= --=20INSERT=20...=20ON=20CONFLICT=20DO=20NOTHING=20with=20an=20arbiter=20= clause=20should=20apply=0A=20--=20INSERT=20CHECK=20and=20SELECT=20USING=20= policy=20clauses=20(to=20new=20value,=20whether=20it=0A=20--=20conflicts=20= or=20not)=0A@@=20-332,7=20+369,7=20@@=20NOTICE:=20=20DELETE=20USING=20on=20= rls_test_tgt.(1,"tgt=20c","TGT=20C")=0A=20=0A=20--=20Tidy=20up=0A=20= RESET=20SESSION=20AUTHORIZATION;=0A-DROP=20TABLE=20rls_test_src,=20= rls_test_tgt;=0A+DROP=20TABLE=20rls_test_src,=20rls_test_tgt,=20= rls_test_fpo_tgt;=0A=20DROP=20FUNCTION=20rls_test_tgt_set_c;=0A=20DROP=20= FUNCTION=20rls_test_policy_fn;=0A=20--=20BASIC=20Row-Level=20Security=20= Scenario=0Adiff=20--git=20a/src/test/regress/sql/for_portion_of.sql=20= b/src/test/regress/sql/for_portion_of.sql=0Aindex=20= a1ee1d5e501..f48644347d1=20100644=0A---=20= a/src/test/regress/sql/for_portion_of.sql=0A+++=20= b/src/test/regress/sql/for_portion_of.sql=0A@@=20-1676,4=20+1676,62=20@@=20= ROLLBACK;=0A=20SELECT=20*=20FROM=20fpo_cursed;=0A=20DROP=20TABLE=20= fpo_cursed;=0A=20=0A+--=20UPDATE/DELETE=20FOR=20PORTION=20OF=20leftover=20= rows=20must=20satisfy=20RLS=20INSERT=20checks.=0A+CREATE=20ROLE=20= regress_fpo_rls;=0A+CREATE=20TABLE=20fpo_rls=20(=0A+=20=20id=20int,=0A+=20= =20valid_at=20int4range=0A+);=0A+ALTER=20TABLE=20fpo_rls=20ENABLE=20ROW=20= LEVEL=20SECURITY;=0A+CREATE=20POLICY=20fpo_rls_select=20ON=20fpo_rls=0A+=20= =20FOR=20SELECT=20TO=20regress_fpo_rls=0A+=20=20USING=20(true);=0A= +CREATE=20POLICY=20fpo_rls_update=20ON=20fpo_rls=0A+=20=20FOR=20UPDATE=20= TO=20regress_fpo_rls=0A+=20=20USING=20(lower(valid_at)=20<=2050)=0A+=20=20= WITH=20CHECK=20(lower(valid_at)=20<=2050);=0A+CREATE=20POLICY=20= fpo_rls_delete=20ON=20fpo_rls=0A+=20=20FOR=20DELETE=20TO=20= regress_fpo_rls=0A+=20=20USING=20(lower(valid_at)=20<=2050);=0A+CREATE=20= POLICY=20fpo_rls_insert=20ON=20fpo_rls=0A+=20=20FOR=20INSERT=20TO=20= regress_fpo_rls=0A+=20=20WITH=20CHECK=20(lower(valid_at)=20<=2050);=0A= +GRANT=20SELECT,=20UPDATE,=20DELETE=20ON=20fpo_rls=20TO=20= regress_fpo_rls;=0A+=0A+INSERT=20INTO=20fpo_rls=20VALUES=20(1,=20= '[10,100)');=0A+SET=20ROLE=20regress_fpo_rls;=0A+UPDATE=20fpo_rls=0A+=20=20= FOR=20PORTION=20OF=20valid_at=20FROM=2030=20TO=20100=0A+=20=20SET=20id=20= =3D=202;=0A+RESET=20ROLE;=0A+SELECT=20*=20FROM=20fpo_rls=20ORDER=20BY=20= valid_at;=0A+=0A+TRUNCATE=20fpo_rls;=0A+INSERT=20INTO=20fpo_rls=20VALUES=20= (1,=20'[10,100)');=0A+SET=20ROLE=20regress_fpo_rls;=0A+DELETE=20FROM=20= fpo_rls=0A+=20=20FOR=20PORTION=20OF=20valid_at=20FROM=2030=20TO=20100;=0A= +RESET=20ROLE;=0A+SELECT=20*=20FROM=20fpo_rls=20ORDER=20BY=20valid_at;=0A= +=0A+TRUNCATE=20fpo_rls;=0A+INSERT=20INTO=20fpo_rls=20VALUES=20(1,=20= '[10,100)');=0A+SET=20ROLE=20regress_fpo_rls;=0A+UPDATE=20fpo_rls=0A+=20=20= FOR=20PORTION=20OF=20valid_at=20FROM=2030=20TO=2070=0A+=20=20SET=20id=20= =3D=202;=0A+RESET=20ROLE;=0A+SELECT=20*=20FROM=20fpo_rls=20ORDER=20BY=20= valid_at;=0A+=0A+TRUNCATE=20fpo_rls;=0A+INSERT=20INTO=20fpo_rls=20VALUES=20= (1,=20'[10,100)');=0A+SET=20ROLE=20regress_fpo_rls;=0A+DELETE=20FROM=20= fpo_rls=0A+=20=20FOR=20PORTION=20OF=20valid_at=20FROM=2030=20TO=2070;=0A= +RESET=20ROLE;=0A+SELECT=20*=20FROM=20fpo_rls=20ORDER=20BY=20valid_at;=0A= +=0A+DROP=20TABLE=20fpo_rls;=0A+DROP=20ROLE=20regress_fpo_rls;=0A+=0A=20= RESET=20datestyle;=0Adiff=20--git=20= a/src/test/regress/sql/rowsecurity.sql=20= b/src/test/regress/sql/rowsecurity.sql=0Aindex=20= 6b3566271df..0bd24d1028b=20100644=0A---=20= a/src/test/regress/sql/rowsecurity.sql=0A+++=20= b/src/test/regress/sql/rowsecurity.sql=0A@@=20-85,6=20+85,22=20@@=20= CREATE=20POLICY=20upd_pol=20ON=20rls_test_tgt=20FOR=20UPDATE=0A=20CREATE=20= POLICY=20del_pol=20ON=20rls_test_tgt=20FOR=20DELETE=0A=20=20=20USING=20= (rls_test_policy_fn('DELETE=20USING=20on=20rls_test_tgt',=20= rls_test_tgt));=0A=20=0A+--=20setup=20temporal=20target=20table=20(for=20= FOR=20PORTION=20OF=20operations)=0A+CREATE=20TABLE=20rls_test_fpo_tgt=20= (a=20int,=20b=20text,=20valid_at=20int4range);=0A+ALTER=20TABLE=20= rls_test_fpo_tgt=20ENABLE=20ROW=20LEVEL=20SECURITY;=0A+GRANT=20SELECT,=20= UPDATE,=20DELETE=20ON=20rls_test_fpo_tgt=20TO=20public;=0A+INSERT=20INTO=20= rls_test_fpo_tgt=20VALUES=20(1,=20'fpo=20a',=20'[1,10)');=0A+=0A+CREATE=20= POLICY=20sel_pol=20ON=20rls_test_fpo_tgt=20FOR=20SELECT=0A+=20=20USING=20= (rls_test_policy_fn('SELECT=20USING=20on=20rls_test_fpo_tgt',=20= rls_test_fpo_tgt));=0A+CREATE=20POLICY=20ins_pol=20ON=20rls_test_fpo_tgt=20= FOR=20INSERT=0A+=20=20WITH=20CHECK=20(rls_test_policy_fn('INSERT=20CHECK=20= on=20rls_test_fpo_tgt',=20rls_test_fpo_tgt));=0A+CREATE=20POLICY=20= upd_pol=20ON=20rls_test_fpo_tgt=20FOR=20UPDATE=0A+=20=20USING=20= (rls_test_policy_fn('UPDATE=20USING=20on=20rls_test_fpo_tgt',=20= rls_test_fpo_tgt))=0A+=20=20WITH=20CHECK=20(rls_test_policy_fn('UPDATE=20= CHECK=20on=20rls_test_fpo_tgt',=20rls_test_fpo_tgt));=0A+CREATE=20POLICY=20= del_pol=20ON=20rls_test_fpo_tgt=20FOR=20DELETE=0A+=20=20USING=20= (rls_test_policy_fn('DELETE=20USING=20on=20rls_test_fpo_tgt',=20= rls_test_fpo_tgt));=0A+=0A=20--=20test=20policies=20applied=20to=20= regress_rls_bob=0A=20SET=20SESSION=20AUTHORIZATION=20regress_rls_bob;=0A=20= =0A@@=20-114,6=20+130,14=20@@=20UPDATE=20rls_test_tgt=20SET=20b=20=3D=20= 'tgt=20b';=0A=20UPDATE=20rls_test_tgt=20SET=20b=20=3D=20'tgt=20c'=20= WHERE=20a=20=3D=201;=0A=20UPDATE=20rls_test_tgt=20SET=20b=20=3D=20'tgt=20= d'=20RETURNING=20*;=0A=20=0A+--=20UPDATE=20...=20FOR=20PORTION=20OF=20= should=20also=20apply=20SELECT=20USING=20policy=20clauses=20to=0A+--=20= the=20old=20and=20new=20rows,=20and=20INSERT=20CHECK=20policy=20clauses=20= to=20leftover=20rows.=0A+BEGIN;=0A+UPDATE=20rls_test_fpo_tgt=0A+=20=20= FOR=20PORTION=20OF=20valid_at=20FROM=203=20TO=207=0A+=20=20SET=20b=20=3D=20= 'fpo=20b';=0A+ROLLBACK;=0A+=0A=20--=20DELETE=20without=20WHERE=20or=20= RETURNING=20should=20only=20apply=20DELETE=20USING=20policy=20clause=0A=20= BEGIN;=20DELETE=20FROM=20rls_test_tgt;=20ROLLBACK;=0A=20=0A@@=20-121,6=20= +145,13=20@@=20BEGIN;=20DELETE=20FROM=20rls_test_tgt;=20ROLLBACK;=0A=20= BEGIN;=20DELETE=20FROM=20rls_test_tgt=20WHERE=20a=20=3D=201;=20ROLLBACK;=0A= =20DELETE=20FROM=20rls_test_tgt=20RETURNING=20*;=0A=20=0A+--=20DELETE=20= ...=20FOR=20PORTION=20OF=20should=20also=20apply=20SELECT=20USING=20= policy=20clauses=20to=0A+--=20the=20old=20row,=20and=20INSERT=20CHECK=20= policy=20clauses=20to=20leftover=20rows.=0A+BEGIN;=0A+DELETE=20FROM=20= rls_test_fpo_tgt=0A+=20=20FOR=20PORTION=20OF=20valid_at=20FROM=203=20TO=20= 7;=0A+ROLLBACK;=0A+=0A=20--=20INSERT=20...=20ON=20CONFLICT=20DO=20= NOTHING=20with=20an=20arbiter=20clause=20should=20apply=0A=20--=20INSERT=20= CHECK=20and=20SELECT=20USING=20policy=20clauses=20(to=20new=20value,=20= whether=20it=0A=20--=20conflicts=20or=20not)=0A@@=20-192,7=20+223,7=20@@=20= MERGE=20INTO=20rls_test_tgt=20t=20USING=20rls_test_src=20s=20ON=20t.a=20= =3D=20s.a=0A=20=0A=20--=20Tidy=20up=0A=20RESET=20SESSION=20= AUTHORIZATION;=0A-DROP=20TABLE=20rls_test_src,=20rls_test_tgt;=0A+DROP=20= TABLE=20rls_test_src,=20rls_test_tgt,=20rls_test_fpo_tgt;=0A=20DROP=20= FUNCTION=20rls_test_tgt_set_c;=0A=20DROP=20FUNCTION=20= rls_test_policy_fn;=0A=20=0A--=20=0A2.50.1=20(Apple=20Git-155)=0A=0A= --Apple-Mail=_A59BC863-D757-4D70-9092-976B2A473F90--