Thread: "plan should not reference subplan's variable" when using row level security

Hi,

Whilst trying to use row level security with a subquery in the USING
expression, I'm receiving an error "plan should not reference
subplan's variable"

A simple sql file to reproduce:

****

CREATE TABLE a (
    id      INTEGER PRIMARY KEY
);

CREATE TABLE b (
    id      INTEGER PRIMARY KEY,
    a_id    INTEGER,
    text    TEXT
);

CREATE POLICY a_select ON b FOR SELECT
    USING ( EXISTS(SELECT FROM a WHERE a.id = b.a_id) );

ALTER TABLE b ENABLE ROW LEVEL SECURITY;

INSERT INTO a (id) VALUES (1);

INSERT INTO b (id, a_id, text) VALUES (1, 1, 'one');

GRANT ALL ON ALL TABLES IN SCHEMA public TO test;

SET ROLE test;

SELECT * FROM b;

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

****

gives error:

psql:/tmp/test.sql:26: ERROR:  plan should not reference subplan's variable

Is this a bug or am I doing something wrong?

Any help much appreciated,

Adam

On 02/24/2016 09:51 AM, Adam Guthrie wrote:
> Hi,
>
> Whilst trying to use row level security with a subquery in the USING
> expression, I'm receiving an error "plan should not reference
> subplan's variable"
>
> A simple sql file to reproduce:
>
> ****
>
> CREATE TABLE a (
>      id      INTEGER PRIMARY KEY
> );
>
> CREATE TABLE b (
>      id      INTEGER PRIMARY KEY,
>      a_id    INTEGER,
>      text    TEXT
> );
>
> CREATE POLICY a_select ON b FOR SELECT
>      USING ( EXISTS(SELECT FROM a WHERE a.id = b.a_id) );
>
> ALTER TABLE b ENABLE ROW LEVEL SECURITY;
>
> INSERT INTO a (id) VALUES (1);
>
> INSERT INTO b (id, a_id, text) VALUES (1, 1, 'one');
>
> GRANT ALL ON ALL TABLES IN SCHEMA public TO test;
>
> SET ROLE test;
>
> SELECT * FROM b;
>
> UPDATE b SET text = 'ONE' WHERE id = 1;
>
> ****
>
> gives error:
>
> psql:/tmp/test.sql:26: ERROR:  plan should not reference subplan's variable
>
> Is this a bug or am I doing something wrong?

I started to work through this when I realized the
permissions/attributes of the role test are not shown. This seems to be
important as the UPDATE example works if you run it immediately after:

INSERT INTO b (id, a_id, text) VALUES (1, 1, 'one');

>
> Any help much appreciated,
>
> Adam
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Adam,

* Adam Guthrie (asguthrie@gmail.com) wrote:
> psql:/tmp/test.sql:26: ERROR:  plan should not reference subplan's variable
>
> Is this a bug or am I doing something wrong?

Yeah, looks like a bug to me.  My gut reaction is that we're pulling up
a subquery in a way that isn't possible and that plan shouldn't be
getting built/considered.

As a work-around, until we fix it, you could create an sql function to
check for the existance of the id in 'a' and use that in the policy
definition.

Thanks!

Stephen

Adrian,

* Adrian Klaver (adrian.klaver@aklaver.com) wrote:
> I started to work through this when I realized the
> permissions/attributes of the role test are not shown. This seems to
> be important as the UPDATE example works if you run it immediately
> after:
>
> INSERT INTO b (id, a_id, text) VALUES (1, 1, 'one');

Not sure what you mean- the necessary permissions for the test role are
just the 'GRANT ALL ON ALL TABLES' which is included.

If you run the UPDATE immediately after the INSERT, then it's before
that GRANT and, more importantly, before the 'SET ROLE', meaning that
you're running it as the table owner, and the policy is ignored
(policies are not applied to the owner of the table, unless FORCE RLS is
used).

Thanks!

Stephen

On 24 February 2016 at 20:27, Stephen Frost <sfrost@snowman.net> wrote:
> Yeah, looks like a bug to me.  My gut reaction is that we're pulling up
> a subquery in a way that isn't possible and that plan shouldn't be
> getting built/considered.

Thanks - shall I go ahead and submit a bug report?

>
> As a work-around, until we fix it, you could create an sql function to
> check for the existance of the id in 'a' and use that in the policy
> definition.

I've also discovered that using the following policy instead

CREATE POLICY a_select ON b FOR SELECT
    USING ( a_id IN (SELECT id FROM a) );

also seems to work around the issue.

* Adam Guthrie (asguthrie@gmail.com) wrote:
> On 24 February 2016 at 20:27, Stephen Frost <sfrost@snowman.net> wrote:
> > Yeah, looks like a bug to me.  My gut reaction is that we're pulling up
> > a subquery in a way that isn't possible and that plan shouldn't be
> > getting built/considered.
>
> Thanks - shall I go ahead and submit a bug report?

Sure.

> > As a work-around, until we fix it, you could create an sql function to
> > check for the existance of the id in 'a' and use that in the policy
> > definition.
>
> I've also discovered that using the following policy instead
>
> CREATE POLICY a_select ON b FOR SELECT
>     USING ( a_id IN (SELECT id FROM a) );
>
> also seems to work around the issue.

Yes, that also works, but it could get painful if 'a' gets large.  An
SQL function like:

select exists (select * from a where a.id = $1);

Would still use an indexed lookup against 'a'.

Thanks!

Stephen