I need strict control of the reading and writing of my Postgres data. Updatable views have always provided very good, strict, control of the reading of my data and allows me to add valuable computed columns. With Postgres 9.5 row level security has introduced a new and powerful way to control my data. But I can't use both technologies views, and row level security together. Why?
30
votes
if you enable row level security on the table and then use the updatable view on the table, does the security not work?
– mehmet
No because the query goes through the view defined role, not the current role.
– Calebmer
Then, how about setting up the row level security on the view defined role?
– mehmet
I have a few different roles accessing the view, so I lose that information.
– Calebmer
2 Answers
30
votes
Basically because it wasn't possible to retroactively change how views work. I'd like to be able to support SECURITY INVOKER (or equivalent) for views but as far as I know no such feature presently exists.
You can filter access to the view its self with row security normally.
The tables accessed by the view will also have their row security rules applied. However, they'll see the current_user as the view creator because views access tables (and other views) with the rights of the user who created/owns the view.
Maybe it'd be worth raising this on pgsql-hackers if you're willing to step in and help with development of the feature you need, or pgsql-general otherwise?
That said, while views access tables as the creating user and change current_user accordingly, they don't prevent you from using custom GUCs, the session_user, or other contextual information in row security policies. You can use row security with views, just not (usefully) to filter based on current_user.
