public inbox for [email protected]  

help / color / mirror / Atom feed

From: Josh Snyder <[email protected]>
To: [email protected]
Subject: PATCH: Add query for operators unusable with RLS to documentation
Date: Sat, 18 May 2024 16:54:52 -0700
Message-ID: <[email protected]> (raw)

When deploying RLS, I was surprised to find that certain queries which used 
only builtin indexes and operators had dramatically different query plans when 
a policy is applied. In my case, the query `tsvector @@ tsquery` over a GIN
index was no longer able to use that index. I was able to find one other
instance [1] of someone being surprised by this behavior on the mailing lists.

The docs already discuss the LEAKPROOF semantics in the abstract, but I think 
they place not enough focus on the idea that builtin operators can be (and
frequently are) not leakproof. Based on the query given in the attached patch,
I found that 387 operators are not leakproof versus 588 that are.

The attached patch updates the documentation to provide an easy query over
system catalogs as a way of determining which operators will no longer perform
well under RLS or a security-barrier view.

Thanks,
Josh

[1] https://www.postgresql.org/message-id/CAGrP7a2t%2BJbeuxpQY%2BRSvNe4fr3%2B%3D%3DUmyimwV0GCD%2BwcrSSb%...

Attachments:

  [text/x-patch] 0001-Add-query-for-operators-unusable-with-RLS.patch (1.7K, ../[email protected]/2-0001-Add-query-for-operators-unusable-with-RLS.patch)
  download | inline diff:
From db382697f14bd12dd9e29606c314d7a35bd290ea Mon Sep 17 00:00:00 2001
From: Josh Snyder <[email protected]>
Date: Sat, 18 May 2024 15:50:47 -0700
Subject: [PATCH] Add query for operators unusable with RLS

---
 doc/src/sgml/rules.sgml | 26 ++++++++++++++++++++++++++
 1 file changed, 26 insertions(+)

diff --git a/doc/src/sgml/rules.sgml b/doc/src/sgml/rules.sgml
index 7a928bd7b9..3b1283c002 100644
--- a/doc/src/sgml/rules.sgml
+++ b/doc/src/sgml/rules.sgml
@@ -2167,6 +2167,32 @@ CREATE VIEW phone_number WITH (security_barrier) AS
     view's row filters.
 </para>
 
+<para>
+    When working with <literal>security_barrier</literal> views or row-level
+    security policies, a query author can check in advance which index-access
+    operators cannot cross the security barrier by querying system catalogs:
+</para>
+
+<programlisting>
+SELECT
+  amname,
+  format('%s(%s,%s) [%s]', oprcode, lefttype.typname, righttype.typname, oprname)
+FROM pg_operator
+  JOIN pg_amop ON pg_operator.oid = pg_amop.amopopr
+  JOIN pg_am ON pg_amop.amopmethod = pg_am.oid
+  JOIN pg_proc ON pg_operator.oprcode = pg_proc.oid
+  JOIN pg_type lefttype ON pg_amop.amoplefttype = lefttype.oid
+  JOIN pg_type righttype ON pg_amop.amoprighttype = righttype.oid
+WHERE proleakproof = false;
+</programlisting>
+
+<para>
+    Any operator returned by the above query will not be able to perform an
+    indexed lookup through the security barrier. In that case, the view's own
+    conditions will be applied first, followed by conditions added by the query
+    author.
+</para>
+
 <para>
     It is important to understand that even a view created with the
     <literal>security_barrier</literal> option is intended to be secure only
-- 
2.40.1

view thread (2+ messages)

reply

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: [email protected]
  Cc: [email protected]
  Subject: Re: PATCH: Add query for operators unusable with RLS to documentation
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox