Postgresql row level security does not throw errors

Question

I have a Postgresql DB that I want to enable the Row-Level-Security on one of its tables.

Everything is working fine, except one thing, that is I want to have an error to be thrown when a user try to perform an update on a record that he doesn't have privileges on.

According to the docs:

check_expression: Any SQL conditional expression (returning boolean). The conditional expression cannot contain any aggregate or window functions. This expression will be used in INSERT and UPDATE queries against the table if row level security is enabled. Only rows for which the expression evaluates to true will be allowed. An error will be thrown if the expression evaluates to false or null for any of the records inserted or any of the records that result from the update. Note that the check_expression is evaluated against the proposed new contents of the row, not the original contents.

So I tried the following:

CREATE POLICY update_policy ON my_table FOR UPDATE TO editors
    USING (has_edit_privilege(user_name))
    WITH CHECK (has_edit_privilege(user_name));

I have also another policy for SELECT

CREATE POLICY select_policy ON my_table FOR SELECT TO editors 
    USING (has_select_privilege(user_name));

According to the docs, this should create a policy that would prevent any one from the editors ROLE to perform update on any record of my_table, and would throw an error when an update is performed. This works correctly, but no error is thrown.

What's my problem? Please help.

Answer 1

First, let me explain how row level security works when reading from the table:

You need not even have to define the policy – if there is no policy for a user on a table with row level security, the default is that the user can see nothing.

No error will be thrown when reading from a table.

If you want an error to be thrown, you could write a function

CREATE FUNCTION test_row(my_table) RETURNS boolean
   LANGUAGE plpgsql COST 10000 AS
$$BEGIN
   IF /* the user is not allowed */
   THEN
      RAISE EXCEPTION ...;
   END IF;
   RETURN TRUE;
$$END;$$;

Then use that function in your policy:

CREATE POLICY update_policy ON my_table FOR UPDATE TO editors
    USING (test_row(my_table));

I used COST 10000 for the function to tell PostgreSQL to test that condition after all other conditions, if possible.

This is not a fool-proof technique, but it will work for simple queries. What could happen in the general case is that some conditions get checked after the condition from the policy, and this could lead to errors with rows that wouldn't even be returned from the query.

But I think it is the best you can get when abusing the concept of row level security.

Now let me explain about writing to the table:

Any attempt to write a row to the table that does not satisfy the CHECK clause will cause an error as documented.

Now let's put it together:

Assuming that you define the policy like in your question:

• Any INSERT will cause an error.

• Any SELECT will return an empty result.

• Any UPDATE or DELETE will be successful, but affect no row. This is because these operations have to read (scan) the table before they modify data, and that scan will return no rows like in the SELECT case. Since no rows are affected by the data modification, no error is thrown.