MIME-Version: 1.0
References: <CWXP265MB5009813E2DAA4D48F8C4A9F4F7F5A@CWXP265MB5009.GBRP265.PROD.OUTLOOK.COM>
 <202510210716.4cr5qkq2z2mj@alvherre.pgsql> <CWXP265MB5009E1A654FA3898D6FF41C1F7F2A@CWXP265MB5009.GBRP265.PROD.OUTLOOK.COM>
In-Reply-To: <CWXP265MB5009E1A654FA3898D6FF41C1F7F2A@CWXP265MB5009.GBRP265.PROD.OUTLOOK.COM>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Tue, 21 Oct 2025 10:26:04 -0400
Message-ID: <CANzqJaDq0BJduatLB2+5iNHN3Jzkk9hHrwSQesXq64C8JDGz9g@mail.gmail.com>
Subject: Re: Extend CREATE POLICY to add IF EXISTS
To: Paul Austin <paul.austin@automutatio.com>
Cc: =?UTF-8?Q?=C3=81lvaro_Herrera?= <alvherre@kurilemu.de>, 
	"pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000084baa60641abfb9a"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaDq0BJduatLB2%2B5iNHN3Jzkk9hHrwSQesXq64C8JDGz9g%40mail.gmail.com>
Precedence: bulk

--00000000000084baa60641abfb9a
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Doesn't CREATE OR REPLACE have limitations based on parameter-list changes?

On Tue, Oct 21, 2025 at 10:08=E2=80=AFAM Paul Austin <paul.austin@automutat=
io.com>
wrote:

> =C3=81lvaro,
>
> Yes, a CREATE OR REPLACE would also be useful.
>
> However, the CREATE IF NOT EXISTS is also useful when you aren't concerne=
d
> that the POLICY is going to change. Same with the existing CREATE TABLE I=
F
> NOT EXISTS.
>
> Yes I'm aware that the DROP/CREATE can create a security hole, which is
> why I'd like the IF NOT EXISTS. At the moment my use case stops the
> application prior to running this script and the database is in a private
> network.
>
> -----Original Message-----
> From: =C3=81lvaro Herrera <alvherre@kurilemu.de>
> Sent: October 21, 2025 12:23 AM
> To: Paul Austin <paul.austin@automutatio.com>
> Cc: pgsql-general@lists.postgresql.org
> Subject: Re: Extend CREATE POLICY to add IF EXISTS
>
> On 2025-Oct-20, Paul Austin wrote:
>
> > Adrian,
> >
> > The drop policy IF EXISTS does work.
> >
> > But it would be nice to have the IF NOT EXISTS on CREATE POLICY so I
> > don't need to do a drop and create.
>
> How would CREATE IF NOT EXISTS handle the case of an existing policy that
> doesn't match the one you want?  I think it would just silently not do
> anything, and in that case you can't really rely on it, can you?  So your
> script would have to extract the current policy, compare with the one you
> want (how?) and then maybe drop it and create it anew, or leave it alone.
> Is this really useful?
>
> I think what you'd really appreciate is CREATE OR REPLACE: if the policy
> exists and matches the one you ask for, then don't do anything; but
> otherwise throw it away and create it anew.  We have this for views, and =
it
> allows for things like adding more columns than the original view had.
>
> BTW, the pattern DROP IF EXISTS / CREATE is a bit nasty, because there
> exists a period in between where no policy exists, which could be a
> security hole.  Unless you use an explicit transaction block.
>
> --
> =C3=81lvaro Herrera        Breisgau, Deutschland  =E2=80=94
> https://www.EnterpriseDB.com/
> "I love the Postgres community. It's all about doing things _properly_.
> :-)"
> (David Garamond)
>


--=20
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

