Date: Mon, 1 Jul 2024 22:08:17 +0900
From: Yugo NAGATA <nagata@sraoss.co.jp>
To: pgsql-hackers@lists.postgresql.org
Subject: psql: Add leakproof field to \dAo+ meta-command results
Message-Id: <20240701220817.483f9b645b95611f8b1f65da@sranhm.sraoss.co.jp>
Mime-Version: 1.0
Content-Type: multipart/mixed;
 boundary="Multipart=_Mon__1_Jul_2024_22_08_17_+0900_6Wl=X83gDfvv+ux9"
Archived-At: <https://www.postgresql.org/message-id/20240701220817.483f9b645b95611f8b1f65da%40sranhm.sraoss.co.jp>
Precedence: bulk

This is a multi-part message in MIME format.

--Multipart=_Mon__1_Jul_2024_22_08_17_+0900_6Wl=X83gDfvv+ux9
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

Hi,

I would like to propose to add a new field to psql's \dAo+ meta-command
to show whether the underlying function of an operator is leak-proof.

This idea is inspired from [1] that claims some indexes uses non-LEAKPROOF
functions under the associated operators, as a result, it can not be selected
for queries with security_barrier views or row-level security policies.
The original proposal was to add a query over system catalogs for looking up
non-leakproof operators to the documentation, but I thought it is useful
to improve \dAo results rather than putting such query to the doc.

The attached patch adds the field to \dAo+ and also a description that
explains the relation between indexes and security quals with referencing
\dAo+ meta-command.

[1] https://www.postgresql.org/message-id/raw/5af3bf0c-5e0c-4128-81dc-084c5258b1af%40code406.com

Regards,
Yugo Nagata

-- 
Yugo NAGATA <nagata@sraoss.co.jp>

--Multipart=_Mon__1_Jul_2024_22_08_17_+0900_6Wl=X83gDfvv+ux9
Content-Type: text/x-diff;
 name="0001-psql-Add-leakproof-field-to-dAo-meta-command-results.patch"
Content-Disposition: attachment;
 filename="0001-psql-Add-leakproof-field-to-dAo-meta-command-results.patch"
Content-Transfer-Encoding: 7bit

From 3417c4cce46ec068464b7069428e7f4a9a2cd07d Mon Sep 17 00:00:00 2001
From: Yugo Nagata <nagata@sraoss.co.jp>
Date: Mon, 1 Jul 2024 16:16:39 +0900
Subject: [PATCH] psql: Add leakproof field to \dAo+ meta-command results

This adds a field that shows whether the underlying function of an
operator associated with operator families is leak-proof.
It is useful for checking an index can be used with security_barrier
views or row-level security policies when the query's WHERE
clause contains an operator which is associated with the index.
---
 doc/src/sgml/ref/psql-ref.sgml |  3 ++-
 doc/src/sgml/rules.sgml        | 10 ++++++++++
 src/bin/psql/describe.c        | 17 +++++++++++++----
 3 files changed, 25 insertions(+), 5 deletions(-)

diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 830306ea1e..d59afa7524 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1362,7 +1362,8 @@ INSERT INTO tbl1 VALUES ($1, $2) \bind 'first value' 'second value' \g
         is specified, only members of operator families whose names match that
         pattern are listed.
         If <literal>+</literal> is appended to the command name, each operator
-        is listed with its sort operator family (if it is an ordering operator).
+        is listed with its sort operator family (if it is an ordering operator),
+        and whether it is leak-proof.
         </para>
         </listitem>
       </varlistentry>
diff --git a/doc/src/sgml/rules.sgml b/doc/src/sgml/rules.sgml
index 7a928bd7b9..5e17031ee9 100644
--- a/doc/src/sgml/rules.sgml
+++ b/doc/src/sgml/rules.sgml
@@ -2167,6 +2167,16 @@ CREATE VIEW phone_number WITH (security_barrier) AS
     view's row filters.
 </para>
 
+<para>
+    For example, an index scan can not be selected for queries with
+    <literal>security_barrier</literal> views or row-level security policies if an
+    operator used in the <literal>WHERE</literal> clause is associated with the
+    operator family of the index, but its underlying function is not marked
+    <literal>LEAKPROOF</literal>. The <xref linkend="app-psql"/> program's
+    <command>\dAo+</command> meta-command is useful for listing the operators
+    with associated operator families and whether it is leak-proof.
+</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
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index f67bf0b892..243f099017 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -6872,7 +6872,7 @@ listOpFamilyOperators(const char *access_method_pattern,
 	printQueryOpt myopt = pset.popt;
 	bool		have_where = false;
 
-	static const bool translate_columns[] = {false, false, false, false, false, false};
+	static const bool translate_columns[] = {false, false, false, false, false, false, false};
 
 	initPQExpBuffer(&buf);
 
@@ -6900,8 +6900,15 @@ listOpFamilyOperators(const char *access_method_pattern,
 
 	if (verbose)
 		appendPQExpBuffer(&buf,
-						  ", ofs.opfname AS \"%s\"\n",
-						  gettext_noop("Sort opfamily"));
+						  ", ofs.opfname AS \"%s\"\n,"
+						  "  CASE\n"
+						  "   WHEN p.proleakproof THEN '%s'\n"
+						  "   ELSE '%s'\n"
+						  " END AS \"%s\"\n",
+						  gettext_noop("Sort opfamily"),
+						  gettext_noop("yes"),
+						  gettext_noop("no"),
+						  gettext_noop("Leak-proof"));
 	appendPQExpBufferStr(&buf,
 						 "FROM pg_catalog.pg_amop o\n"
 						 "  LEFT JOIN pg_catalog.pg_opfamily of ON of.oid = o.amopfamily\n"
@@ -6909,7 +6916,9 @@ listOpFamilyOperators(const char *access_method_pattern,
 						 "  LEFT JOIN pg_catalog.pg_namespace nsf ON of.opfnamespace = nsf.oid\n");
 	if (verbose)
 		appendPQExpBufferStr(&buf,
-							 "  LEFT JOIN pg_catalog.pg_opfamily ofs ON ofs.oid = o.amopsortfamily\n");
+							 "  LEFT JOIN pg_catalog.pg_opfamily ofs ON ofs.oid = o.amopsortfamily\n"
+							 "  LEFT JOIN pg_catalog.pg_operator op ON op.oid = o.amopopr\n"
+							 "  LEFT JOIN pg_catalog.pg_proc p ON p.oid = op.oprcode\n");
 
 	if (access_method_pattern)
 	{
-- 
2.25.1


--Multipart=_Mon__1_Jul_2024_22_08_17_+0900_6Wl=X83gDfvv+ux9--