The documentation on policies (https://www.postgresql.org/docs/current/sql-createpolicy.html) says for upserts: Note that INSERT with ON CONFLICT DO UPDATE checks INSERT policies' WITH CHECK expressions only for rows appended to the relation by the INSERT path.
In this case, the update path is taken, but the values can only be upserted after adding an insert policy: root@4284b66b43be:/# psql -h localhost -U postgres psql (18.1 (Debian 18.1-1.pgdg13+2)) Type "help" for help.
postgres=# create table t (a integer primary key); CREATE TABLE postgres=# insert into t values (1), (2), (3); INSERT 0 3 postgres=# create role policy_role; CREATE ROLE postgres=# grant select, insert, update, delete on t to policy_role; GRANT postgres=# alter table t enable row level security; ALTER TABLE postgres=# create policy t_aLess4 on t for update using (true) with check (a < 4); CREATE POLICY postgres=# create policy t_select on t for select using (true); CREATE POLICY postgres=# set role policy_role; SET postgres=> select * from t order by a; a --- 1 2 3 (3 rows)
postgres=> insert into t values (1) on conflict (a) do update set a = 0; ERROR: new row violates row-level security policy for table "t"
I don’t think this is a bug in RLS, because the RLS system intentionally validates whether an INSERT policy exists before performing INSERT … ON CONFLICT DO UPDATE. With RLS enabled and no INSERT policy defined, postgres denies the statement before reaching the UPDATE path.
The documentation wording about only applying INSERT policies’ WITH CHECK expressions to rows “appended by the INSERT path” can be interpreted as implying that UPSERT doesn’t require INSERT permissions if the conflict goes to update, but in practice postgres enforces INSERT policy presence first. Clarifying that requirement in the documentation would help future readers avoid confusion.
