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 <nag...@sraoss.co.jp>
>From 3417c4cce46ec068464b7069428e7f4a9a2cd07d Mon Sep 17 00:00:00 2001
From: Yugo Nagata <nag...@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
