On Sat, Sep 26, 2026 at 8:26 PM Kirill Reshke <[email protected]> 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/
