Date: Wed, 24 Feb 2016 15:45:53 -0500
From: Stephen Frost <sfrost@snowman.net>
To: Adam Guthrie <asguthrie@gmail.com>
Cc: pgsql-general@postgresql.org
Subject: Re: "plan should not reference subplan's variable" when
 using row level security
Message-ID: <20160224204553.GK3127@tamriel.snowman.net>
References: 
 <CAC3DOVy2H7W5bGeVaJjq5XtKvxGNKiPkG_SjXNOqXYLB5ccFBA@mail.gmail.com>
 <20160224202751.GI3127@tamriel.snowman.net>
 <CAC3DOVwf4s5ftjVSOrpSHVdxR=mMhSqsORmRcaryAqTzrRjRTg@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="2WS97oupGEGbYNpW"
Content-Disposition: inline
In-Reply-To: 
 <CAC3DOVwf4s5ftjVSOrpSHVdxR=mMhSqsORmRcaryAqTzrRjRTg@mail.gmail.com>
User-Agent: Mutt/1.5.21 (2010-09-15)
Precedence: bulk
Sender: pgsql-general-owner@postgresql.org


--2WS97oupGEGbYNpW
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

* 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.
>=20
> 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.
>=20
> I've also discovered that using the following policy instead
>=20
> CREATE POLICY a_select ON b FOR SELECT
>     USING ( a_id IN (SELECT id FROM a) );
>=20
> 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 =3D $1);

Would still use an indexed lookup against 'a'.

Thanks!

Stephen

--2WS97oupGEGbYNpW
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iQIcBAEBCgAGBQJWzhaBAAoJEO1sijiDR2RVgQYP/3/DF0kq5f6zbjUsS9qddu4S
fYvwDHNvZWjS2XMo5pfcsZ3hcLd8f8VO/EiMkDIKYQx/dP5KJ9ObG30Cr87lP2Lw
Wc3eBREeTEc8a2OV8QWHDOBpVRmJpe6tlL/9+MktxqfN1M0Zduy6s5hvnw5jPI/S
J/KqghIb00uk7thMjtvWX+6JXDmuL0G3FeuWM2tNd8hLo0vF2JxOah90bOLU+p/o
JlRv632lDipp47UFgjPISyQjf5QptKQsrDlG/wGP5pxJ/Tq7zKEdtsyUpACPxkj+
eSwBH4BA3+z+PGNwmVahldeJBqQRBOvUFdmFDkdyducrMyXAyAS3jkzyx+28k2Um
McGznLvXSQAEuNwk0IHFVNXrtPA6fPvQBfSHw0lUjFYzP6K7eJrsZ/wUNj/ka24v
ffRAlhE2ymsCL5z9vP52BUvXvWD48HKnUxzgB946voImNRU+OWewEfmBTD8Xe1dK
iIXNEZqyupolmcYcjXYxqodXYbwLIUEHLMzW6vMa+riIV3Sdk6fl74MqLpiDqAMS
yqjzaCjCsZLc8IlStVzDMbBUZl23r2hQu5QXSJkCDHk6D1feYBM5ZgMjZekHPofN
aR8FgXKZUUbbBHuI0Tt2Sus81dc7BaiOlg7EZGiBJEjHMnYTievsxKf/IAt361cm
m7iOrAn0BVVTxXMT4D/u
=VmKv
-----END PGP SIGNATURE-----

--2WS97oupGEGbYNpW--