Re: proposal: schema variables

Hi

st 20. 11. 2024 v 21:14 odesílatel Dmitry Dolgov <9erthalion6@gmail.com>
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