Re: [v9.4] row level security - Mailing list pgsql-hackers

On 11/07/2013 09:47 PM, Greg Stark wrote:

> Incidentally I still feel this is at root the problem with updateable
> views in general. I know it's a bit off to be tossing in concerns from
> the peanut gallery when I'm not actually offering to work on it and
> others are having putting in serious efforts in this area and having
> some success. So take this for what it's worth...

Frankly, the peanut gallery input can be quite handy. It's easy to get
so stuck in the way you've seen it thought about already that you don't
see other ways to view it. Plus, sometimes the peanut gallery becomes
the "oh, I don't like this at all" crowd when commit time is
approaching, so early comments are better than no comments then last
minute complaints.

> I think the right approach for updateable views would be to support a
> syntax like this in the planner:
> 
> UPDATE (select * from tab1,tab2 ...) WHERE tab1.id <http://tab1.id> = ..
> SET ...

I want to support that for rewritten parse trees, and therefore (because
of recursive rewrite) in pre-rewrite parse trees. It's exactly what's
needed to make this sane, IMO, and I think this is what Robert was
suggesting with making UPDATE capable of dealing with operating directly
on a subquery scan.

I'm not at all convinced it should be exposed to the user and accepted
by the parser as SQL, but I don't know if that's what you were suggesting.

Robert? Is this what you meant? If so, any chance you can point a
planner neophyte like me in vaguely the right direction?

> I'm not sure if this solves all the problems with RLS but it would solve
> the concern about plan invalidations and I think it would make it
> simpler to reason about security rules that are otherwise happening at
> plan time.

I don't think it'd help with plan invalidations. View expansion happens
at rewrite time, and we don't re-run the rewriter on the original parse
tree when we invalidate plans and re-plan. The current patch entirely
elides the RLS quals and subquery when running as superuser and that
won't work with view expansion at the rewrite stage.

So we still have that to deal with, and need to handle a few side issues
with portals and user id changes etc, but the biggest issue is coming up
with an acceptable way to update a security barrier view or subquery.

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