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.kla...@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
