Re: Schema variables - new implementation for Postgres 15 - Mailing list pgsql-hackers
| From | Pavel Stehule |
|---|---|
| Subject | Re: Schema variables - new implementation for Postgres 15 |
| Date | |
| Msg-id | CAFj8pRCThnE2yTXWq-+nMpyt+S7LKGqeq7nkVpWnPbdt4-OBDQ@mail.gmail.com Whole thread Raw |
| In response to | Re: Schema variables - new implementation for Postgres 15 (Dmitry Dolgov <9erthalion6@gmail.com>) |
| Responses |
Re: Schema variables - new implementation for Postgres 15
|
| List | pgsql-hackers |
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;
+-- 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
);
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
\.
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 SELECT,INSERT ON TABLE public.accounts TO t2role;
GRANT ALL ON VARIABLE public.v TO t1role;
GRANT ALL ON VARIABLE public.v TO t2role;
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)
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
pgsql-hackers by date: