Horton

Re: proposal: schema variables

Pavel Stehule <pavel.stehule@gmail.com>

View thread
From: Pavel Stehule <pavel.stehule@gmail.com>
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>
Date: 2024-12-14T15:40:42Z
Lists: pgsql-hackers, pgsql-performance

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Move WAL sequence code into its own file

  2. Add ExplainState argument to pg_plan_query() and planner().

  3. Don't include access/htup_details.h in executor/tuptable.h

  4. Refactor to avoid code duplication in transformPLAssignStmt.

  5. Avoid including commands/dbcommands.h in so many places

  6. Restrict psql meta-commands in plain-text dumps.

  7. Split func.sgml into more manageable pieces

  8. Fix squashing algorithm for query texts

  9. EXPLAIN: Always use two fractional digits for row counts.

  10. Preliminary refactoring of plpgsql expression construction.

  11. plpgsql: pure parser and reentrant scanner

  12. Add some sanity checks in executor for query ID reporting

  13. Fix misleading error message context

  14. Add macros for looping through a List without a ListCell.

Attachments

Hi

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

> 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.
>
>
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
>
>
>
>
>