psql internals queries breaks in presence of user-defined operators
Hi!
If a user creates a malformed operator in theirs database, like:
```
create or replace function f (name, name) RETURNS boolean AS $$ begin
RETURN $1::text = $2::text; end; $$ LANGUAGE plpgsql;
CREATE OPERATOR public.!~ ( LEFTARG = name, RIGHTARG = name, FUNCTION = f);
```
then psql's meta-commands queries like \du stop working.
That's because the catalog misses operators for leftarg = name,
rightarg = name and oprname = '!~'.
I think this can be enhanced a little by schema-qualifying psql's
internal query.
While this issue is fully the user's fault, I think psql can still be
a little more conservative in operator selection, and work anyways.
PFA POC patch.
This is not a security issue, per:
https://www.postgresql.org/docs/current/app-psql.html says:
If untrusted users have access to a database that has not adopted a secure
schema usage pattern, begin your session by removing publicly-writable
schemas from search_path.
--
Best regards,
Kirill Reshke
Attachments:
v1-0001-Schema-qualify-psql-internal-query-operators.patchapplication/octet-stream; name=v1-0001-Schema-qualify-psql-internal-query-operators.patchDownload+5-6
Hi,
On Thu, 25 Jun 2026 at 12:03, Kirill Reshke <reshkekirill@gmail.com> wrote:
Hi!
If a user creates a malformed operator in theirs database, like:```
create or replace function f (name, name) RETURNS boolean AS $$ begin
RETURN $1::text = $2::text; end; $$ LANGUAGE plpgsql;
CREATE OPERATOR public.!~ ( LEFTARG = name, RIGHTARG = name, FUNCTION = f);
```then psql's meta-commands queries like \du stop working.
That's because the catalog misses operators for leftarg = name,
rightarg = name and oprname = '!~'.I think this can be enhanced a little by schema-qualifying psql's
internal query.
While this issue is fully the user's fault, I think psql can still be
a little more conservative in operator selection, and work anyways.
PFA POC patch.
Thanks for the patch!
I reviewed it and the changes look good to me.\du, \dn, \dt work
with a shadowing operation present.
Would it be worth adding a test (maybe in the psql regression suite) that
the
describe queries still return the expected rows when such an operator
exists?
Regards,
Ayush
Kirill Reshke <reshkekirill@gmail.com> writes:
If a user creates a malformed operator in theirs database, like:
...
then psql's meta-commands queries like \du stop working.
Yup.
I think this can be enhanced a little by schema-qualifying psql's
internal query.
This has been discussed and rejected before. Fully schema-qualifying
those queries would make them unreadable and close to unmaintainable.
And before you say that readability doesn't matter here: you're wrong.
Lots of people look at these queries via "psql -E" to see how to query
the catalogs.
regards, tom lane