PostgreSQL – Troubleshooting Row Level Security with No Error on Query Blocking

permissionspostgresql

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.

Best Answer

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.

Related Question