ON CONFLICT DO SELECT returns rows hidden by a view

From: shihao zhong <zhong950419(at)gmail(dot)com>
To: pgsql-hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Cc: dean(dot)a(dot)rasheed(at)gmail(dot)com, v(at)viktorh(dot)net, andreas(at)proxel(dot)se, jian he <jian(dot)universality(at)gmail(dot)com>
Subject: ON CONFLICT DO SELECT returns rows hidden by a view
Date: 2026-09-25 04:45:01
Message-ID: CAGRkXqTOpdNG-4azFmQ=xE5ZGFiB_zjd0JTpB1FepSMbeET8yg@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Kirill Reshke 2026-09-25 04:51:42 FDW RTE join pushdown fails to create plan with aggregates
Previous Message vignesh C 2026-09-25 04:38:54 Re: Up to 50x degradation in dblink performance when receiving notice traffic 19 vs 18