Re: BUG #13988: "plan should not reference subplan's variable" whilst using row level security

On 24 February 2016 at 21:42, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> wrote:
> asguthrie(at)gmail(dot)com wrote:
>> The following bug has been logged on the website:
>>
>> Bug reference: 13988
>> Logged by: Adam Guthrie
>> Email address: asguthrie(at)gmail(dot)com
>> PostgreSQL version: 9.5.1
>> Operating system: OS X 10.11
>
> For the benefit of those not in pgsql-general, this is already being
> discussed here:
> http://www.postgresql.org/message-id/CAC3DOVy2H7W5bGeVaJjq5XtKvxGNKiPkG_SjXNOqXYLB5ccFBA@mail.gmail.com
>

This can also be directly reproduced using updatable security barrier
views. The following is equivalent to what is happening with that RLS
setup:

CREATE TABLE a(id int);
CREATE TABLE b(id int, a_id int, text text);

CREATE VIEW v1 WITH (security_barrier=true) AS
SELECT * FROM b WHERE false;
CREATE VIEW v2 WITH (security_barrier=true) AS
SELECT * FROM v1 WHERE EXISTS (SELECT FROM a WHERE a.id = v1.a_id);

UPDATE v2 SET text = 'ONE' WHERE id = 1;

Debugging it, I have a theory as to the cause of the problem, which I
think is in security_barrier_replace_vars() --- when it finds a
matching Var that needs to be added to the targetlist that it is
building, it copies the existing Var and modifies it:

/* New variable for subquery targetlist */
newvar = copyObject(var);
newvar->varno = newvar->varnoold = 1;
...

However, the Var found comes from a sublink subquery in the outer
query, and so has varlevelsup = 1, but newvar is for the new subquery
being built, so it needs to have varlevelsup set to 0, which that code
fails to do.

If that is indeed the problem, the fix is trivial, but I haven't had a
chance to test that theory yet -- hopefully I'll get some more time at
the weekend.

Regards,
Dean