pg.ddx.io pgsql-hackers@postgresql.org mailing list archive
help / color / mirror / Atom feedexpand refint docs with usage info
5+ messages / 3 participants
[nested] [flat]
* expand refint docs with usage info
@ 2026-05-26 16:53 Nathan Bossart <nathandbossart@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: Nathan Bossart @ 2026-05-26 16:53 UTC (permalink / raw)
To: pgsql-hackers
The security team has received a couple of reports about potential SQL
injection opportunities via refint's trigger arguments. We discussed this
while preparing CVE-2026-6637 and concluded that forcibly quoting these
arguments would be much more likely to break working code than to prevent
any exploits. Unlike data values, the table/column names come from trigger
arguments, and there is little reason for a trigger author to put hostile
inputs into those arguments.
The attached documentation patch was originally intended to go along with
CVE-2026-6637, but we ultimately scoped it down to only the
security-relevant parts. This should be back-patched to v14. Note that we
are preparing to removing refint completely in v20, but IMHO this doc
update is still worth doing.
Thoughts?
--
nathan
From 45e236f4f66726146983c15c936997bb358162ba Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Tue, 26 May 2026 11:46:45 -0500
Subject: [PATCH v1 1/1] expand refint docs with usage info
---
doc/src/sgml/contrib-spi.sgml | 58 ++++++++++++++++++++++++++++++++++-
1 file changed, 57 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/contrib-spi.sgml b/doc/src/sgml/contrib-spi.sgml
index 6fa9479d1b9..7e4e580bc74 100644
--- a/doc/src/sgml/contrib-spi.sgml
+++ b/doc/src/sgml/contrib-spi.sgml
@@ -34,6 +34,14 @@
key mechanism, of course, but the module is still useful as an example.)
</para>
+ <note>
+ <para>
+ <filename>refint</filename> requires a
+ <link linkend="ddl-schemas-patterns">secure schema usage pattern</link> and
+ data types where the equality operator is named <literal>=</literal>.
+ </para>
+ </note>
+
<para>
<function>check_primary_key()</function> checks the referencing table.
To use, create an <literal>AFTER INSERT OR UPDATE</literal> trigger using this
@@ -44,6 +52,29 @@
keys, create a trigger for each reference.
</para>
+ <note>
+ <para>
+ The <emphasis>referenced</emphasis> table name and column name arguments to
+ <function>check_primary_key()</function> are copied as-is into internally
+ generated SQL statements and therefore must be double-quoted by the user as
+ necessary in the <command>CREATE TRIGGER</command> command. See
+ <xref linkend="sql-syntax-identifiers"/> for more information about quoting
+ SQL identifiers. Conversely, the <emphasis>referencing</emphasis> table
+ column name arguments should not be double quoted. See the following mock
+ example of proper use of <function>check_primary_key()</function>:
+<programlisting>
+CREATE TRIGGER mytrigger
+AFTER INSERT OR UPDATE ON referencing_table
+FOR EACH ROW EXECUTE PROCEDURE
+check_primary_key (
+ 'column A', 'column B', -- referencing table columns
+ 'myschema."referenced table"', -- referenced table
+ '"column A"', '"column B"' -- referenced table columns
+);
+</programlisting>
+ </para>
+ </note>
+
<para>
<function>check_foreign_key()</function> checks the referenced table.
To use, create an <literal>AFTER DELETE OR UPDATE</literal> trigger using this
@@ -53,13 +84,38 @@
(<literal>cascade</literal> — to delete the referencing row,
<literal>restrict</literal> — to abort transaction if referencing keys
exist, <literal>setnull</literal> — to set referencing key fields to null),
- the triggered table's column names which form the primary/unique key, then
+ the referenced table's column names which form the primary/unique key, then
the referencing table name and column names (repeated for as many
referencing tables as were specified by first argument). Note that the
primary/unique key columns should be marked NOT NULL and should have a
unique index.
</para>
+ <note>
+ <para>
+ The <emphasis>referencing</emphasis> table name and column name arguments
+ to <function>check_foreign_key()</function> are copied as-is into
+ internally generated SQL statements and therefore must be double-quoted by
+ the user as necessary in the <command>CREATE TRIGGER</command> command.
+ See <xref linkend="sql-syntax-identifiers"/> for more information about
+ quoting SQL identifiers. Conversely, the <emphasis>referenced</emphasis>
+ table column name arguments should not be double quoted. See the following
+ mock example of proper use of <function>check_foreign_key()</function>:
+<programlisting>
+CREATE TRIGGER mytrigger
+AFTER DELETE OR UPDATE ON referenced_table
+FOR EACH ROW EXECUTE PROCEDURE
+check_foreign_key (
+ 1, -- number of referencing tables
+ 'cascade', -- action
+ 'column A', 'column B', -- referenced table columns
+ 'myschema."referencing table"', -- referencing table
+ '"column A"', '"column B"' -- referencing table columns
+);
+</programlisting>
+ </para>
+ </note>
+
<para>
Note that if these triggers are executed from
another <literal>BEFORE</literal> trigger, they can fail unexpectedly. For
--
2.50.1 (Apple Git-155)
Attachments:
[text/plain] v1-0001-expand-refint-docs-with-usage-info.patch (4.2K, ../../ahXP7z7nsfGPOZ3T@nathan/2-v1-0001-expand-refint-docs-with-usage-info.patch)
download | inline diff:
From 45e236f4f66726146983c15c936997bb358162ba Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Tue, 26 May 2026 11:46:45 -0500
Subject: [PATCH v1 1/1] expand refint docs with usage info
---
doc/src/sgml/contrib-spi.sgml | 58 ++++++++++++++++++++++++++++++++++-
1 file changed, 57 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/contrib-spi.sgml b/doc/src/sgml/contrib-spi.sgml
index 6fa9479d1b9..7e4e580bc74 100644
--- a/doc/src/sgml/contrib-spi.sgml
+++ b/doc/src/sgml/contrib-spi.sgml
@@ -34,6 +34,14 @@
key mechanism, of course, but the module is still useful as an example.)
</para>
+ <note>
+ <para>
+ <filename>refint</filename> requires a
+ <link linkend="ddl-schemas-patterns">secure schema usage pattern</link> and
+ data types where the equality operator is named <literal>=</literal>.
+ </para>
+ </note>
+
<para>
<function>check_primary_key()</function> checks the referencing table.
To use, create an <literal>AFTER INSERT OR UPDATE</literal> trigger using this
@@ -44,6 +52,29 @@
keys, create a trigger for each reference.
</para>
+ <note>
+ <para>
+ The <emphasis>referenced</emphasis> table name and column name arguments to
+ <function>check_primary_key()</function> are copied as-is into internally
+ generated SQL statements and therefore must be double-quoted by the user as
+ necessary in the <command>CREATE TRIGGER</command> command. See
+ <xref linkend="sql-syntax-identifiers"/> for more information about quoting
+ SQL identifiers. Conversely, the <emphasis>referencing</emphasis> table
+ column name arguments should not be double quoted. See the following mock
+ example of proper use of <function>check_primary_key()</function>:
+<programlisting>
+CREATE TRIGGER mytrigger
+AFTER INSERT OR UPDATE ON referencing_table
+FOR EACH ROW EXECUTE PROCEDURE
+check_primary_key (
+ 'column A', 'column B', -- referencing table columns
+ 'myschema."referenced table"', -- referenced table
+ '"column A"', '"column B"' -- referenced table columns
+);
+</programlisting>
+ </para>
+ </note>
+
<para>
<function>check_foreign_key()</function> checks the referenced table.
To use, create an <literal>AFTER DELETE OR UPDATE</literal> trigger using this
@@ -53,13 +84,38 @@
(<literal>cascade</literal> — to delete the referencing row,
<literal>restrict</literal> — to abort transaction if referencing keys
exist, <literal>setnull</literal> — to set referencing key fields to null),
- the triggered table's column names which form the primary/unique key, then
+ the referenced table's column names which form the primary/unique key, then
the referencing table name and column names (repeated for as many
referencing tables as were specified by first argument). Note that the
primary/unique key columns should be marked NOT NULL and should have a
unique index.
</para>
+ <note>
+ <para>
+ The <emphasis>referencing</emphasis> table name and column name arguments
+ to <function>check_foreign_key()</function> are copied as-is into
+ internally generated SQL statements and therefore must be double-quoted by
+ the user as necessary in the <command>CREATE TRIGGER</command> command.
+ See <xref linkend="sql-syntax-identifiers"/> for more information about
+ quoting SQL identifiers. Conversely, the <emphasis>referenced</emphasis>
+ table column name arguments should not be double quoted. See the following
+ mock example of proper use of <function>check_foreign_key()</function>:
+<programlisting>
+CREATE TRIGGER mytrigger
+AFTER DELETE OR UPDATE ON referenced_table
+FOR EACH ROW EXECUTE PROCEDURE
+check_foreign_key (
+ 1, -- number of referencing tables
+ 'cascade', -- action
+ 'column A', 'column B', -- referenced table columns
+ 'myschema."referencing table"', -- referencing table
+ '"column A"', '"column B"' -- referencing table columns
+);
+</programlisting>
+ </para>
+ </note>
+
<para>
Note that if these triggers are executed from
another <literal>BEFORE</literal> trigger, they can fail unexpectedly. For
--
2.50.1 (Apple Git-155)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: expand refint docs with usage info
@ 2026-05-26 17:04 Christoph Berg <myon@debian.org>
parent: Nathan Bossart <nathandbossart@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: Christoph Berg @ 2026-05-26 17:04 UTC (permalink / raw)
To: Nathan Bossart <nathandbossart@gmail.com>; +Cc: pgsql-hackers
Re: Nathan Bossart
> This should be back-patched to v14. Note that we are preparing to
> removing refint completely in v20, but IMHO this doc update is still
> worth doing.
I suggest mentioning the deprecation in the same place.
Christoph
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: expand refint docs with usage info
@ 2026-05-26 17:34 SATYANARAYANA NARLAPURAM <satyanarlapuram@gmail.com>
parent: Nathan Bossart <nathandbossart@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: SATYANARAYANA NARLAPURAM @ 2026-05-26 17:34 UTC (permalink / raw)
To: Nathan Bossart <nathandbossart@gmail.com>; +Cc: pgsql-hackers
Hi,
On Tue, May 26, 2026 at 9:53 AM Nathan Bossart <nathandbossart@gmail.com>
wrote:
> The security team has received a couple of reports about potential SQL
> injection opportunities via refint's trigger arguments. We discussed this
> while preparing CVE-2026-6637 and concluded that forcibly quoting these
> arguments would be much more likely to break working code than to prevent
> any exploits. Unlike data values, the table/column names come from trigger
> arguments, and there is little reason for a trigger author to put hostile
> inputs into those arguments.
>
> The attached documentation patch was originally intended to go along with
> CVE-2026-6637, but we ultimately scoped it down to only the
> security-relevant parts. This should be back-patched to v14. Note that we
> are preparing to removing refint completely in v20, but IMHO this doc
> update is still worth doing.
>
> Thoughts?
>
LGTM.
Thanks,
Satya
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: expand refint docs with usage info
@ 2026-05-26 19:39 Nathan Bossart <nathandbossart@gmail.com>
parent: Christoph Berg <myon@debian.org>
0 siblings, 1 reply; 5+ messages in thread
From: Nathan Bossart @ 2026-05-26 19:39 UTC (permalink / raw)
To: Christoph Berg <myon@debian.org>; pgsql-hackers
On Tue, May 26, 2026 at 07:04:30PM +0200, Christoph Berg wrote:
> Re: Nathan Bossart
>> This should be back-patched to v14. Note that we are preparing to
>> removing refint completely in v20, but IMHO this doc update is still
>> worth doing.
>
> I suggest mentioning the deprecation in the same place.
Agreed, but I'd rather leave that for the removal effort.
--
nathan
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: expand refint docs with usage info
@ 2026-06-08 15:36 Nathan Bossart <nathandbossart@gmail.com>
parent: Nathan Bossart <nathandbossart@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Nathan Bossart @ 2026-06-08 15:36 UTC (permalink / raw)
To: Christoph Berg <myon@debian.org>; pgsql-hackers
Committed.
--
nathan
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2026-06-08 15:36 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-05-26 16:53 expand refint docs with usage info Nathan Bossart <nathandbossart@gmail.com>
2026-05-26 17:04 ` Christoph Berg <myon@debian.org>
2026-05-26 19:39 ` Nathan Bossart <nathandbossart@gmail.com>
2026-06-08 15:36 ` Nathan Bossart <nathandbossart@gmail.com>
2026-05-26 17:34 ` SATYANARAYANA NARLAPURAM <satyanarlapuram@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox