Re: proposal: schema variables

Hi

po 9. 12. 2024 v 17:54 odesílatel Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
napsal:

> Hi
>
> st 20. 11. 2024 v 21:14 odesílatel Dmitry Dolgov <9erthalion6(at)gmail(dot)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.
>
>
here is a implementation with dynamic variable fence usage guard (depends
on context)

(2024-12-14 16:34:13) postgres=# set
session_variables_use_fence_context_guard to nospi ;
SET
(2024-12-14 16:34:25) postgres=# create variable xx as int;
CREATE VARIABLE
(2024-12-14 16:34:32) postgres=# select xx;
ERROR: session variable "public.xx" is not used inside variable fence
DETAIL: There is a risk of unwanted usage of session variable.
HINT: Use variable fence "VARIABLE(varname) for access to variable".
(2024-12-14 16:34:38) postgres=# let xx = 20;
LET
(2024-12-14 16:34:42) postgres=# select variable(xx);
┌────┐
│ xx │
╞════╡
│ 20 │
└────┘
(1 row)

(2024-12-14 16:34:48) postgres=# do $$
postgres$# begin
postgres$# raise notice '%', xx;
postgres$# end;
postgres$# $$;
NOTICE: 20
DO

Regards

Pavel

> Comments, notes?
>
> Regards
>
> Pavel
>
>
>
>
>