Re: Odd behavior of updatable security barrier views on foreign tables

* Stephen Frost (sfrost(at)snowman(dot)net) wrote:
> * Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
> > On Fri, Jan 30, 2015 at 5:20 AM, Etsuro Fujita
> > <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp> wrote:
> > > I noticed that when updating security barrier views on foreign tables,
> > > we fail to give FOR UPDATE to selection queries issued at ForeignScan.
> > > Here is an example.
> [...]
> > > postgres=# alter view rw_view set (security_barrier = true);
> > > ALTER VIEW
> > > postgres=# explain verbose delete from rw_view;
> > > QUERY PLAN
> > > --------------------------------------------------------------------------------------------------
> > > Delete on public.base_ftbl base_ftbl_1 (cost=100.00..144.54 rows=14
> > > width=6)
> > > Remote SQL: DELETE FROM public.base_tbl WHERE ctid = $1
> > > -> Subquery Scan on base_ftbl (cost=100.00..144.54 rows=14 width=6)
> > > Output: base_ftbl.ctid
> > > -> Foreign Scan on public.base_ftbl base_ftbl_2
> > > (cost=100.00..144.40 rows=14 width=6)
> > > Output: base_ftbl_2.ctid
> > > Remote SQL: SELECT ctid FROM public.base_tbl WHERE
> > > ((visibility = 'public'::text))
> > > (7 rows)
> > >
> > > Correct me if I am wrong.
> >
> > That looks like a bug to me.
>
> Agreed. I've been looking at this and I suspect it's related to the
> discussion around prepsecurity.c and generating the security barrier
> subquery that I've been having with Dean. An initial look, at least,
> shows that GetForeignPlan is looking at the subquery instead of the base
> relation (as it expects to be).
>
> I'll continue digging into it.

I've looked into this a fair bit more over the weekend and the issue
appears to be that the FDW isn't expecting a do-instead sub-query.
I've been considering how we might be able to address that but havn't
come up with any particularly great ideas and would welcome any
suggestions. Simply having the FDW try to go up through the query would
likely end up with too many queries showing up with 'for update'. We
add the 'for update' to the sub-query before we even get called from
the 'Modify' path too, which means we can't use that to realize when
we're getting ready to modify rows and therefore need to lock them.

In any case, I'll continue to look but would welcome any other thoughts.

Thanks,

Stephen