--00000000000084baa60641abfb9a
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Doesn&#39;t CREATE OR REPLACE have limitations based =
on=C2=A0parameter-list changes?</div><br><div class=3D"gmail_quote gmail_qu=
ote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Tue, Oct 21, 2025 a=
t 10:08=E2=80=AFAM Paul Austin &lt;<a href=3D"mailto:paul.austin@automutati=
o.com">paul.austin@automutatio.com</a>&gt; wrote:<br></div><blockquote clas=
s=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid r=
gb(204,204,204);padding-left:1ex">=C3=81lvaro,<br>
<br>
Yes, a CREATE OR REPLACE would also be useful.<br>
<br>
However, the CREATE IF NOT EXISTS is also useful when you aren&#39;t concer=
ned that the POLICY is going to change. Same with the existing CREATE TABLE=
 IF NOT EXISTS.<br>
<br>
Yes I&#39;m aware that the DROP/CREATE can create a security hole, which is=
 why I&#39;d like the IF NOT EXISTS. At the moment my use case stops the ap=
plication prior to running this script and the database is in a private net=
work.<br>
<br>
-----Original Message-----<br>
From: =C3=81lvaro Herrera &lt;<a href=3D"mailto:alvherre@kurilemu.de" targe=
t=3D"_blank">alvherre@kurilemu.de</a>&gt; <br>
Sent: October 21, 2025 12:23 AM<br>
To: Paul Austin &lt;<a href=3D"mailto:paul.austin@automutatio.com" target=
=3D"_blank">paul.austin@automutatio.com</a>&gt;<br>
Cc: <a href=3D"mailto:pgsql-general@lists.postgresql.org" target=3D"_blank"=
>pgsql-general@lists.postgresql.org</a><br>
Subject: Re: Extend CREATE POLICY to add IF EXISTS<br>
<br>
On 2025-Oct-20, Paul Austin wrote:<br>
<br>
&gt; Adrian,<br>
&gt; <br>
&gt; The drop policy IF EXISTS does work.<br>
&gt; <br>
&gt; But it would be nice to have the IF NOT EXISTS on CREATE POLICY so I <=
br>
&gt; don&#39;t need to do a drop and create.<br>
<br>
How would CREATE IF NOT EXISTS handle the case of an existing policy that d=
oesn&#39;t match the one you want?=C2=A0 I think it would just silently not=
 do anything, and in that case you can&#39;t really rely on it, can you?=C2=
=A0 So your script would have to extract the current policy, compare with t=
he one you want (how?) and then maybe drop it and create it anew, or leave =
it alone.=C2=A0 Is this really useful?<br>
<br>
I think what you&#39;d really appreciate is CREATE OR REPLACE: if the polic=
y exists and matches the one you ask for, then don&#39;t do anything; but o=
therwise throw it away and create it anew.=C2=A0 We have this for views, an=
d it allows for things like adding more columns than the original view had.=
<br>
<br>
BTW, the pattern DROP IF EXISTS / CREATE is a bit nasty, because there exis=
ts a period in between where no policy exists, which could be a security ho=
le.=C2=A0 Unless you use an explicit transaction block.<br>
<br>
-- <br>
=C3=81lvaro Herrera=C2=A0 =C2=A0 =C2=A0 =C2=A0 Breisgau, Deutschland=C2=A0 =
=E2=80=94=C2=A0 <a href=3D"https://www.EnterpriseDB.com/" rel=3D"noreferrer=
" target=3D"_blank">https://www.EnterpriseDB.com/</a><br>
&quot;I love the Postgres community. It&#39;s all about doing things _prope=
rly_. :-)&quot;<br>
(David Garamond)<br>
</blockquote></div><div><br clear=3D"all"></div><div><br></div><span class=
=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_s=
ignature"><div dir=3D"ltr">Death to &lt;Redacted&gt;, and butter sauce.<div=
>Don&#39;t boil me, I&#39;m still alive.<br><div><div>&lt;Redacted&gt; lobs=
ter!</div></div></div></div></div></div>

--00000000000084baa60641abfb9a--