public inbox for [email protected]
help / color / mirror / Atom feedFix unqualified catalog references in psql describe queries
14+ messages / 7 participants
[nested] [flat]
* Fix unqualified catalog references in psql describe queries
@ 2026-06-08 08:46 Chao Li <[email protected]>
0 siblings, 2 replies; 14+ messages in thread
From: Chao Li @ 2026-06-08 08:46 UTC (permalink / raw)
To: Postgres hackers <[email protected]>; +Cc: Fujii Masao <[email protected]>; Peter Eisentraut <[email protected]>; Jeff Davis <[email protected]>; Ashutosh Bapat <[email protected]>; Amit Kapila <[email protected]>
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)
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Fix unqualified catalog references in psql describe queries
@ 2026-06-09 07:01 Tingchuan Sun <[email protected]>
parent: Chao Li <[email protected]>
1 sibling, 0 replies; 14+ messages in thread
From: Tingchuan Sun @ 2026-06-09 07:01 UTC (permalink / raw)
To: Chao Li <[email protected]>; Postgres hackers <[email protected]>; +Cc: Fujii Masao <[email protected]>; Peter Eisentraut <[email protected]>; Jeff Davis <[email protected]>; Ashutosh Bapat <[email protected]>; Amit Kapila <[email protected]>
在 2026/6/8 16:46, Chao Li 写道:
> 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.
I just tried the repro. I never knew a way to make \dRs+ to output wrong data like this, this is interesting.
> 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/
>
>
>
The patch looks good to me. I applied the patch locally and verified it with “make check-world”.
Regards,
Tingchuan Sun
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Fix unqualified catalog references in psql describe queries
@ 2026-06-09 07:08 Michael Paquier <[email protected]>
parent: Chao Li <[email protected]>
1 sibling, 1 reply; 14+ messages in thread
From: Michael Paquier @ 2026-06-09 07:08 UTC (permalink / raw)
To: Chao Li <[email protected]>; +Cc: Postgres hackers <[email protected]>; Fujii Masao <[email protected]>; Peter Eisentraut <[email protected]>; Jeff Davis <[email protected]>; Ashutosh Bapat <[email protected]>; Amit Kapila <[email protected]>
On Mon, Jun 08, 2026 at 04:46:46PM +0800, Chao Li wrote:
> 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.
Which is always annoying.. Will fix and double-check later, thanks.
--
Michael
Attachments:
[application/pgp-signature] signature.asc (833B, ../../[email protected]/2-signature.asc)
download
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Fix unqualified catalog references in psql describe queries
@ 2026-06-09 09:08 Bertrand Drouvot <[email protected]>
parent: Michael Paquier <[email protected]>
0 siblings, 1 reply; 14+ messages in thread
From: Bertrand Drouvot @ 2026-06-09 09:08 UTC (permalink / raw)
To: Michael Paquier <[email protected]>; +Cc: Chao Li <[email protected]>; Postgres hackers <[email protected]>; Fujii Masao <[email protected]>; Peter Eisentraut <[email protected]>; Jeff Davis <[email protected]>; Ashutosh Bapat <[email protected]>; Amit Kapila <[email protected]>
Hi,
On Tue, Jun 09, 2026 at 04:08:26PM +0900, Michael Paquier wrote:
> On Mon, Jun 08, 2026 at 04:46:46PM +0800, Chao Li wrote:
> > 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.
>
> Which is always annoying.. Will fix and double-check later, thanks.
Now I wonder if we shoud not "protect" the operators too. They could also
lead to wrong results (if not worst).
Regards,
--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Fix unqualified catalog references in psql describe queries
@ 2026-06-10 01:57 Michael Paquier <[email protected]>
parent: Bertrand Drouvot <[email protected]>
0 siblings, 1 reply; 14+ messages in thread
From: Michael Paquier @ 2026-06-10 01:57 UTC (permalink / raw)
To: Bertrand Drouvot <[email protected]>; +Cc: Chao Li <[email protected]>; Postgres hackers <[email protected]>; Fujii Masao <[email protected]>; Peter Eisentraut <[email protected]>; Jeff Davis <[email protected]>; Ashutosh Bapat <[email protected]>; Amit Kapila <[email protected]>
On Tue, Jun 09, 2026 at 09:08:50AM +0000, Bertrand Drouvot wrote:
> Now I wonder if we shoud not "protect" the operators too. They could also
> lead to wrong results (if not worst).
Kind of true. Still we have been pretty lax about the operators as
they also lead to less readable queries.
There was one spot missing with string_agg() two lines down one of the
pg_get_expr() changes. The spots for relation and function
qualifications are worth addressing anyway, and consistent with the
file style. Fixed the extra spot I have noticed, and applied the
result on HEAD.
--
Michael
Attachments:
[application/pgp-signature] signature.asc (833B, ../../[email protected]/2-signature.asc)
download
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Fix unqualified catalog references in psql describe queries
@ 2026-06-10 02:12 Tom Lane <[email protected]>
parent: Michael Paquier <[email protected]>
0 siblings, 1 reply; 14+ messages in thread
From: Tom Lane @ 2026-06-10 02:12 UTC (permalink / raw)
To: Michael Paquier <[email protected]>; +Cc: Bertrand Drouvot <[email protected]>; Chao Li <[email protected]>; Postgres hackers <[email protected]>; Fujii Masao <[email protected]>; Peter Eisentraut <[email protected]>; Jeff Davis <[email protected]>; Ashutosh Bapat <[email protected]>; Amit Kapila <[email protected]>
Michael Paquier <[email protected]> writes:
> On Tue, Jun 09, 2026 at 09:08:50AM +0000, Bertrand Drouvot wrote:
>> Now I wonder if we shoud not "protect" the operators too. They could also
>> lead to wrong results (if not worst).
> Kind of true. Still we have been pretty lax about the operators as
> they also lead to less readable queries.
We disclaimed security against odd search_paths for these queries long ago,
precisely because wrapping every operator in PG_OPERATOR(pg_catalog.*)
would be far too tedious and destructive of readability --- not to
mention that there are some syntaxes such as IN that don't even offer
the option to do that.
I'm okay with schema-qualifying these table references, mainly because
that preserves consistency with historical style here. But let's not
go further than that.
regards, tom lane
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Fix unqualified catalog references in psql describe queries
@ 2026-06-10 06:16 Bertrand Drouvot <[email protected]>
parent: Tom Lane <[email protected]>
0 siblings, 1 reply; 14+ messages in thread
From: Bertrand Drouvot @ 2026-06-10 06:16 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Michael Paquier <[email protected]>; Chao Li <[email protected]>; Postgres hackers <[email protected]>; Fujii Masao <[email protected]>; Peter Eisentraut <[email protected]>; Jeff Davis <[email protected]>; Ashutosh Bapat <[email protected]>; Amit Kapila <[email protected]>
Hi,
On Tue, Jun 09, 2026 at 10:12:48PM -0400, Tom Lane wrote:
> Michael Paquier <[email protected]> writes:
> > On Tue, Jun 09, 2026 at 09:08:50AM +0000, Bertrand Drouvot wrote:
> >> Now I wonder if we shoud not "protect" the operators too. They could also
> >> lead to wrong results (if not worst).
>
> > Kind of true. Still we have been pretty lax about the operators as
> > they also lead to less readable queries.
>
> We disclaimed security against odd search_paths for these queries long ago,
> precisely because wrapping every operator in PG_OPERATOR(pg_catalog.*)
> would be far too tedious and destructive of readability --- not to
> mention that there are some syntaxes such as IN that don't even offer
> the option to do that.
I do agree that doing so would "destroy" the readability. I did not look in detail,
but what about forcing ALWAYS_SECURE_SEARCH_PATH_SQL before the queries and
restore the search_path once the query is done? (that way that would not impact
the readability)
Regards,
--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Fix unqualified catalog references in psql describe queries
@ 2026-06-10 08:33 Álvaro Herrera <[email protected]>
parent: Bertrand Drouvot <[email protected]>
0 siblings, 1 reply; 14+ messages in thread
From: Álvaro Herrera @ 2026-06-10 08:33 UTC (permalink / raw)
To: Bertrand Drouvot <[email protected]>; Tom Lane <[email protected]>; +Cc: Michael Paquier <[email protected]>; Chao Li <[email protected]>; L. pgsql-hackers <[email protected]>; Fujii Masao <[email protected]>; Peter Eisentraut <[email protected]>; Jeff Davis <[email protected]>; Ashutosh Bapat <[email protected]>; Amit Kapila <[email protected]>
On 2026-06-10, Bertrand Drouvot wrote:
> I do agree that doing so would "destroy" the readability. I did not
> look in detail,
> but what about forcing ALWAYS_SECURE_SEARCH_PATH_SQL before the queries
> and
> restore the search_path once the query is done? (that way that would
> not impact
> the readability)
I think we should just ditch the idea that operators live in schemas.
--
Álvaro Herrera
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Fix unqualified catalog references in psql describe queries
@ 2026-06-10 13:51 Tom Lane <[email protected]>
parent: Álvaro Herrera <[email protected]>
0 siblings, 2 replies; 14+ messages in thread
From: Tom Lane @ 2026-06-10 13:51 UTC (permalink / raw)
To: Álvaro Herrera <[email protected]>; +Cc: Bertrand Drouvot <[email protected]>; Michael Paquier <[email protected]>; Chao Li <[email protected]>; L. pgsql-hackers <[email protected]>; Fujii Masao <[email protected]>; Peter Eisentraut <[email protected]>; Jeff Davis <[email protected]>; Ashutosh Bapat <[email protected]>; Amit Kapila <[email protected]>
=?UTF-8?Q?=C3=81lvaro_Herrera?= <[email protected]> writes:
> I think we should just ditch the idea that operators live in schemas.
How would you do that without removing user-defined operators
altogether? (And thereby breaking most extensions.)
regards, tom lane
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Fix unqualified catalog references in psql describe queries
@ 2026-06-10 22:17 Michael Paquier <[email protected]>
parent: Tom Lane <[email protected]>
1 sibling, 0 replies; 14+ messages in thread
From: Michael Paquier @ 2026-06-10 22:17 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Álvaro Herrera <[email protected]>; Bertrand Drouvot <[email protected]>; Chao Li <[email protected]>; L. pgsql-hackers <[email protected]>; Fujii Masao <[email protected]>; Peter Eisentraut <[email protected]>; Jeff Davis <[email protected]>; Ashutosh Bapat <[email protected]>; Amit Kapila <[email protected]>
On Wed, Jun 10, 2026 at 09:51:03AM -0400, Tom Lane wrote:
> =?UTF-8?Q?=C3=81lvaro_Herrera?= <[email protected]> writes:
>> I think we should just ditch the idea that operators live in schemas.
>
> How would you do that without removing user-defined operators
> altogether? (And thereby breaking most extensions.)
I am ready to estimate that the amount of existing users that would be
pissed after such a removal would be higher than the number of users
feeling safer with their queries after this operator capability is
removed. All of them are probably already using their own search_path
for queries they care about anyway.
--
Michael
Attachments:
[application/pgp-signature] signature.asc (833B, ../../[email protected]/2-signature.asc)
download
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Fix unqualified catalog references in psql describe queries
@ 2026-06-15 17:08 Álvaro Herrera <[email protected]>
parent: Tom Lane <[email protected]>
1 sibling, 1 reply; 14+ messages in thread
From: Álvaro Herrera @ 2026-06-15 17:08 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Bertrand Drouvot <[email protected]>; Michael Paquier <[email protected]>; Chao Li <[email protected]>; L. pgsql-hackers <[email protected]>; Fujii Masao <[email protected]>; Peter Eisentraut <[email protected]>; Jeff Davis <[email protected]>; Ashutosh Bapat <[email protected]>; Amit Kapila <[email protected]>
On 2026-Jun-10, Tom Lane wrote:
> =?UTF-8?Q?=C3=81lvaro_Herrera?= <[email protected]> writes:
> > I think we should just ditch the idea that operators live in schemas.
>
> How would you do that without removing user-defined operators
> altogether? (And thereby breaking most extensions.)
My proposal would be that all operators, both system-defined as well as
user-defined, live in a single namespace -- not that we forbid them from
being created. I expect extensions mostly create operators for the data
types they themselves define, not for existing system datatypes.
I think the idea of public.=(int,int) being different from
pg_catalog.=(int,int) is just too dangerous and trips people up without
giving much valuable functionality. If the extension offers
=(complex,complex) then that's fine: we would still have overloading per
the type system.
I may be missing something though. Care to point out what it is?
--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
"El que vive para el futuro es un iluso, y el que vive para el pasado,
un imbécil" (Luis Adler, "Los tripulantes de la noche")
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Fix unqualified catalog references in psql describe queries
@ 2026-06-15 17:22 Tom Lane <[email protected]>
parent: Álvaro Herrera <[email protected]>
0 siblings, 1 reply; 14+ messages in thread
From: Tom Lane @ 2026-06-15 17:22 UTC (permalink / raw)
To: Álvaro Herrera <[email protected]>; +Cc: Bertrand Drouvot <[email protected]>; Michael Paquier <[email protected]>; Chao Li <[email protected]>; L. pgsql-hackers <[email protected]>; Fujii Masao <[email protected]>; Peter Eisentraut <[email protected]>; Jeff Davis <[email protected]>; Ashutosh Bapat <[email protected]>; Amit Kapila <[email protected]>
=?utf-8?Q?=C3=81lvaro?= Herrera <[email protected]> writes:
> My proposal would be that all operators, both system-defined as well as
> user-defined, live in a single namespace -- not that we forbid them from
> being created.
Exactly how does that improve anyone's life? It will certainly not
improve query security, rather the reverse. You could no longer put
less-trusted stuff into a schema that's not in your search_path.
Yes, it would stop people from creating operators that are exact
duplicates of system operators, but those are not the problem:
user-defined operators like that are already masked by the lookup
rules, assuming that pg_catalog is searched first as is the normal
case. The thing that is dangerous is a user-defined operator that
is made to capture cases that lack an exact system-operator match
(say, varchar = text). AFAICS your proposal puts those on exactly the
same footing as system-defined operators, and there is no recourse.
regards, tom lane
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Fix unqualified catalog references in psql describe queries
@ 2026-06-15 21:21 Álvaro Herrera <[email protected]>
parent: Tom Lane <[email protected]>
0 siblings, 1 reply; 14+ messages in thread
From: Álvaro Herrera @ 2026-06-15 21:21 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Bertrand Drouvot <[email protected]>; Michael Paquier <[email protected]>; Chao Li <[email protected]>; L. pgsql-hackers <[email protected]>; Fujii Masao <[email protected]>; Peter Eisentraut <[email protected]>; Jeff Davis <[email protected]>; Ashutosh Bapat <[email protected]>; Amit Kapila <[email protected]>
On 2026-Jun-15, Tom Lane wrote:
> =?utf-8?Q?=C3=81lvaro?= Herrera <[email protected]> writes:
> > My proposal would be that all operators, both system-defined as well as
> > user-defined, live in a single namespace -- not that we forbid them from
> > being created.
>
> Exactly how does that improve anyone's life? It will certainly not
> improve query security, rather the reverse. You could no longer put
> less-trusted stuff into a schema that's not in your search_path.
I am imagining that only database owners would be able to create
operators. There isn't any case for allowing that for anybody else,
ISTM. How much need is there for "less-trusted" operators, really?
As long as it's not restricted to superusers, there is flexibility
enough.
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Fix unqualified catalog references in psql describe queries
@ 2026-07-08 00:04 Jeff Davis <[email protected]>
parent: Álvaro Herrera <[email protected]>
0 siblings, 0 replies; 14+ messages in thread
From: Jeff Davis @ 2026-07-08 00:04 UTC (permalink / raw)
To: Álvaro Herrera <[email protected]>; Tom Lane <[email protected]>; +Cc: Bertrand Drouvot <[email protected]>; Michael Paquier <[email protected]>; Chao Li <[email protected]>; L. pgsql-hackers <[email protected]>; Fujii Masao <[email protected]>; Peter Eisentraut <[email protected]>; Ashutosh Bapat <[email protected]>; Amit Kapila <[email protected]>
On Mon, 2026-06-15 at 23:21 +0200, Álvaro Herrera wrote:
> I am imagining that only database owners would be able to create
> operators. There isn't any case for allowing that for anybody else,
> ISTM. How much need is there for "less-trusted" operators, really?
That's probably true in almost all cases. Operators are generally
defined as part of an extension that offers interesting types and
opclasses, and these are C extensions anyway. I haven't ever seen
someone define a convenience operator like they might define a
convenience function.
Perhaps some use it for some clever hacks around unmodifiable
application code or something?
Regards,
Jeff Davis
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2026-07-08 00:04 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-06-08 08:46 Fix unqualified catalog references in psql describe queries Chao Li <[email protected]>
2026-06-09 07:01 ` Tingchuan Sun <[email protected]>
2026-06-09 07:08 ` Michael Paquier <[email protected]>
2026-06-09 09:08 ` Bertrand Drouvot <[email protected]>
2026-06-10 01:57 ` Michael Paquier <[email protected]>
2026-06-10 02:12 ` Tom Lane <[email protected]>
2026-06-10 06:16 ` Bertrand Drouvot <[email protected]>
2026-06-10 08:33 ` Álvaro Herrera <[email protected]>
2026-06-10 13:51 ` Tom Lane <[email protected]>
2026-06-10 22:17 ` Michael Paquier <[email protected]>
2026-06-15 17:08 ` Álvaro Herrera <[email protected]>
2026-06-15 17:22 ` Tom Lane <[email protected]>
2026-06-15 21:21 ` Álvaro Herrera <[email protected]>
2026-07-08 00:04 ` Jeff Davis <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox