public inbox for [email protected]
help / color / mirror / Atom feedRe: Extend CREATE POLICY to add IF EXISTS
3+ messages / 3 participants
[nested] [flat]
* Re: Extend CREATE POLICY to add IF EXISTS
@ 2025-10-21 07:23 Álvaro Herrera <[email protected]>
2025-10-21 14:08 ` RE: Extend CREATE POLICY to add IF EXISTS Paul Austin <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Álvaro Herrera @ 2025-10-21 07:23 UTC (permalink / raw)
To: Paul Austin <[email protected]>; +Cc: [email protected] <[email protected]>
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)
^ permalink raw reply [nested|flat] 3+ messages in thread
* RE: Extend CREATE POLICY to add IF EXISTS
2025-10-21 07:23 Re: Extend CREATE POLICY to add IF EXISTS Álvaro Herrera <[email protected]>
@ 2025-10-21 14:08 ` Paul Austin <[email protected]>
2025-10-21 14:26 ` Re: Extend CREATE POLICY to add IF EXISTS Ron Johnson <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Paul Austin @ 2025-10-21 14:08 UTC (permalink / raw)
To: Álvaro Herrera <[email protected]>; +Cc: [email protected] <[email protected]>
Á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)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Extend CREATE POLICY to add IF EXISTS
2025-10-21 07:23 Re: Extend CREATE POLICY to add IF EXISTS Álvaro Herrera <[email protected]>
2025-10-21 14:08 ` RE: Extend CREATE POLICY to add IF EXISTS Paul Austin <[email protected]>
@ 2025-10-21 14:26 ` Ron Johnson <[email protected]>
0 siblings, 0 replies; 3+ messages in thread
From: Ron Johnson @ 2025-10-21 14:26 UTC (permalink / raw)
To: Paul Austin <[email protected]>; +Cc: Álvaro Herrera <[email protected]>; [email protected] <[email protected]>
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!
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-10-21 14:26 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-10-21 07:23 Re: Extend CREATE POLICY to add IF EXISTS Álvaro Herrera <[email protected]>
2025-10-21 14:08 ` Paul Austin <[email protected]>
2025-10-21 14:26 ` Ron Johnson <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox