Bug: RLS policy FOR SELECT is used to check new rows
Try this as a user with NOBYPASSRLS:
CREATE TABLE rlsbug (deleted boolean);
INSERT INTO rlsbug VALUES (FALSE);
CREATE POLICY p_sel ON rlsbug FOR SELECT TO laurenz USING (NOT deleted);
CREATE POLICY p_upd ON rlsbug FOR UPDATE TO laurenz USING (TRUE);
ALTER TABLE rlsbug ENABLE ROW LEVEL SECURITY;
ALTER TABLE rlsbug FORCE ROW LEVEL SECURITY;
UPDATE rlsbug SET deleted = TRUE WHERE NOT deleted;
ERROR: new row violates row-level security policy for table "rlsbug"
I'd say that this error is wrong. The FOR SELECT policy should be applied
to the WHERE condition, but certainly not to check new rows.
Yours,
Laurenz Albe
On Tue, 24 Oct 2023 at 09:36, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
I'd say that this error is wrong. The FOR SELECT policy should be applied
to the WHERE condition, but certainly not to check new rows.
Yes, I had the same thought recently. I would say that the SELECT
policies should only be used to check new rows if the UPDATE has a
RETURNING clause and SELECT permissions are required on the target
relation.
In other words, it should be OK to UPDATE a row to new values that are
not visible according to the table's SELECT policies, provided that
the UPDATE command does not attempt to return those new values. That
would be consistent with what we do for INSERT.
Note, that the current behaviour goes back a long way, though it's not
quite clear whether this was intentional [1]https://github.com/postgres/postgres/commit/7d8db3e8f37aec9d252353904e77381a18a2fa9f.
[1]: https://github.com/postgres/postgres/commit/7d8db3e8f37aec9d252353904e77381a18a2fa9f
Regards,
Dean
Dean Rasheed <dean.a.rasheed@gmail.com> writes:
On Tue, 24 Oct 2023 at 09:36, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
I'd say that this error is wrong. The FOR SELECT policy should be applied
to the WHERE condition, but certainly not to check new rows.
Yes, I had the same thought recently. I would say that the SELECT
policies should only be used to check new rows if the UPDATE has a
RETURNING clause and SELECT permissions are required on the target
relation.
In other words, it should be OK to UPDATE a row to new values that are
not visible according to the table's SELECT policies, provided that
the UPDATE command does not attempt to return those new values. That
would be consistent with what we do for INSERT.
Note, that the current behaviour goes back a long way, though it's not
quite clear whether this was intentional [1].
I'm fairly sure that it was intentional, but I don't recall the
reasoning; perhaps Stephen does. In any case, I grasp your point
that maybe we should distinguish RETURNING from not-RETURNING cases.
regards, tom lane
On Tue, 2023-10-24 at 11:59 -0400, Tom Lane wrote:
I'm fairly sure that it was intentional, but I don't recall the
reasoning; perhaps Stephen does. In any case, I grasp your point
that maybe we should distinguish RETURNING from not-RETURNING cases.
Perhaps the idea is that if there are constraints involved, the failure
or success of an INSERT/UPDATE/DELETE could leak information that you
don't have privileges to read.
Regards,
Jeff Davis
On Tue, Oct 24, 2023 at 1:46 PM Jeff Davis <pgsql@j-davis.com> wrote:
Perhaps the idea is that if there are constraints involved, the failure
or success of an INSERT/UPDATE/DELETE could leak information that you
don't have privileges to read.
My recollection of this topic is pretty hazy, but like Tom, I seem to
remember it being intentional, and I think the reason had something to
do with wanting the slice of a RLS-protect table that you can see to
feel like a complete table. When you update a row in a table all of
which is visible to you, the updated row can never vanish as a result
of that update, so it was thought, if I remember correctly, that this
should also be true here. It's also similar to what happens if an
updatable view has WITH CHECK OPTION, and I think that was part of the
precedent as well. I don't know whether or not the constraint issue
that you mention here was also part of the concern, but it may have
been. This was all quite a while ago...
--
Robert Haas
EDB: http://www.enterprisedb.com
Greetings,
On Tue, Oct 24, 2023 at 14:42 Robert Haas <robertmhaas@gmail.com> wrote:
On Tue, Oct 24, 2023 at 1:46 PM Jeff Davis <pgsql@j-davis.com> wrote:
Perhaps the idea is that if there are constraints involved, the failure
or success of an INSERT/UPDATE/DELETE could leak information that you
don't have privileges to read.My recollection of this topic is pretty hazy, but like Tom, I seem to
remember it being intentional, and I think the reason had something to
do with wanting the slice of a RLS-protect table that you can see to
feel like a complete table. When you update a row in a table all of
which is visible to you, the updated row can never vanish as a result
of that update, so it was thought, if I remember correctly, that this
should also be true here. It's also similar to what happens if an
updatable view has WITH CHECK OPTION, and I think that was part of the
precedent as well. I don't know whether or not the constraint issue
that you mention here was also part of the concern, but it may have
been. This was all quite a while ago...
Yes, having it be similar to a view WITH CHECK OPTION was intentional, also
on not wishing for things to be able to disappear or to not get saved. The
risk of a constraint possibly causing the leak of information is better
than either having data just thrown away or having the constraint not
provide the guarantee it’s supposed to …
Thanks,
Stephen
(On my phone at an event currently, sorry for not digging in deeper on
this..)
Show quoted text
On Tue, 2023-10-24 at 15:05 -0400, Stephen Frost wrote:
On Tue, Oct 24, 2023 at 14:42 Robert Haas <robertmhaas@gmail.com> wrote:
On Tue, Oct 24, 2023 at 1:46 PM Jeff Davis <pgsql@j-davis.com> wrote:
Perhaps the idea is that if there are constraints involved, the failure
or success of an INSERT/UPDATE/DELETE could leak information that you
don't have privileges to read.My recollection of this topic is pretty hazy, but like Tom, I seem to
remember it being intentional, and I think the reason had something to
do with wanting the slice of a RLS-protect table that you can see to
feel like a complete table. When you update a row in a table all of
which is visible to you, the updated row can never vanish as a result
of that update, so it was thought, if I remember correctly, that this
should also be true here. It's also similar to what happens if an
updatable view has WITH CHECK OPTION, and I think that was part of the
precedent as well. I don't know whether or not the constraint issue
that you mention here was also part of the concern, but it may have
been. This was all quite a while ago...Yes, having it be similar to a view WITH CHECK OPTION was intentional,
also on not wishing for things to be able to disappear or to not get saved.
The risk of a constraint possibly causing the leak of information is better
than either having data just thrown away or having the constraint not
provide the guarantee it’s supposed to …
Thanks everybody for looking and remembering.
I can accept that the error is intentional, even though it violated the
POLA for me. I can buy into the argument that an UPDATE should not make
a row seem to vanish.
I cannot buy into the constraint argument. If the table owner wanted to
prevent you from causing a constraint violation error with a row you
cannot see, she wouldn't have given you a FOR UPDATE policy that allows
you to perform such an UPDATE.
Anyway, it is probably too late to change a behavior that has been like
that for a while and is not manifestly buggy.
Yours,
Laurenz Albe
On Wed, 2023-10-25 at 09:45 +0200, Laurenz Albe wrote:
I can accept that the error is intentional, even though it violated the
POLA for me. I can buy into the argument that an UPDATE should not make
a row seem to vanish.I cannot buy into the constraint argument. If the table owner wanted to
prevent you from causing a constraint violation error with a row you
cannot see, she wouldn't have given you a FOR UPDATE policy that allows
you to perform such an UPDATE.Anyway, it is probably too late to change a behavior that has been like
that for a while and is not manifestly buggy.
I have thought some more about this, and I believe that if FOR SELECT
policies are used to check new rows, you should be allowed to specify
WITH CHECK on FOR SELECT policies. Why not allow a user to specify
different conditions for fetching from a table and for new rows after
an UPDATE?
The attached patch does that. What so you think?
Yours,
Laurenz Albe
Attachments:
0001-Allow-WITH-CKECK-on-FOR-SELECT-policies.patchtext/x-patch; charset=UTF-8; name=0001-Allow-WITH-CKECK-on-FOR-SELECT-policies.patchDownload
From c1bf1cb39962690933e4a37af0ab8b00926a0020 Mon Sep 17 00:00:00 2001
From: Laurenz Albe <laurenz.albe@cybertec.at>
Date: Thu, 9 Nov 2023 16:09:10 +0100
Subject: [PATCH] Allow WITH CKECK on FOR SELECT policies
FOR SELECT or FOR ALL policies are used to check the new row
after an UPDATE, but you could not define a WITH CHECK clause
on FOR SELECT policies, so the USING clause was used.
This patch adds the capability to define WITH CHECK clauses on
FOR SELECT policies, which allows the user to specify different
criteria for fetching rows from the table and for new rows
after an UPDATE.
Author: Laurenz Albe
Discussion: https://postgr.es/m/aee893f1ec3ca8f62a0da2fc2f9f8b73920f9f9d.camel%40cybertec.at
---
doc/src/sgml/ref/create_policy.sgml | 35 +++++++++++++++--------
src/backend/commands/policy.c | 14 ++++-----
src/backend/rewrite/rowsecurity.c | 2 +-
src/test/regress/expected/rowsecurity.out | 14 +++++++++
src/test/regress/sql/rowsecurity.sql | 11 +++++++
5 files changed, 55 insertions(+), 21 deletions(-)
diff --git a/doc/src/sgml/ref/create_policy.sgml b/doc/src/sgml/ref/create_policy.sgml
index e76c342d3d..d473bb1094 100644
--- a/doc/src/sgml/ref/create_policy.sgml
+++ b/doc/src/sgml/ref/create_policy.sgml
@@ -82,7 +82,7 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable
<para>
For policies that can have both <literal>USING</literal>
- and <literal>WITH CHECK</literal> expressions (<literal>ALL</literal>
+ and <literal>WITH CHECK</literal> expressions (<literal>ALL</literal>, <literal>SELECT</literal>
and <literal>UPDATE</literal>), if no <literal>WITH CHECK</literal>
expression is defined, then the <literal>USING</literal> expression will be
used both to determine which rows are visible (normal
@@ -270,9 +270,11 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable
that require <literal>SELECT</literal> permissions, such as
<literal>UPDATE</literal>, will also only see those records
that are allowed by the <literal>SELECT</literal> policy.
- A <literal>SELECT</literal> policy cannot have a <literal>WITH
- CHECK</literal> expression, as it only applies in cases where
- records are being retrieved from the relation.
+ In addition, rows written by an <command>INSERT</command> or
+ <command>UPDATE</command> statement are checked against the
+ <literal>WITH CHECK</literal> expression of <literal>SELECT</literal>
+ policies on the table (or, if there is no <literal>WITH
+ CHECK</literal> expression, the <literal>USING</literal> expression).
</para>
</listitem>
</varlistentry>
@@ -402,14 +404,17 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable
<table id="sql-createpolicy-summary">
<title>Policies Applied by Command Type</title>
- <tgroup cols="6">
- <colspec colnum="4" colname="update-using"/>
- <colspec colnum="5" colname="update-check"/>
+ <tgroup cols="7">
+ <colspec colnum="2" colname="insert-using"/>
+ <colspec colnum="3" colname="insert-check"/>
+ <colspec colnum="5" colname="update-using"/>
+ <colspec colnum="6" colname="update-check"/>
+ <spanspec namest="insert-using" nameend="insert-check" spanname="insert"/>
<spanspec namest="update-using" nameend="update-check" spanname="update"/>
<thead>
<row>
<entry morerows="1">Command</entry>
- <entry><literal>SELECT/ALL policy</literal></entry>
+ <entry spanname="insert"><literal>SELECT/ALL policy</literal></entry>
<entry><literal>INSERT/ALL policy</literal></entry>
<entry spanname="update"><literal>UPDATE/ALL policy</literal></entry>
<entry><literal>DELETE/ALL policy</literal></entry>
@@ -417,6 +422,7 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable
<row>
<entry><literal>USING expression</literal></entry>
<entry><literal>WITH CHECK expression</literal></entry>
+ <entry><literal>WITH CHECK expression</literal></entry>
<entry><literal>USING expression</literal></entry>
<entry><literal>WITH CHECK expression</literal></entry>
<entry><literal>USING expression</literal></entry>
@@ -430,11 +436,13 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable
<entry>—</entry>
<entry>—</entry>
<entry>—</entry>
+ <entry>—</entry>
</row>
<row>
<entry><command>SELECT FOR UPDATE/SHARE</command></entry>
<entry>Existing row</entry>
<entry>—</entry>
+ <entry>—</entry>
<entry>Existing row</entry>
<entry>—</entry>
<entry>—</entry>
@@ -442,6 +450,7 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable
<row>
<entry><command>INSERT</command> / <command>MERGE ... THEN INSERT</command></entry>
<entry>—</entry>
+ <entry>—</entry>
<entry>New row</entry>
<entry>—</entry>
<entry>—</entry>
@@ -449,6 +458,7 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable
</row>
<row>
<entry><command>INSERT ... RETURNING</command></entry>
+ <entry>—</entry>
<entry>
New row <footnote id="rls-select-priv">
<para>
@@ -465,9 +475,8 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable
</row>
<row>
<entry><command>UPDATE</command> / <command>MERGE ... THEN UPDATE</command></entry>
- <entry>
- Existing & new rows <footnoteref linkend="rls-select-priv"/>
- </entry>
+ <entry>Existing row <footnoteref linkend="rls-select-priv"/></entry>
+ <entry>New row</entry>
<entry>—</entry>
<entry>Existing row</entry>
<entry>New row</entry>
@@ -481,11 +490,13 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable
<entry>—</entry>
<entry>—</entry>
<entry>—</entry>
+ <entry>—</entry>
<entry>Existing row</entry>
</row>
<row>
<entry><command>ON CONFLICT DO UPDATE</command></entry>
- <entry>Existing & new rows</entry>
+ <entry>Existing row</entry>
+ <entry>New row</entry>
<entry>—</entry>
<entry>Existing row</entry>
<entry>New row</entry>
diff --git a/src/backend/commands/policy.c b/src/backend/commands/policy.c
index 76a45e56bf..e3e19167fe 100644
--- a/src/backend/commands/policy.c
+++ b/src/backend/commands/policy.c
@@ -597,13 +597,12 @@ CreatePolicy(CreatePolicyStmt *stmt)
polcmd = parse_policy_command(stmt->cmd_name);
/*
- * If the command is SELECT or DELETE then WITH CHECK should be NULL.
+ * If the command is DELETE then WITH CHECK should be NULL.
*/
- if ((polcmd == ACL_SELECT_CHR || polcmd == ACL_DELETE_CHR)
- && stmt->with_check != NULL)
+ if (polcmd == ACL_DELETE_CHR && stmt->with_check != NULL)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("WITH CHECK cannot be applied to SELECT or DELETE")));
+ errmsg("WITH CHECK cannot be applied to DELETE")));
/*
* If the command is INSERT then WITH CHECK should be the only expression
@@ -899,13 +898,12 @@ AlterPolicy(AlterPolicyStmt *stmt)
polcmd = DatumGetChar(polcmd_datum);
/*
- * If the command is SELECT or DELETE then WITH CHECK should be NULL.
+ * If the command is DELETE then WITH CHECK should be NULL.
*/
- if ((polcmd == ACL_SELECT_CHR || polcmd == ACL_DELETE_CHR)
- && stmt->with_check != NULL)
+ if (polcmd == ACL_DELETE_CHR && stmt->with_check != NULL)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("only USING expression allowed for SELECT, DELETE")));
+ errmsg("only USING expression allowed for DELETE")));
/*
* If the command is INSERT then WITH CHECK should be the only expression
diff --git a/src/backend/rewrite/rowsecurity.c b/src/backend/rewrite/rowsecurity.c
index b1620e4625..8df5422583 100644
--- a/src/backend/rewrite/rowsecurity.c
+++ b/src/backend/rewrite/rowsecurity.c
@@ -308,7 +308,7 @@ get_row_security_policies(Query *root, RangeTblEntry *rte, int rt_index,
select_restrictive_policies,
withCheckOptions,
hasSubLinks,
- true);
+ false);
}
/*
diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out
index 6988128aa4..a43160cb0d 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -4308,6 +4308,20 @@ ERROR: new row violates row-level security policy for table "r1"
INSERT INTO r1 VALUES (10)
ON CONFLICT ON CONSTRAINT r1_pkey DO UPDATE SET a = 30;
ERROR: new row violates row-level security policy for table "r1"
+-- Add an explicit WITH CHECK clause to the FOR SELECT policy, so that the
+-- UPDATE that failed before can succeed
+ALTER POLICY p1 ON r1 WITH CHECK (true);
+BEGIN;
+UPDATE r1 SET a = 30 WHERE a = 10;
+ROLLBACK;
+BEGIN;
+UPDATE r1 SET a = 30 RETURNING *;
+ a
+----
+ 30
+(1 row)
+
+ROLLBACK;
DROP TABLE r1;
-- Check dependency handling
RESET SESSION AUTHORIZATION;
diff --git a/src/test/regress/sql/rowsecurity.sql b/src/test/regress/sql/rowsecurity.sql
index dec7340538..7ebf89e2ce 100644
--- a/src/test/regress/sql/rowsecurity.sql
+++ b/src/test/regress/sql/rowsecurity.sql
@@ -2030,6 +2030,17 @@ INSERT INTO r1 VALUES (10)
INSERT INTO r1 VALUES (10)
ON CONFLICT ON CONSTRAINT r1_pkey DO UPDATE SET a = 30;
+-- Add an explicit WITH CHECK clause to the FOR SELECT policy, so that the
+-- UPDATE that failed before can succeed
+ALTER POLICY p1 ON r1 WITH CHECK (true);
+
+BEGIN;
+UPDATE r1 SET a = 30 WHERE a = 10;
+ROLLBACK;
+BEGIN;
+UPDATE r1 SET a = 30 RETURNING *;
+ROLLBACK;
+
DROP TABLE r1;
-- Check dependency handling
--
2.41.0
On Thu, 9 Nov 2023 at 15:16, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
I have thought some more about this, and I believe that if FOR SELECT
policies are used to check new rows, you should be allowed to specify
WITH CHECK on FOR SELECT policies. Why not allow a user to specify
different conditions for fetching from a table and for new rows after
an UPDATE?The attached patch does that. What so you think?
So you'd be able to write policies that allowed you to do an
INSERT/UPDATE ... RETURNING, where the WITH CHECK part of the SELECT
policy allowed you see the new row, but then if you tried to SELECT it
later, the USING part of the policy might say no.
That seems pretty confusing. I would expect a row to either be visible
or not, consistently across all commands.
Regards,
Dean
On Thu, 2023-11-09 at 15:59 +0000, Dean Rasheed wrote:
On Thu, 9 Nov 2023 at 15:16, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
I have thought some more about this, and I believe that if FOR SELECT
policies are used to check new rows, you should be allowed to specify
WITH CHECK on FOR SELECT policies. Why not allow a user to specify
different conditions for fetching from a table and for new rows after
an UPDATE?The attached patch does that. What so you think?
So you'd be able to write policies that allowed you to do an
INSERT/UPDATE ... RETURNING, where the WITH CHECK part of the SELECT
policy allowed you see the new row, but then if you tried to SELECT it
later, the USING part of the policy might say no.That seems pretty confusing. I would expect a row to either be visible
or not, consistently across all commands.
I think it can be useful to allow a user an UPDATE where the result
does not satisfy the USING clause of the FOR SELECT policy.
True, it could surprise that you cannot SELECT something you just saw
with UPDATE ... RETURNING, but I would argue that these are different
operations.
The idea that an UPDATE should only produce rows you can SELECT is not
true today: if you run an UPDATE without a WHERE clause, you can
create rows you cannot see. The restriction is only on UPDATEs with
a WHERE clause. Weird, isn't it?
Yours,
Laurenz Albe
On Thu, 9 Nov 2023 at 18:55, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
I think it can be useful to allow a user an UPDATE where the result
does not satisfy the USING clause of the FOR SELECT policy.The idea that an UPDATE should only produce rows you can SELECT is not
true today: if you run an UPDATE without a WHERE clause, you can
create rows you cannot see. The restriction is only on UPDATEs with
a WHERE clause. Weird, isn't it?
That's true, but only if the UPDATE also doesn't have a RETURNING
clause. What I find weird about your proposal is that it would allow
an UPDATE ... RETURNING command to return something that would be
visible just that once, but then subsequently disappear. That seems
like a cure that's worse than the original disease that kicked off
this discussion.
As mentioned by others, the intention was that RLS behave like WITH
CHECK OPTION on an updatable view, so that new rows can't just
disappear. There are, however, 2 differences between the way it
currently works for RLS, and an updatable view:
1). RLS only does this for UPDATE commands. INSERT commands *can*
insert new rows that aren't visible, and so disappear.
2). It can't be turned off. The WITH CHECK OPTION on an updatable view
is an option that the user can choose to turn on or off. That's not
possible with RLS.
In a green field, I would say that it would be better to fix (1), so
that INSERT and UPDATE are consistent. However, I fear that it may be
too late for that, because any such change would risk breaking
existing RLS policy setups in subtle ways.
It might be possible to change (2) though, by adding a new table-level
option (similar to a view's WITH CHECK OPTION) that enabled or
disabled the checking of new rows for that table, and whose default
matched the current behaviour.
Before going too far down that route though, it is perhaps worth
asking whether this is something users really want. Is there a real
use-case for being able to UPDATE rows and have them disappear?
Regards,
Dean
On Fri, 2023-11-10 at 09:39 +0000, Dean Rasheed wrote:
On Thu, 9 Nov 2023 at 18:55, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
I think it can be useful to allow a user an UPDATE where the result
does not satisfy the USING clause of the FOR SELECT policy.The idea that an UPDATE should only produce rows you can SELECT is not
true today: if you run an UPDATE without a WHERE clause, you can
create rows you cannot see. The restriction is only on UPDATEs with
a WHERE clause. Weird, isn't it?That's true, but only if the UPDATE also doesn't have a RETURNING
clause. What I find weird about your proposal is that it would allow
an UPDATE ... RETURNING command to return something that would be
visible just that once, but then subsequently disappear. That seems
like a cure that's worse than the original disease that kicked off
this discussion.
What kicked off the discussion was my complaint that FOR SELECT
rules mess with UPDATE, so that's exactly what I would have liked:
an UPDATE that makes the rows vanish.
My naïve expectation was that FOR SELECT policies govern SELECT
and FOR UPDATE policies govern UPDATE. After all, there is a
WITH CHECK clause for FOR UPDATE policies that checks the result rows.
So, from my perspective, we should never have let FOR SELECT policies
mess with an UPDATE. But I am too late for that; such a change would
be way too invasive now. So I'd like to introduce a "back door" by
creating a FOR SELECT policy with WITH CHECK (TRUE).
As mentioned by others, the intention was that RLS behave like WITH
CHECK OPTION on an updatable view, so that new rows can't just
disappear. There are, however, 2 differences between the way it
currently works for RLS, and an updatable view:1). RLS only does this for UPDATE commands. INSERT commands *can*
insert new rows that aren't visible, and so disappear.2). It can't be turned off. The WITH CHECK OPTION on an updatable view
is an option that the user can choose to turn on or off. That's not
possible with RLS.
Right. Plus the above-mentioned fact that you can make rows vanish
with an UPDATE that has no WHERE.
It might be possible to change (2) though, by adding a new table-level
option (similar to a view's WITH CHECK OPTION) that enabled or
disabled the checking of new rows for that table, and whose default
matched the current behaviour.
That would be a viable solution.
Pro: it doesn't make the already hideously complicated RLS system
even more complicated.
Con: yet another storage option...
Before going too far down that route though, it is perhaps worth
asking whether this is something users really want. Is there a real
use-case for being able to UPDATE rows and have them disappear?
What triggered my investigation was this question:
https://stackoverflow.com/q/77346757/6464308
I personally don't have any stake in this. I just wanted a way to
make RLS behave more like I think it should.
Yours,
Laurenz Albe
On Fri, Nov 10, 2023 at 7:43 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
So, from my perspective, we should never have let FOR SELECT policies
mess with an UPDATE. But I am too late for that; such a change would
be way too invasive now. So I'd like to introduce a "back door" by
creating a FOR SELECT policy with WITH CHECK (TRUE).
In principle I see no problem with some kind of back door here, but
that seems like it might not be the right way to do it. I don't think
we want constant true to behave arbitrarily differently than any other
expression. Maybe that's not what you had in mind and I'm just not
seeing the full picture, though.
--
Robert Haas
EDB: http://www.enterprisedb.com
On Mon, 2023-11-13 at 12:57 -0500, Robert Haas wrote:
On Fri, Nov 10, 2023 at 7:43 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
So, from my perspective, we should never have let FOR SELECT policies
mess with an UPDATE. But I am too late for that; such a change would
be way too invasive now. So I'd like to introduce a "back door" by
creating a FOR SELECT policy with WITH CHECK (TRUE).In principle I see no problem with some kind of back door here, but
that seems like it might not be the right way to do it. I don't think
we want constant true to behave arbitrarily differently than any other
expression. Maybe that's not what you had in mind and I'm just not
seeing the full picture, though.
I experimented some more, and I think I see my mistake now.
Currently, the USING clause of FOR SELECT/ALL/UPDATE policies is
an *additional* restriction to the WITH CHECK clause.
So my suggestion of using the WITH CHECK clause *instead of*
the USING clause in FOR SELECT policies would be unprincipled.
Sorry for the noise.
Yours,
Laurenz Albe