Re: Updatable security_barrier views (was: [v9.4] row level security)

On 11/12/2013 02:35 PM, Tom Lane wrote:

>> [ lots o details snipped ]
>> Totally crazy? Or workable? I'm extremely new to the planner, so I know
>> this might be unworkable, and would value advice.
>
> The main omission I notice in your sketch is that the join tree that is
> the source of tuples has to produce both the ctid of the row to be
> updated, and values for *all* the columns of the target relation.
> So for any column that's not updated explicitly in the UPDATE command,
> we have to fetch the old value. IOW, if we have a table with columns
> x,y,z, and the original command is
>
> UPDATE ... SET y = something FROM ...
>
> then we effectively transform that to
>
> UPDATE ... SET x = x, y = something, z = z FROM ...
>
> and so we have to pull old values of x and z, as well as whatever
> we need to calculate the "something".

That makes sense. I was just reading the relevant part of
expand_targetlist when I got your mail. I don't think it makes much
difference.

Thankyou very much for taking the time to read this proposal. The fact
that it doesn't look immediately unworkable to you makes me hopeful. I'm
going to start prototyping it now.

> What I've not seen explained here is what is different between updating a
> security barrier view and the already-implemented logic for updating
> a plain view?

The current updatable view logic pulls up the view quals out of the view
subquery then flattens the view subquery. It's basically duplicating
logic done in the optimizer and notes that in the comments.

We can't do this for security barrier views, that's the whole point of
them. If we pull up quals we re-introduce the leaky view problem for
updates, eg:

UPDATE some_sb_view SET col = 'val' WHERE f_leak(secretcol);

If support for updating a subquery is added we might be able to get rid
of the pull-up code in the current updatable view support. Just let the
update pass through into the planner as an update over a subquery, and
then let the planner flatten it if it isn't a security barrier, just
like it does for a SELECT.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services