0

I want to expose a user/accounts table via an API and secure it on the PostgreSQL level.

Using row-level security has worked great for my other tables, but on the accounts table I have some data that I don't want the user to be able to view or edit even on his own row (such as the locked state, number of failed logins and some internal fields).

My first thought was to hide the actual table away in a different scheme and define a view with only the fields I want accessible. But row-level security doesn't accept that. According to this site here: https://www.benburwell.com/posts/row-level-security-postgresql-views/ - that's a problem of views and ownership. But that was 2020. Maybe something changed? His solution wouldn't work for me because I also want to write into the table.

Is there a solution to implement row-level security in 2022 with current PostgreSQL version? Or is there a better workaround that allows writing?

related info:

Tom
  • 2,688
  • 3
  • 29
  • 53
  • What's the problem with the view? You can specify exactly what rows and columns of the underlying table(s) will be visible and restricting updating of a view is trivial. – Patrick Aug 18 '22 at 19:25
  • according to the first 2 links, row-level security of the underlying table is ignored in a view, and it seems to not be possible to define RLS for a view (at least I'm getting errors). But I need RLS because I want that people can see and edit only their own row of user data. – Tom Aug 19 '22 at 05:24
  • 1
    From PostgreSQL v15 on, you can use the `security_invoker` option on views. – Laurenz Albe Aug 19 '22 at 06:33
  • @LaurenzAlbe there is the answer I was looking for. Thanks! – Tom Aug 19 '22 at 08:37

0 Answers0