Re: Schema variables - new implementation for Postgres 15

Hi

ne 26. 3. 2023 v 19:53 odesílatel Dmitry Dolgov <9erthalion6@gmail.com>
napsal:

> > On Sun, Mar 26, 2023 at 07:32:05PM +0800, Julien Rouhaud wrote:
> > Hi,
> >
> > I just have a few minor wording improvements for the various comments /
> > documentation you quoted.
>
> Talking about documentation I've noticed that the implementation
> contains few limitations, that are not mentioned in the docs. Examples
> are WITH queries:
>
>     WITH x AS (LET public.svar = 100) SELECT * FROM x;
>     ERROR:  LET not supported in WITH query
>

 The LET statement doesn't support the RETURNING clause, so using inside
CTE does not make any sense.

Do you have some tips, where this behaviour should be mentioned?


> and using with set-returning functions (haven't found any related tests).
>

There it is:

+CREATE VARIABLE public.svar AS int;
+-- should be ok
+LET public.svar = generate_series(1, 1);
+-- should fail
+LET public.svar = generate_series(1, 2);
+ERROR:  expression returned more than one row
+LET public.svar = generate_series(1, 0);
+ERROR:  expression returned no rows
+DROP VARIABLE public.svar;


>
> Another small note is about this change in the rowsecurity:
>
>         /*
>     -    * For SELECT, UPDATE and DELETE, add security quals to enforce
> the USING
>     -    * policies.  These security quals control access to existing
> table rows.
>     -    * Restrictive policies are combined together using AND, and
> permissive
>     -    * policies are combined together using OR.
>     +    * For SELECT, LET, UPDATE and DELETE, add security quals to
> enforce the
>     +    * USING policies.  These security quals control access to
> existing table
>     +    * rows. Restrictive policies are combined together using AND, and
>     +    * permissive policies are combined together using OR.
>          */
>
> From this commentary one may think that LET command supports row level
> security, but I don't see it being implemented. A wrong commentary?
>

I don't think so.  The row level security should be supported. I tested it
on example from doc:

CREATE TABLE public.accounts (
    manager text,
    company text,
    contact_email text
);

CREATE VARIABLE public.v AS text;

COPY public.accounts (manager, company, contact_email) FROM stdin;
t1role xxx t1role@xxx.org
t2role yyy t2role@yyy.org
\.

CREATE POLICY account_managers ON public.accounts USING ((manager =
CURRENT_USER));
ALTER TABLE public.accounts ENABLE ROW LEVEL SECURITY;

GRANT SELECT,INSERT ON TABLE public.accounts TO t1role;
GRANT SELECT,INSERT ON TABLE public.accounts TO t2role;

GRANT ALL ON VARIABLE public.v TO t1role;
GRANT ALL ON VARIABLE public.v TO t2role;


[pavel@localhost postgresql.master]$ psql
Assertions: on
psql (16devel)
Type "help" for help.

(2023-03-28 21:32:33) postgres=# set role to t1role;
SET
(2023-03-28 21:32:40) postgres=# select * from accounts ;
┌─────────┬─────────┬────────────────┐
│ manager │ company │ contact_email  │
╞═════════╪═════════╪════════════════╡
│ t1role  │ xxx     │ t1role@xxx.org │
└─────────┴─────────┴────────────────┘
(1 row)

(2023-03-28 21:32:45) postgres=# let v = (select company from accounts);
LET
(2023-03-28 21:32:58) postgres=# select v;
┌─────┐
│  v  │
╞═════╡
│ xxx │
└─────┘
(1 row)

(2023-03-28 21:33:03) postgres=# set role to default;
SET
(2023-03-28 21:33:12) postgres=# set role to t2role;
SET
(2023-03-28 21:33:19) postgres=# select * from accounts ;
┌─────────┬─────────┬────────────────┐
│ manager │ company │ contact_email  │
╞═════════╪═════════╪════════════════╡
│ t2role  │ yyy     │ t2role@yyy.org │
└─────────┴─────────┴────────────────┘
(1 row)

(2023-03-28 21:33:22) postgres=# let v = (select company from accounts);
LET
(2023-03-28 21:33:26) postgres=# select v;
┌─────┐
│  v  │
╞═════╡
│ yyy │
└─────┘
(1 row)


Regards

Pavel