INSERT statement returns a policy violation (USING expression)

Question

I am using AWS Aurora Postgres 14 (14.5) and have this table set-up (with the help of Diesel in Rust) ->

create table contacts (
    email TEXT NOT NULL,
    user_id TEXT NOT NULL,
    contact_data JSONB,
    user_groups TEXT[],
    tenant_groups TEXT[],
    tags TEXT[],
    PRIMARY KEY (email, user_id)
);

With policies ->

ALTER TABLE contacts FORCE ROW LEVEL SECURITY;
CREATE POLICY select_contacts_policy ON contacts 
FOR SELECT
USING (user_id = current_setting('myapp.user_id'));
ALTER TABLE contacts ENABLE ROW LEVEL SECURITY;

CREATE POLICY insert_contacts_policy ON contacts 
FOR INSERT
WITH CHECK (true);
ALTER TABLE contacts ENABLE ROW LEVEL SECURITY;

While inserting new data to this table, I first set a session parameter called user_id (a string) (using Diesel's sql_query function)->

sql_query(format!("SET SESSION myapp.user_id = '{user_id}';")).execute(pg_conn)?;

Then I insert using diesel ->

diesel::insert_into(contacts::table)
    .values(&contacts) # a custom struct
    .on_conflict((contacts::email, contacts::user_id))
    .do_update()
    .set(&contacts)
    .execute(pg_conn)?;

After this query, the user_id is reverted to a default ->

sql_query("SET SESSION myapp.user_id = -1;").execute(pg_conn)?;

The diesel function call translates to this SQL Query (got it from postgres logs) ->

INSERT INTO "contacts" ("email", "user_id", "contact_data", "user_groups", "tenant_groups", "tags") VALUES ($1, $2, $3, $4, $5, $6) ON CONFLICT ("email", "user_id") DO UPDATE SET "email" = $7, "user_id" = $8, "contact_data" = $9, "user_groups" = $10, "tenant_groups" = $11, "tags" = $12

Upon running this query, I get this error message when there is a conflict and the update branch is triggered ->

new row violates row-level security policy (USING expression) for table "contacts"

I have a somewhat similar (without multiple users but the same schema) postgres installation (v 14.8) on my local which is able to upsert without any issues.

Additional information ->

The user working on this is a different one called backend user

The access privileges -> Snapshot of access privileges for the contacts table

I tried to run an EXPLAIN ANALYZE query and log it to see where the error occurs but that query results in policy violation before completion. If I run without the ANALYZE flag, it just prints the query plan (but I want to know where and what is resulting in that violation)

Please clarify: do I understand that correctly that you are running the same statement twice, with different settings for `myapp.user_id`? It would be easier if you could reproduce the problem in pure SQL, so that I can try it out easily. — Laurenz Albe, Jun 06 '23 at 06:26
@LaurenzAlbe I am setting a session before each `INSERT` statement using `SET SESSION`. Once the upsert is done, I am reverting back to the default value for `user_id`. If another user sends a request with their `user_id`, the whole process repeats. — parmesant, Jun 06 '23 at 11:14
@LaurenzAlbe You could try running the three commands in succession and that would replicate the flow. I am running them as a shell command using `psql` since the backend user doesn't have a database of its own to connect. — parmesant, Jun 06 '23 at 11:25

score 2 · Accepted Answer · answered Jun 06 '23 at 12:30

The problem here is the ON CONFLICT ... DO UPDATE. A plain INSERT or an INSERT ... ON CONFLICT DO NOTHING would succeed.

You can only update rows that you can see, that is, rows for which you have a FOR SELECT policy, as the documentation describes:

[...] queries that require SELECT permissions, such as UPDATE, will also only see those records that are allowed by the SELECT policy.

So it is the FOR SELECT policy and the (missing) FOR UPDATE policy that give you trouble here. This is somewhat surprising, since one could argue that you should only get that error if there is a conflict during the INSERT. However, to determine whether there is a conflict or not, you already need to be able to SELECT from the table. Adding a FOR SELECT policy would make the statement succeed if there is no conflict. If there is a conflict, you need an additional FOR UPDATE policy.

You'll have to add policies that allow the inserting role unconditional updates and selects:

CREATE POLICY inserter_may_select ON contacts FOR SELECT TO inserter
   USING (TRUE);
CREATE POLICY inserter_may_update ON contacts FOR UPDATE TO inserter
   USING (TRUE);

Adding a `FOR UPDATE` policy worked! For my usecase, I set it up exactly as the `SELECT` policy. I still don't understand why the upsert worked on my local without the update policy but that's something for me to figure out. Thanks @Laurenz Albe! — parmesant, Jun 06 '23 at 15:18

INSERT statement returns a policy violation (USING expression)

1 Answers1