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

On Sat, Sep 26, 2026 at 8:26 PM Kirill Reshke <reshkekirill(at)gmail(dot)com> wrote:
>
> But also, looks like with INSERT ON CONFLICT SELECT user now (with and
> without v1 patch) access data it would otherwise unable to:
>
> ```
> reshke=# create view vhs AS select * from t where i = 2;
> CREATE VIEW
> reshke=# grant select, insert on vhs to intruder ;
> GRANT
> reshke=# set role intruder ;
> SET
> reshke=> table t;
> ERROR: permission denied for table t
> reshke=> insert into vhs values(1) on conflict(i) do select returning *;
> i
> ---
> 1
> (1 row)
>
> INSERT 0 1
> ```
> So, on pg < 19 there is no way to retrieve i = 1 row from relation,
> and with pg19 there is a way. Not sure if we should guarantee
> something for non-sec-barrier views.
>
> Is that an issue?
>

Hi.
In pg < 19, we can use ON CONFLICT DO UPDATE retrieve all the
information where i = 1, see the producer below.
I think the last query you provided (insert into vhs values(1) on
conflict(i) do select returning *;) works as expected.

See https://www.postgresql.org/docs/current/rules-privileges.html
"""
This means that, except for security invoker views, users only need
the required privileges for the tables/views that are explicitly named
in their queries.
"""

Full producer follows.
-----------------------------
grant all on schema public to public;
create table t as select g as i, g as b from generate_series(1, 10) g;
create view vhs AS select * from t where i = 2;
create view vhssv with (security_invoker = true) AS select * from t where i = 3;
create role intruder login NOSUPERUSER;
grant select, insert, update on vhs to intruder;
alter table t add PRIMARY key(i);

set session authorization intruder;
table t;
table vhs;
begin;
insert into vhs values(1) on conflict(i) do update set i = 1, b = 11
returning *, old, new;
rollback;

begin;
insert into vhssv values(1) on conflict(i) do select returning *;
-- insert into vhssv values(1) on conflict(i) do update set i = 1, b =
11 returning *, old, new;
rollback;
-----------------------------

--
jian
https://www.enterprisedb.com/