Date: Tue, 21 Feb 2023 16:43:40 -0500
From: Stephen Frost <sfrost@snowman.net>
To: mtaube@caci.com, pgsql-docs@lists.postgresql.org
Subject: Re: Row Level Security Execution within the SQL Evaluation Pipeline
Message-ID: <Y/U7DNmxpvBg+9Sz@tamriel.snowman.net>
References: <167701474147.23475.959567841182626784@wrigleys.postgresql.org>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="YnPP0ljAG7m81EeI"
Content-Disposition: inline
In-Reply-To: <167701474147.23475.959567841182626784@wrigleys.postgresql.org>
User-Agent: Mutt/2.1.4 (2021-12-11)
Archived-At: 
 <https://www.postgresql.org/message-id/Y/U7DNmxpvBg%2B9Sz%40tamriel.snowman.net>
Precedence: bulk


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

Greetings,

* PG Doc comments form (noreply@postgresql.org) wrote:
> RLS documentation seems to say the user's predicate is evaluated AFTER the
> policy is evaluated. This presents to me an issue that I can't wrap my he=
ad
> around, and seems to confuse others as well.
>=20
> Setup:
> 1. We have a policy-enabled table that has millions of rows, with ID as a
> primary key.=20
> 2. There exists a row in this table whose ID column is 10.
> 3. User submits a query: SELECT * FROM MYTABLE WHERE id =3D 10.=20
>=20
> According to the documentation, the WHERE clause is not evaluated until
> AFTER the policy is evaluated, thus there is initially a full table scan =
of
> MYTABLE that returns only the set of rows that the user has access to (via
> the policy), THEN the WHERE clause is activated that reduces the row count
> to 1 or 0.
>=20
> This sounds non-performant, and if this is or is not the case, I think it
> should be more clearly explained. In addition, a link to a "best practices
> using the policy effectively" would be useful, as from reviewing stack
> overflow, there is lots of concern over performance of RLS.

Functions which are leakproof can be pushed down below the policy
because those functions won't ever leak information about the values
that they might see in the data that the user shouldn't be allowed to
see.

Logically, the WHERE clause still comes after the policy, but with the
leakproof function that backs the '=3D' operator, we're able to optimize
the query and use the 'id' index that exists.

Of course, RLS isn't going to be free and you can certainly have cases
where you're using a function or operator that isn't leakproof and then
you'll have the issues you describe, or just in general adding on the
conditions of the policy could have performance impacts, but this
specific case isn't going to be an issue.  Note that we do explicitly
perform query optimization *after* adding in the RLS policies into the
query.

Thanks,

Stephen

--YnPP0ljAG7m81EeI
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAABCgAdFiEEwf6gbxKhD863zrx/7WyKOINHZFUFAmP1OwkACgkQ7WyKOINH
ZFXgcg//QZZw3V84UU5qtYnzo2CUFwC5oKOVhrj9gm1RLVwhi8J93mwO2XI06jpU
F9LFIpRJS72CtMfM+Y9e59GUh/MaitWaEbx1oWSajCd/tydm/rptJiJJgvkI7kGl
P6LmsGa7J1a7uu+XvpbL1fRE0uaFnH7xAOgUfIHNpWvYdYBuRl4aUoQegEjZdhdT
SjhEQC8yqR2u+1felEkgYopazLb9OhNPTMWTSkoXUAy1PgxXqpahaHGg7p171wvh
1QcIZzvl2bLzYYHRsyIzOqH1h13v5CJAYE8hgeaQ1iaoMSROx6/zG7pvwQMih2Jp
1BBkMc8oip9Qf/RXs0FDZVM/pIHH7ADyh5waDToG03qbUgJkzX/uidVgu1CSzDc0
+riGpB8RlXyHANqAo28NDw38nLU+EAyJgQX5i7HGSNWz9fm18DPBf32HnvO/2vvY
/F8bYb0HSXgqfRD76LF54Kq/qlMK4G9YvbSA1rYSnIvHvscYUEnHSglLaswloK5t
bfBzHALwQr8HG9RRvpkrAsInw0xvbwpU9C+NxleT9n2EXXQA3xX5AgNE90/ashfT
U72VF+yzfa2C9c+ZngHLjoy364AIvFIKs91ZhhOG+r910Mu3IN85FQMUElFujkm6
T6C86NYcqQ2eXpinuRZSaRF9tsc1JGaXBcAEP8QymW9vfoaLXL8=
=Ecq2
-----END PGP SIGNATURE-----

--YnPP0ljAG7m81EeI--