Doesn't CREATE OR REPLACE have limitations based on parameter-list changes?
On Tue, Oct 21, 2025 at 10:08 AM Paul Austin <[email protected]> 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 <[email protected]> > Sent: October 21, 2025 12:23 AM > To: Paul Austin <[email protected]> > Cc: [email protected] > 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!
