Hi, has there been any discussion as to supporting row-level security
policies on materialized views? I didn't have much luck searching on
this topic. I see that PostgreSQL 9.5 refuses to create a policy on
a materialized view, consistent with the documentation focusing only on
tables.
First off, am I correct in understanding that access to regular views
will experience the same row-level security as if the client posed the
view-definition SQL directly in their query? I.e. the view is
expanded like a macro, and the row-level security on constituent
tables applied during the query evaluation with respect to the
consuming client security context?
In an ideal but impractical world, I would use materialized views as
strictly a performance optimization, while getting the same row-level
security as described above. However, I suspect in practice that the
security context of the client who defines or refreshes the view will
be applied instead, while the view is being materialized. Is that
also correct?
So, in a more practical but secure world, I suppose I would want the
materialized view to work the same as "CREATE TABLE AS ..." so that at
least I could retain security properties from source tables in my
transformed output, and define moral equivalent row-level security
policies on my materialized view. Then, other clients querying my
extract could have the same security applied as if they queried the
source tables, so I don't necessarily leak rows to clients less
trusted than myself.
Thanks for any insights,
Karl