Re: Schema variables - new implementation for Postgres 15

Hi

ne 26. 3. 2023 v 19:53 odesílatel Dmitry Dolgov <9erthalion6(at)gmail(dot)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(at)xxx(dot)org
t2role yyy t2role(at)yyy(dot)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(at)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(at)xxx(dot)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(at)yyy(dot)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