postgresqlrow-level-securitysecurity-policy

PostgreSQL RLS explicit Policies

Are the below implementation of policies equivalent from a security point of view ?

Implicit:

CREATE POLICY test_access_policy ON test 
  TO PUBLIC 
  USING (id = (current_setting('rls.id'::TEXT))) 
  WITH CHECK (TRUE);

Explicit:

CREATE POLICY test_insert_policy ON test 
  FOR INSERT TO PUBLIC
  WITH CHECK (TRUE);   

CREATE POLICY test_select_policy ON test
  FOR SELECT TO PUBLIC 
  USING (id = (current_setting('rls.id'::TEXT)));  

CREATE POLICY test_update_policy ON test 
  FOR UPDATE TO PUBLIC
  USING (id = (current_setting('rls.id'::TEXT)));   

CREATE POLICY test_delete_policy ON test 
  FOR DELETE TO PUBLIC 
USING (id = (current_setting('rls.id'::TEXT)));

My concern is the UPDATE policy, as stated in the docs:

Any rows whose updated values do not pass the WITH CHECK expression will cause an error, and the entire command will be aborted. If only a USING clause is specified, then that clause will be used for both USING and WITH CHECK cases.

From my understanding the equivalent UPDATE policy for the implicit version (oneliner) is the following:

CREATE POLICY test_update_policy ON test 
  FOR UPDATE TO PUBLIC 
  USING (id = (current_setting('rls.id'::TEXT))) WITH CHECK (TRUE);

whereas the explicit version is:

CREATE POLICY test_update_policy ON test 
  FOR UPDATE TO PUBLIC 
  USING (id = (current_setting('rls.id'::TEXT))) WITH CHECK (id =
(current_setting('rls.id'::TEXT)));

After testing both cases I didn't find any security bridge am I missing something?

Solution

  • Your interpretation is correct, and you'd have to add WITH CHECK (TRUE) to the FOR UPDATE policy to have an equivalent definition.

    The difference is that WITH CHECK (TRUE) allows you to change the value to anything, and without it you will receive an error if the new row version does not match the condition.