CODEWITHSUNDEEP
postgresqlrls
Jon Taylor
Jon Taylor

Reputation: 1

Postgres RLS update conditions not met when column RLS relies on is updated

I have a Postgres Database with a single table profile in it. This table has two columns, an id and a username. I've enabled RLS and have applied the following policies.

 schemaname | tablename |         policyname          | permissive |  roles   |  cmd   |                            qual                            | with_check 
------------+-----------+-----------------------------+------------+----------+--------+------------------------------------------------------------+------------
 public     | profile   | select_user_or_group_policy | PERMISSIVE | {public} | SELECT | (current_setting('myapp.username'::text, true) = username) | 
 public     | profile   | unrestricted_insert         | PERMISSIVE | {public} | INSERT |                                                            | true
 public     | profile   | unrestricted_update         | PERMISSIVE | {public} | UPDATE | true                                                       | true
 public     | profile   | unrestricted_delete         | PERMISSIVE | {public} | DELETE | true                                                       |

myapp.username is set by an external application but for testing purposes gets set by calling

select set_config('myapp.username', 'user1', false);.

When selecting data from the DB I see it work as expected, only returning the rows where the username matches the myapp.username config. When updating though I'd expect the policy to allow me to update any record I can see at the start of the update. I'd like to be able to change the username to a different user, but I get the following error when doing so. I'm confused because the update policy is set to USING (true) WITH CHECK (true).

Table before update (the table has a primary key on the id column and no restrictions on the other column.

id username
1 user1
2 user2 
UPDATE profile SET username = 'user2' WHERE id = 1;
ERROR:  new row violates row-level security policy for table "profile"

Upvotes: 0

Views: 30

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247625

The problem is that the SELECT policy is also used to check the new row, so that you cannot perform an update that would make a row seem to vanish.

See the comments in the source (function get_row_security_policies() in src/backend/rewrite/rowsecurity.c):

/*
 * Get and add ALL/SELECT policies, if SELECT rights are required for
 * this relation (eg: when RETURNING is used).  These are added as WCO
 * policies rather than security quals to ensure that an error is
 * raised if a policy is violated; otherwise, we might end up silently
 * dropping rows to be added.
 */

Upvotes: 1

Related Questions