0

I'm currently working on implementing a 2 layer security, meaning we have orm query filtering in our application code as well as in our database. In the database I'm using Postgre Row Level Security.

In an ideal world, all the filtering queries built in the application code is perfect and the row level security policies never remove entries. However, if it does happen to filter out a result, that means that we have an error in our filtering in application code that must be fixed. I would like to be able to log this.

Is it possible to configure Postgresql that I can be notified / log these occurrences?

1 Answer 1

1

You could use a function in your policy that has the side effect of logging data. But that is not reliable, because it might well be that the RLS condition is applied before some other query condition, so that you'd get false positives that wouldn't have become part of the result set anyway.

I don't think that there is a way to do what you want. That is not the job of RLS: it makes some rows invisible for a user, so that nobody can see the data they shouldn't.

5
  • Late follow up, but - wouldn't it be possible to assign that function a very high fake cost to ensure that it'd get executed last in most cases? Of course it would need to be profiled if messing with the execution order like that creates a noticable performance penalty, but it could theoretically work.
    – Joe
    Mar 15 at 14:51
  • 1
    That should do the trick in many cases. But I wouldn't be absolutely certain that it works always. Mar 15 at 14:59
  • I'd argue that if there are cases where it doesn't work, something else is wrong anyways. But does highest cost guarantee it will get executed last?
    – Joe
    Mar 15 at 15:00
  • 1
    Right, that's what I doubt. Correctness always goes before cheapness. Besides, the high cost will distort the cost estimates, which may lead to bad plan choices. You don't want your queries to perform bad just because of auditing. Mar 15 at 15:03
  • You're right. Hmm. It's a shame there's no good way of achieving the goal.
    – Joe
    Mar 15 at 15:22

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.