Re: [GENERAL] Can row level security policies also be implemented for views?

[Caleb Meredith]

Yes it probably could be solved by this. Just a trigger which replaces
SELECT right?
On Wed, Dec 2, 2015 at 6:01 PM Jim Nasby  wrote:

> 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
>

Re: [GENERAL] Can row level security policies also be implemented for views?

[Caleb Meredith]

What is an SRF?

On Wed, Dec 2, 2015 at 5:46 PM Jim Nasby  wrote:

> 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
>

Re: [GENERAL] Can row level security policies also be implemented for views?

[Jim Nasby]

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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Can row level security policies also be implemented for views?

[Jim Nasby]

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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Can row level security policies also be implemented for views?

[Jim Nasby]

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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Can row level security policies also be implemented for views?

[Stephen Frost]

Caleb,

* Caleb Meredith (calebmeredi...@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


signature.asc
Description: Digital signature

[GENERAL] Can row level security policies also be implemented for views?

[Caleb Meredith]

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?

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.