pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feed"plan should not reference subplan's variable" when using row level security
6+ messages / 3 participants
[nested] [flat]
* "plan should not reference subplan's variable" when using row level security
@ 2016-02-24 17:51 Adam Guthrie <asguthrie@gmail.com>
2016-02-24 19:54 ` Re: "plan should not reference subplan's variable" when using row level security Adrian Klaver <adrian.klaver@aklaver.com>
2016-02-24 20:27 ` Re: "plan should not reference subplan's variable" when using row level security Stephen Frost <sfrost@snowman.net>
0 siblings, 2 replies; 6+ messages in thread
From: Adam Guthrie @ 2016-02-24 17:51 UTC (permalink / raw)
To: pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: "plan should not reference subplan's variable" when using row level security
2016-02-24 17:51 "plan should not reference subplan's variable" when using row level security Adam Guthrie <asguthrie@gmail.com>
@ 2016-02-24 19:54 ` Adrian Klaver <adrian.klaver@aklaver.com>
2016-02-24 20:29 ` Re: "plan should not reference subplan's variable" when using row level security Stephen Frost <sfrost@snowman.net>
1 sibling, 1 reply; 6+ messages in thread
From: Adrian Klaver @ 2016-02-24 19:54 UTC (permalink / raw)
To: Adam Guthrie <asguthrie@gmail.com>; pgsql-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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: "plan should not reference subplan's variable" when using row level security
2016-02-24 17:51 "plan should not reference subplan's variable" when using row level security Adam Guthrie <asguthrie@gmail.com>
2016-02-24 19:54 ` Re: "plan should not reference subplan's variable" when using row level security Adrian Klaver <adrian.klaver@aklaver.com>
@ 2016-02-24 20:29 ` Stephen Frost <sfrost@snowman.net>
0 siblings, 0 replies; 6+ messages in thread
From: Stephen Frost @ 2016-02-24 20:29 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: Adam Guthrie <asguthrie@gmail.com>; pgsql-general
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
Attachments:
signature.asc (application/pgp-signature, 818B) download
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: "plan should not reference subplan's variable" when using row level security
2016-02-24 17:51 "plan should not reference subplan's variable" when using row level security Adam Guthrie <asguthrie@gmail.com>
@ 2016-02-24 20:27 ` Stephen Frost <sfrost@snowman.net>
2016-02-24 20:35 ` Re: "plan should not reference subplan's variable" when using row level security Adam Guthrie <asguthrie@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: Stephen Frost @ 2016-02-24 20:27 UTC (permalink / raw)
To: Adam Guthrie <asguthrie@gmail.com>; +Cc: pgsql-general
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
Attachments:
signature.asc (application/pgp-signature, 818B) download
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: "plan should not reference subplan's variable" when using row level security
2016-02-24 17:51 "plan should not reference subplan's variable" when using row level security Adam Guthrie <asguthrie@gmail.com>
2016-02-24 20:27 ` Re: "plan should not reference subplan's variable" when using row level security Stephen Frost <sfrost@snowman.net>
@ 2016-02-24 20:35 ` Adam Guthrie <asguthrie@gmail.com>
2016-02-24 20:45 ` Re: "plan should not reference subplan's variable" when using row level security Stephen Frost <sfrost@snowman.net>
0 siblings, 1 reply; 6+ messages in thread
From: Adam Guthrie @ 2016-02-24 20:35 UTC (permalink / raw)
To: Stephen Frost <sfrost@snowman.net>; +Cc: pgsql-general
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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: "plan should not reference subplan's variable" when using row level security
2016-02-24 17:51 "plan should not reference subplan's variable" when using row level security Adam Guthrie <asguthrie@gmail.com>
2016-02-24 20:27 ` Re: "plan should not reference subplan's variable" when using row level security Stephen Frost <sfrost@snowman.net>
2016-02-24 20:35 ` Re: "plan should not reference subplan's variable" when using row level security Adam Guthrie <asguthrie@gmail.com>
@ 2016-02-24 20:45 ` Stephen Frost <sfrost@snowman.net>
0 siblings, 0 replies; 6+ messages in thread
From: Stephen Frost @ 2016-02-24 20:45 UTC (permalink / raw)
To: Adam Guthrie <asguthrie@gmail.com>; +Cc: pgsql-general
* 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
Attachments:
signature.asc (application/pgp-signature, 818B) download
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2016-02-24 20:45 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-02-24 17:51 "plan should not reference subplan's variable" when using row level security Adam Guthrie <asguthrie@gmail.com>
2016-02-24 19:54 ` Adrian Klaver <adrian.klaver@aklaver.com>
2016-02-24 20:29 ` Stephen Frost <sfrost@snowman.net>
2016-02-24 20:27 ` Stephen Frost <sfrost@snowman.net>
2016-02-24 20:35 ` Adam Guthrie <asguthrie@gmail.com>
2016-02-24 20:45 ` Stephen Frost <sfrost@snowman.net>
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