When creating a policy (CREATE POLICY) in postgres it's possible to specify expression to filter out some rows when issuing a SELECT query:
CREATE POLICY ... USING ( using_expression ) ...
It's also possible to specify a separate expression which triggers an error when evaluated to false but only for INSERT/UPDATE -
WITH CHECK ( check_expression ).
I'd like the same behavior for selects - is there a way to trigger an error if a select query tries to access a row which should be inaccessible according to some expression?
The reason I'm asking is that I'm considering using RLS as an additional and redundant layer of access control while the first one would be in the application logic. The problem with this is that it would be very hard to find issues in the application logic which somewhat defeats the purpose:
- If RLS is misconfigured and incorrectly allows some records through it will be caught by the application layer which should trigger an error - the issue is easy to detect.
- But if the application layer has a bug and incorrectly grants access to records it won't be visible at all - RLS will magically filter them out, until someone misconfigures RLS and the data leaks.
Is there a way to configure postgres such that it would error if a query attempts to read rows that it shouldn't? In particular it could be a way to ensure that all code paths actually have proper access control checks implemented.