Re: Extend CREATE POLICY to add IF EXISTS

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

On Tue, Oct 21, 2025 at 10:08 AM Paul Austin <paul(dot)austin(at)automutatio(dot)com>
wrote:

> Álvaro,
>
> Yes, a CREATE OR REPLACE would also be useful.
>
> However, the CREATE IF NOT EXISTS is also useful when you aren't concerned
> that the POLICY is going to change. Same with the existing CREATE TABLE IF
> 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: Álvaro Herrera <alvherre(at)kurilemu(dot)de>
> Sent: October 21, 2025 12:23 AM
> To: Paul Austin <paul(dot)austin(at)automutatio(dot)com>
> Cc: pgsql-general(at)lists(dot)postgresql(dot)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.
>
> --
> Álvaro Herrera Breisgau, Deutschland —
> https://www.EnterpriseDB.com/
> "I love the Postgres community. It's all about doing things _properly_.
> :-)"
> (David Garamond)
>

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