Re: row level security on conflict do update

On Tue, Apr 26, 2022 at 9:44 AM alias <postgres(dot)rocks(at)gmail(dot)com> wrote:

>
>> 723
>> <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l723>
>> -- Violates USING qual for UPDATE policy p3.
>> 724
>> <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l724>
>> --
>> 725
>> <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l725>
>> -- UPDATE path is taken, but UPDATE fails purely because *existing* row to be
>> 726
>> <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l726>
>> -- updated is not a "novel"/cid 11 (row is not leaked, even though we have
>> 727
>> <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l727>
>> -- SELECT privileges sufficient to see the row in this instance):
>> 728
>> <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l728>
>> INSERT INTO document VALUES (33, 22, 1, 'regress_rls_bob', 'okay science fiction'); -- preparation for next statement
>> 729
>> <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l729>
>> INSERT INTO document VALUES (33, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_bob', 'Some novel, replaces sci-fi') -- takes UPDATE path
>> 730
>> <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l730>
>> ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle;
>> 731
>> <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l731>
>> -- Fine (we UPDATE, since INSERT WCOs and UPDATE security barrier quals + WCOs
>> 732
>> <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l732>
>> -- not violated):
>> 733
>> <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l733>
>> INSERT INTO document VALUES (2, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_bob', 'my first novel')
>> 734
>> <https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rowsecurity.sql;h=febf3cc4cf10499eb3a0ccb1c16afe1509033a05;hb=7103ebb7aae8ab8076b7e85f335ceb8fe799097c#l734>
>> ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle RETURNING *;
>
>
> POLICY "p3" FOR UPDATE
>> USING ((cid = ( SELECT category.cid
>> FROM category
>> WHERE (category.cname = 'novel'::text))))
>> WITH CHECK ((dauthor = CURRENT_USER))
>
> row level security p3, if I understand correctly: the *dauthor *shall be
> *currernt_user*
> and updated cid value shall not be *11*
>

The proposed record must have a dauthor value equal to current_user. That
is the only restriction.

However, the only rows eligible for updating are novels (cid = 11).

With this combination it is possible for an author to update existing
novels authored by someone else to instead be authored by themself.

It is not possible for them to update non-novels authored by themself, or
anyone else.

If it is possible to perform the update the effect of the update must
result in the dauthor value being made equal to current_user or the update
will fail.

Line 729 to 730 fails. That makes sense to me. but I don't get the line *733
> to 734. *
> Also in the comment section, what does `quals + WCOs` mean?
>
>
WCO := With Check Option clause(the word option is implicit in the actual
clause name)
+ := And
Security Barrier Quals := Using clause

It is basically saying: "both RLS restriction types".

What don't you get about 733/734?

Inserting a row 2 (with valid dauthor value), row 2 exists so move to on
conflict update, row 2 is visible to the update because its cid is 11,
perform update since the new row will have a valid dauthor value.

David J.