Á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
withthe 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
myuse case stops the application prior to running this script and the database is in a private network.
-----Original Message-----
From: Álvaro 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
wouldjust silently not do anything, and in that case you can't really rely on it, can you? So your script would have
toextract the current policy, compare with the one you want (how?) and then maybe drop it and create it anew, or leave
italone. 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'tdo anything; but otherwise throw it away and create it anew. We have this for views, and it allows for things
likeadding 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)
