public inbox for [email protected]
help / color / mirror / Atom feedFrom: Pavel Stehule <[email protected]>
To: Dmitry Dolgov <[email protected]>
Cc: Laurenz Albe <[email protected]>
Cc: Erik Rijkers <[email protected]>
Cc: Michael Paquier <[email protected]>
Cc: Amit Kapila <[email protected]>
Cc: DUVAL REMI <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: proposal: schema variables
Date: Mon, 9 Dec 2024 17:54:26 +0100
Message-ID: <CAFj8pRBWqEb8i6WmrF_Xh64=48GtisKijgczMv7HTTpe4GswuA@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAFj8pRC+hPCc2X88xC=pTJoqmVPApDsageZOMyqaxi5788WxHA@mail.gmail.com>
<CAFj8pRDJ9cq00VYSHxs6LsoHNWjhYXyWWBtV6UgeWwhs0AHa9A@mail.gmail.com>
<CAFj8pRBPXTcw_3fpKtgVthV2+9rZGhxitZ40DnAwCrK601TZZg@mail.gmail.com>
<ndtfl4tsnpkb7m7hwvnmlpsascpgd3a7xvjmjhtxffsbrgygtm@4du6zsmnnwq5>
<CAFj8pRAu4XvNCGu1751t=2YEqLqTjDA3FavMExm2S0KYQq=DdQ@mail.gmail.com>
<CAFj8pRAsEoeZv0HEnA8CKgFKDSQ-wYw18Os1vdksWCV7ez2bVw@mail.gmail.com>
<3chredgnjcmccym2kczawfih226b4ac6co7p6z4jeofevrcosi@mrsxkx2x2c65>
<CAFj8pRBoWPDTOwn5FmMzc+1qiopw+N04U26nviOdF61fs8A2wQ@mail.gmail.com>
<stckyvkl4yyzvgjsaawojs3xikke7mmds5bhv7l7qerclywywk@h4v4n43xm6u2>
<CAFj8pRB_E1GM_YGT-ti4bXka6mhLdAAFeTe+BHgHFYC+qb-76g@mail.gmail.com>
<[email protected]>
Hi
st 20. 11. 2024 v 21:14 odesÃlatel Dmitry Dolgov <[email protected]>
napsal:
> > On Tue, Nov 19, 2024 at 08:14:01PM +0100, Pavel Stehule wrote:
> > Hi
> >
> > I wrote POC of VARIABLE(varname) syntax support
>
> Thanks, the results look good. I'm still opposed the idea of having a
> warning, and think it has to be an error -- but it's my subjective
> opinion. Lets see if there are more votes on that topic.
>
Maybe the warning of usage of unfenced variables can be changed (enhanced)
to some different mechanism that can be more restrictive (and safer), but I
think it can still be user friendly.
My idea is based on assumption so users with knowledge of stored procedures
know variables and related risks (and know tools how to minimize risks),
and for other people the risk is higher and we should force usage of
variable fences. I think we can force usage of variable fences at query
runtime, when query is not executed from the SPI environment. This
behaviour can be enabled by default, but can be optionally disabled.
CREATE VARIABLE s.x AS int; -- allowed when user has create right on schema
s
CREATE VIEW v1 AS SELECT x; -- no problem, the check is dynamic
(execution), not static
CREATE VIEW v2 AS SELECT VARIABLE(x); -- no problem
SELECT x; -- fails on access to unfenced variable
SELECT * FROM v1; -- fails on access to unfenced variable
SELECT * FROM v2; -- ok
but inside pl, this check will not be active, and then with default setting
I can write an code like
LET var = 10; -- fencing is not allowed there, and there is not any
collision
DO $$
BEGIN
RAISE NOTICE 'var=%', var;
RAISE NOTICE 'var=%', (SELECT * FROM v1); --is ok here too
END;
$$;
Outside PL the fencing can be required, inside PL the fencing can be
optional. With this proposal we can limit the possible risk usage of
unfenced variables only in PL context, and the behaviour can be very
similar to PL/SQL or SQL/PSM. This check is only a runtime check, so it has
no impact on any DDL statement. It doesn't change the syntax or behavior,
so it can be implemented subsequently - it is just a safeguard against
unwanted usage of variables in an environment, where users have no
possibility to use variables already. I can imagine that this check
"allow_unfenced_variables" can have three values (everywhere, pl, nowhere)
and the default can be pl. The results of queries don't depend on the
current setting of this check. For all values for all possible queries and
situations, the result is the same (when it is finished). But sometimes,
the check can break the execution - in similar meaning like access rights.
All previous proposed warnings can be unchanged.
Comments, notes?
Regards
Pavel
view thread (439+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: proposal: schema variables
In-Reply-To: <CAFj8pRBWqEb8i6WmrF_Xh64=48GtisKijgczMv7HTTpe4GswuA@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox