Re: ON CONFLICT DO SELECT returns rows hidden by a view

On Fri, 25 Sept 2026 at 09:45, shihao zhong <zhong950419(at)gmail(dot)com> wrote:
>
> Hi hackers,
>
> In 19, a user with only INSERT and SELECT on a security_barrier view can
> read rows that the view hides, with ON CONFLICT DO SELECT. Before 19 that
> user had no way to reach a hidden row, because DO UPDATE needs UPDATE.
>
> create view my_log with (security_barrier) as
> select * from documents where owner = current_user;
> grant select, insert on my_log to alice;
>
> -- as alice, row 1 belongs to bob
> insert into my_log (id, title) values (1, '')
> on conflict (id) do select returning *;
> id | owner | title | body
> ----+-------+---------------+----------------------
> 1 | bob | salary review | bob 180k, alice 120k
>
> With generate_series as the source and a rollback at the end, this reads
> the whole table. WITH CHECK OPTION does not help, since nothing is
> written. RLS is not affected, ExecOnConflictSelect() checks the existing
> row against the SELECT policies.
>
> The docs have the pieces. insert.sgml says DO SELECT needs only SELECT,
> and create_view.sgml says it "may similarly affect an existing row not
> visible through the view". They do not say that the row is returned, and
> the security_barrier section in rules.sgml does not mention ON CONFLICT
> at all. The create_view.sgml sentence was added as a doc fix during
> review [1], and I could not find any discussion of the new exposure for
> users without UPDATE.
>
> I see two ways to go. Keep the behavior and say it plainly in the
> security_barrier docs. Or check the existing row against the
> view's quals in DO SELECT, the same way RLS does, and raise an error when
> the row is hidden. I have a draft patch for the second, for views with a
> check option.
>
> Which way do people prefer? If it is the second, should it be a 19 open
> item?
>
> [1] https://postgr.es/m/d631b406-13b7-433e-8c0b-c6040c4b4663@Spark
>
> Regards,
> Shihao Zhong

I think that retrieving rows that configured to be unretrievable (in
< v19) is a regression and this needs both fix and being listed as
Open Item

--
Best regards,
Kirill Reshke