0

With row level security enabled on tbl, but no policy set, as a user the UPDATE throws no error but does not modify the value:

UPDATE tbl SET col = 1;
=> Query returned successfully: 0 rows affected
SELECT col FROM tbl;
=> NULL

If row level security is disabled on tbl, as a user:

UPDATE tbl SET col = 1;
=> Query returned successfully: 1 row affected
SELECT col FROM tbl;
=> 1

I would expect to get something like: ERROR: permission denied for relation tbl instead of a kinda silent 0 rows affected.

Can I get something more explicit?

Using PostgreSQL 9.6.3 on CentOS 6.

Edit: Row level security is the cause, but I'm not clear why there is no error.

1

If you only enable RLS for a table, but do not specify rules, then a "deny all" rule is implicitly assumed.

Quote from the manual

When row security is enabled on a table [...] all normal access to the table for selecting rows or modifying rows must be allowed by a row security policy. [...] If no policy exists for the table, a default-deny policy is used, meaning that no rows are visible or can be modified.

(emphasis mine)

So since the policy makes no rows visible, this is why there is no error when UPDATEing and you get 0 rows affected.

0

There could be a trigger ON UPDATE that checks for the executing role and silently cancels the update for some, but not others:

Example:

...
IF pg_has_role('some_role', 'member') THEN
   RETURN NULL;
ELSE
   RETURN NEW;
END IF;
...

Related:

  • I have no triggers on this table. Row level security seems to be the cause. Edited. – Victor Mar 9 '18 at 23:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.