From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Adam Guthrie <asguthrie@gmail.com>
To: pgsql-general@postgresql.org
Subject: Re: "plan should not reference subplan's variable" when using row level security
Date: Wed, 24 Feb 2016 11:54:42 -0800
Message-ID: <56CE0A82.3040307@aklaver.com> (raw)
In-Reply-To: <CAC3DOVy2H7W5bGeVaJjq5XtKvxGNKiPkG_SjXNOqXYLB5ccFBA@mail.gmail.com>
References: <CAC3DOVy2H7W5bGeVaJjq5XtKvxGNKiPkG_SjXNOqXYLB5ccFBA@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-general>
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-general@postgresql.org
Cc: adrian.klaver@aklaver.com, asguthrie@gmail.com
Subject: Re: "plan should not reference subplan's variable" when using row level security
In-Reply-To: <56CE0A82.3040307@aklaver.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox