Thread: Can row level security policies also be implemented for views?

Caleb,

* Caleb Meredith (calebmeredith8@gmail.com) wrote:
> I'm developing an application where strict control of my data is important.
> Views allow me to build a strict custom reading experience, allowing me to
> add computed columns and hide private and metadata columns. Row level
> security allows me strict write control of my data. However, I can't use
> both technologies together, why?

The short and simple answer is that it simply hasn't been done yet.

> It seems easy conceptually, RLS just adds a WHERE clause to queries if I'm
> not mistaken, and conceptually a view is just a query. The CURRENT_USER
> issue is valid, but personally it's not too big for me as most auth is done
> through database parameters.

The hard part is making sure that what happens when there are policies
on views actually makes sense and works as users expect.

Thanks!

Stephen

On 11/25/15 7:40 AM, Stephen Frost wrote:
>> It seems easy conceptually, RLS just adds a WHERE clause to queries if I'm
>> >not mistaken, and conceptually a view is just a query. The CURRENT_USER
>> >issue is valid, but personally it's not too big for me as most auth is done
>> >through database parameters.
> The hard part is making sure that what happens when there are policies
> on views actually makes sense and works as users expect.

Could this potentially be worked around by having a view that pulls from
an SRF? Possibly an SRF that's using a dynamically constructed query?
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com

On 12/2/15 4:59 PM, Caleb Meredith wrote:
> What is an SRF?

Set returning function.

CREATE FUNCTION srf() RETURNS SETOF pg_class LANGUAGE sql AS 'SELECT *
FROM pg_class';
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com

On 12/3/15 7:17 AM, Caleb Meredith wrote:
> Yes it probably could be solved by this. Just a trigger which replaces
> SELECT right?

It's not a trigger and I don't know what you mean by replacing select.
An SRF can return anything you want it to.

My suggestion was to run a standard select statement (possibly a dynamic
one via EXECUTE) from the SRF, in the hope that the system would just
consider that to be a plain-old SELECT against the table, allowing RLS
to function.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com