MIME-Version: 1.0
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>
 <20241120201313.t4wbhld4ktgielaf@erthalion.local>
In-Reply-To: <20241120201313.t4wbhld4ktgielaf@erthalion.local>
From: Pavel Stehule <pavel.stehule@gmail.com>
Date: Mon, 9 Dec 2024 17:54:26 +0100
Message-ID: 
 <CAFj8pRBWqEb8i6WmrF_Xh64=48GtisKijgczMv7HTTpe4GswuA@mail.gmail.com>
Subject: Re: proposal: schema variables
To: Dmitry Dolgov <9erthalion6@gmail.com>
Cc: Laurenz Albe <laurenz.albe@cybertec.at>, Erik Rijkers <er@xs4all.nl>,
	Michael Paquier <michael@paquier.xyz>, Amit Kapila <amit.kapila16@gmail.com>,
	DUVAL REMI <REMI.DUVAL@cheops.fr>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000e4cd6e0628d93925"
Archived-At: 
 <https://www.postgresql.org/message-id/CAFj8pRBWqEb8i6WmrF_Xh64%3D48GtisKijgczMv7HTTpe4GswuA%40mail.gmail.com>
Precedence: bulk

--000000000000e4cd6e0628d93925
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hi

st 20. 11. 2024 v 21:14 odes=C3=ADlatel Dmitry Dolgov <9erthalion6@gmail.co=
m>
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 =3D 10; -- fencing is not allowed there, and there is not any
collision
DO $$
BEGIN
  RAISE NOTICE 'var=3D%', var;
  RAISE NOTICE 'var=3D%', (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

--000000000000e4cd6e0628d93925
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Hi<br></div><br><div class=3D"gmail_quote gmail_quote=
_container"><div dir=3D"ltr" class=3D"gmail_attr">st 20. 11. 2024 v=C2=A021=
:14 odes=C3=ADlatel Dmitry Dolgov &lt;<a href=3D"mailto:9erthalion6@gmail.c=
om">9erthalion6@gmail.com</a>&gt; napsal:<br></div><blockquote class=3D"gma=
il_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,2=
04,204);padding-left:1ex">&gt; On Tue, Nov 19, 2024 at 08:14:01PM +0100, Pa=
vel Stehule wrote:<br>
&gt; Hi<br>
&gt;<br>
&gt; I wrote POC of VARIABLE(varname) syntax support<br>
<br>
Thanks, the results look good. I&#39;m still opposed the idea of having a<b=
r>
warning, and think it has to be an error -- but it&#39;s my subjective<br>
opinion. Lets see if there are more votes on that topic.<br></blockquote><d=
iv><br></div><div>Maybe the warning of usage of unfenced variables can be c=
hanged (enhanced) to some different mechanism that can be more restrictive =
(and safer), but I think it can still be user friendly.</div><div><br></div=
><div>My idea is based on assumption so users with knowledge of stored proc=
edures know=C2=A0 variables and related risks (and know tools how to minimi=
ze risks), and for other people the risk is higher and we should force usag=
e of variable fences. I think we can force usage of variable fences at quer=
y runtime, when query is not executed from the SPI environment. This behavi=
our can be enabled by default, but can be optionally disabled.=C2=A0</div><=
div><br></div><div>CREATE VARIABLE s.x AS int; -- allowed when user has cre=
ate right on schema s</div><div>CREATE VIEW v1 AS SELECT x; -- no problem, =
the check is dynamic (execution), not static<br></div><div>CREATE VIEW v2 A=
S SELECT VARIABLE(x); -- no problem</div><div><br></div><div>SELECT x; -- f=
ails on access to unfenced variable</div><div>SELECT * FROM v1; -- fails on=
 access to unfenced variable</div><div>SELECT * FROM v2; -- ok</div><div><b=
r></div><div>but inside pl, this check will not be active, and then with de=
fault setting I can write an code like</div><div><br></div><div>LET var =3D=
 10; -- fencing is not allowed there, and there is not any collision<br></d=
iv><div>DO $$</div><div>BEGIN</div><div>=C2=A0 RAISE NOTICE &#39;var=3D%=
9;, var;</div><div>=C2=A0 RAISE NOTICE &#39;var=3D%&#39;, (SELECT * FROM v1=
); --is ok here too<br></div><div>END;</div><div>$$;</div><div><br></div><d=
iv>Outside PL the fencing can be required, inside PL the fencing can be opt=
ional. 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/S=
QL or SQL/PSM. This check is only a runtime check, so it has no impact on a=
ny DDL statement. It doesn&#39;t change the syntax or behavior, so it can b=
e implemented subsequently - it is just a safeguard against unwanted usage =
of variables in an environment, where users have no possibility to use vari=
ables already. I can imagine that this check &quot;allow_unfenced_variables=
&quot; can have three values (everywhere, pl, nowhere) and the default can =
be pl. The results of queries don&#39;t depend on the current setting of th=
is check. For all values for all possible queries and situations, the resul=
t is the same (when it is finished). But sometimes, the check can break the=
 execution - in similar meaning like access rights. All previous proposed w=
arnings can be unchanged.</div><div><br></div><div>Comments, notes?</div><d=
iv><br></div><div>Regards</div><div><br></div><div>Pavel</div><div><br></di=
v><div><br></div><div><br></div><div>=C2=A0<br></div></div></div>

--000000000000e4cd6e0628d93925--