Hi
ne 26. 3. 2023 v 19:53 odesílatel Dmitry Dolgov <9erthali...@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 t1r...@xxx.org
t2role yyy t2r...@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 │ t1r...@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 │ t2r...@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
