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

On 25 Sep 2026 at 12:48 +0200, Kirill Reshke <reshkekirill(at)gmail(dot)com>, wrote:
> 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
Well spotted Shihao. I agree with Kirill that this has to be fixed before release, a doc fix is not enough.
Please share your patch Shihao. I should be able to review within a week.

/Viktor Holmberg