pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #19359: Row level security: Upserts require insert policies in the update path
3+ messages / 3 participants
[nested] [flat]
* BUG #19359: Row level security: Upserts require insert policies in the update path
@ 2025-12-18 15:03 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 3+ messages in thread
From: PG Bug reporting form @ 2025-12-18 15:03 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: elena@cedardb.com
The following bug has been logged on the website:
Bug reference: 19359
Logged by: Elena Krippner
Email address: elena@cedardb.com
PostgreSQL version: 18.1
Operating system: Ubuntu 25.10
Description:
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"
postgres=> set role postgres;
SET
postgres=# create policy t_insert on t for insert with check (true);
CREATE POLICY
postgres=# set role policy_role;
SET
postgres=> insert into t values (1) on conflict (a) do update set a = 0;
INSERT 0 1
postgres=> select * from t order by a;
a
---
0
2
3
(3 rows)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #19359: Row level security: Upserts require insert policies in the update path
@ 2025-12-19 17:40 Srinath Reddy Sadipiralla <srinath2133@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 3+ messages in thread
From: Srinath Reddy Sadipiralla @ 2025-12-19 17:40 UTC (permalink / raw)
To: elena@cedardb.com; pgsql-bugs@lists.postgresql.org
Hi, Thanks for reporting!
On Fri, Dec 19, 2025 at 2:50 AM PG Bug reporting form <
noreply@postgresql.org> wrote:
> The following bug has been logged on the website:
>
> Bug reference: 19359
> Logged by: Elena Krippner
> Email address: elena@cedardb.com
> PostgreSQL version: 18.1
> Operating system: Ubuntu 25.10
> Description:
>
> 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.
--
Thanks,
Srinath Reddy Sadipiralla
EDB: https://www.enterprisedb.com/
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #19359: Row level security: Upserts require insert policies in the update path
@ 2025-12-19 23:12 Dean Rasheed <dean.a.rasheed@gmail.com>
parent: Srinath Reddy Sadipiralla <srinath2133@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Dean Rasheed @ 2025-12-19 23:12 UTC (permalink / raw)
To: Srinath Reddy Sadipiralla <srinath2133@gmail.com>; +Cc: elena@cedardb.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Fri, 19 Dec 2025, 17:41 Srinath Reddy Sadipiralla, <srinath2133@gmail.com>
wrote:
> Hi, Thanks for reporting!
>
> On Fri, Dec 19, 2025 at 2:50 AM PG Bug reporting form <
> noreply@postgresql.org> wrote:
>
>> The following bug has been logged on the website:
>>
>> Bug reference: 19359
>> Logged by: Elena Krippner
>> Email address: elena@cedardb.com
>> PostgreSQL version: 18.1
>> Operating system: Ubuntu 25.10
>> Description:
>>
>> 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
>>
>
> I don’t think this is a bug in RLS
>
Yes, I think RLS is behaving correctly here, and it was the documentation
that was misleading. This was addressed as part of commit 7dc4fa9. The new
version of that documentation, which will be part of the next set of
releases, can be seen here:
https://www.postgresql.org/docs/devel/sql-createpolicy.html
Regards,
Dean
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-12-19 23:12 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-12-18 15:03 BUG #19359: Row level security: Upserts require insert policies in the update path PG Bug reporting form <noreply@postgresql.org>
2025-12-19 17:40 ` Srinath Reddy Sadipiralla <srinath2133@gmail.com>
2025-12-19 23:12 ` Dean Rasheed <dean.a.rasheed@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox