public inbox for [email protected]
help / color / mirror / Atom feedFrom: Chao Li <[email protected]>
To: Postgres hackers <[email protected]>
Cc: Fujii Masao <[email protected]>
Cc: Peter Eisentraut <[email protected]>
Cc: Jeff Davis <[email protected]>
Cc: Ashutosh Bapat <[email protected]>
Cc: Amit Kapila <[email protected]>
Subject: Fix unqualified catalog references in psql describe queries
Date: Mon, 8 Jun 2026 16:46:46 +0800
Message-ID: <[email protected]> (raw)
Hi,
While testing "[aecc55866] psql: Show comments in \dRp+, \dRs+, and \dX+", I noticed a small issue that was actually introduced by "[8185bb534] CREATE SUBSCRIPTION … SERVER”.
The problem is that, when querying pg_foreign_server, it misses the "pg_catalog" schema qualification:
```
appendPQExpBuffer(&buf,
", (select srvname from pg_foreign_server where oid=subserver) AS \"%s\"\n",
gettext_noop("Server"));
```
This is not a big problem, but it provides a way to pollute the result of \dRs+ by adding a fake pg_foreign_server earlier in search_path. See this repro:
1. Setup: create a server and a sub
```
evantest=# create extension postgres_fdw;
CREATE EXTENSION
evantest=# create publication pub;
CREATE PUBLICATION
evantest=# create server s foreign data wrapper postgres_fdw options (dbname 'postgres');
CREATE SERVER
evantest=# create user mapping for current_user server s;
CREATE USER MAPPING
evantest=# create subscription sub server s publication pub with (connect=false, slot_name=none);
WARNING: subscription was created, but is not connected
HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and alter the subscription to refresh publications.
CREATE SUBSCRIPTION
evantest=# \dRs+ sub;
List of subscriptions
Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Server | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description
------+-------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------+--------------------+------------------------+------------------+--------------------+----------+------------------+------------+-------------
sub | chaol | f | {pub} | f | parallel | d | f | any | t | f | f | s | f | 0 | f | off | | -1 | 0/00000000 |
(1 row)
```
As shown above, “Server” column shows the correct server name “s”.
2. Now, pollute the result
```
evantest=# create temp table pg_foreign_server (oid oid, srvname name);
CREATE TABLE
evantest=# insert into pg_foreign_server select oid, 'fake_s'::name from pg_catalog.pg_foreign_server where srvname='s';
INSERT 0 1
evantest=# \dRs+ sub;
List of subscriptions
Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Server | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description
------+-------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------+--------------------+------------------------+------------------+--------------------+----------+------------------+------------+-------------
sub | chaol | f | {pub} | f | parallel | d | f | any | t | f | f | fake_s | f | 0 | f | off | | -1 | 0/00000000 |
(1 row)
```
Now, the "Server" column shows the fake server name that I supplied.
The fix is to add the schema qualification, using "pg_catalog.pg_foreign_server". In describe.c, catalog objects are generally referenced by qualified names. I found 3 other occurrences that missed schema qualification, so I fixed them as well.
There are 4 spots in total. Two are v19-new, oversights of 8185bb53476378443240d57f7d844347d5fae1bf and 2f094e7ac691abc9d2fe0f4dcf0feac4a6ce1d9c. The other two are older and might be worth back-patching. So I split the fix into 2 commits: 0001 is v19-new, and 0002 is a back-patch candidate.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
Attachments:
[application/octet-stream] v1-0001-psql-Schema-qualify-catalog-references-in-describ.patch (2.4K, ../[email protected]/2-v1-0001-psql-Schema-qualify-catalog-references-in-describ.patch)
download | inline diff:
From 07876f501725a48d75b6da492b24dbda7fd066ec Mon Sep 17 00:00:00 2001
From: "Chao Li (Evan)" <[email protected]>
Date: Mon, 8 Jun 2026 16:09:07 +0800
Subject: [PATCH v1 1/2] psql: Schema-qualify catalog references in describe
queries
Some recently added describe queries missed pg_catalog qualification for
catalog relations. This could allow user objects earlier in search_path,
including temporary tables, to affect psql describe output.
Fix the subscription describe query to use pg_catalog.pg_foreign_server,
and fix the property graph describe query to use pg_catalog-qualified
catalog relations.
These were oversights in commits 8185bb53 and 2f094e7a.
Author: Chao Li <[email protected]>
---
src/bin/psql/describe.c | 12 ++++++------
1 file changed, 6 insertions(+), 6 deletions(-)
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index e1449654f96..a245a0a7912 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -1950,11 +1950,11 @@ describeOneTableDetails(const char *schemaname,
"\n when " CppAsString2(PGEKIND_EDGE) " then 'edge' end AS \"%s\","
"\n s.pgealias as \"%s\","
"\n d.pgealias as \"%s\""
- "\n FROM pg_propgraph_element e"
- "\n INNER JOIN pg_class c ON c.oid = e.pgerelid"
- "\n INNER JOIN pg_namespace n ON c.relnamespace = n.oid"
- "\n LEFT JOIN pg_propgraph_element s ON e.pgesrcvertexid = s.oid"
- "\n LEFT JOIN pg_propgraph_element d ON e.pgedestvertexid = d.oid"
+ "\n FROM pg_catalog.pg_propgraph_element e"
+ "\n INNER JOIN pg_catalog.pg_class c ON c.oid = e.pgerelid"
+ "\n INNER JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid"
+ "\n LEFT JOIN pg_catalog.pg_propgraph_element s ON e.pgesrcvertexid = s.oid"
+ "\n LEFT JOIN pg_catalog.pg_propgraph_element d ON e.pgedestvertexid = d.oid"
"\n WHERE e.pgepgid = '%s'"
"\n ORDER BY e.pgealias",
gettext_noop("Element Alias"),
@@ -7165,7 +7165,7 @@ describeSubscriptions(const char *pattern, bool verbose)
if (pset.sversion >= 190000)
{
appendPQExpBuffer(&buf,
- ", (select srvname from pg_foreign_server where oid=subserver) AS \"%s\"\n",
+ ", (select srvname from pg_catalog.pg_foreign_server where oid=subserver) AS \"%s\"\n",
gettext_noop("Server"));
appendPQExpBuffer(&buf,
--
2.50.1 (Apple Git-155)
[application/octet-stream] v1-0002-psql-Schema-qualify-pg_get_expr-in-publication-de.patch (1.7K, ../[email protected]/3-v1-0002-psql-Schema-qualify-pg_get_expr-in-publication-de.patch)
download | inline diff:
From bb58dcb7ce8386dcff84efde6a7feab7bac0eaa8 Mon Sep 17 00:00:00 2001
From: "Chao Li (Evan)" <[email protected]>
Date: Mon, 8 Jun 2026 16:11:51 +0800
Subject: [PATCH v1 2/2] psql: Schema-qualify pg_get_expr() in publication
describe queries
The publication describe queries used unqualified pg_get_expr() calls when
showing row filters. A user-defined function earlier in search_path could
therefore affect psql describe output.
Use pg_catalog.pg_get_expr(), as nearby describe queries already do.
Author: Chao Li <[email protected]>
---
src/bin/psql/describe.c | 4 ++--
1 file changed, 2 insertions(+), 2 deletions(-)
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index a245a0a7912..015586b9895 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -3189,7 +3189,7 @@ describeOneTableDetails(const char *schemaname,
"WHERE pc.oid ='%s' and pg_catalog.pg_relation_is_publishable('%s')\n"
"UNION\n"
"SELECT pubname\n"
- " , pg_get_expr(pr.prqual, c.oid)\n"
+ " , pg_catalog.pg_get_expr(pr.prqual, c.oid)\n"
" , (CASE WHEN pr.prattrs IS NOT NULL THEN\n"
" (SELECT string_agg(attname, ', ')\n"
" FROM pg_catalog.generate_series(0, pg_catalog.array_upper(pr.prattrs::pg_catalog.int2[], 1)) s,\n"
@@ -6995,7 +6995,7 @@ describePublications(const char *pattern)
if (pset.sversion >= 150000)
{
appendPQExpBufferStr(&buf,
- ", pg_get_expr(pr.prqual, c.oid)");
+ ", pg_catalog.pg_get_expr(pr.prqual, c.oid)");
appendPQExpBufferStr(&buf,
", (CASE WHEN pr.prattrs IS NOT NULL THEN\n"
" pg_catalog.array_to_string("
--
2.50.1 (Apple Git-155)
view thread (14+ messages) latest in thread
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], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Fix unqualified catalog references in psql describe queries
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