public inbox for [email protected]
help / color / mirror / Atom feedBUG #19523: psql tab-completion shadows pg_db_role_setting
4+ messages / 3 participants
[nested] [flat]
* BUG #19523: psql tab-completion shadows pg_db_role_setting
@ 2026-06-18 07:06 PG Bug reporting form <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: PG Bug reporting form @ 2026-06-18 07:06 UTC (permalink / raw)
To: [email protected]; +Cc: [email protected]
The following bug has been logged on the website:
Bug reference: 19523
Logged by: Zhou Digoal
Email address: [email protected]
PostgreSQL version: 19beta1
Operating system: macOS
Description:
# 🐛 [BUG] psql tab-completion shadows pg_db_role_setting
## Environment
| Item | Value |
| --- | --- |
| OS | macOS 15.7.7 (build 24G720) |
| Kernel | Darwin 24.6.0 |
| Compiler | Apple clang version 17.0.0 (clang-1700.6.4.2) |
| PG version | 19beta1 (`pg_config --version`) |
| Source branch | `master` |
| Source commit | `850b9218c8e4aa7a56f4ec34a542d4a37f9e07eb` |
| `git describe` | `850b921` |
| Build flags | `--enable-debug --enable-cassert --enable-debug-symbols` |
| Configure | `$PGBIN/pg_config --configure` |
---
## Summary
The query that psql tab-completion runs to list database-scoped GUC names
(Query_for_list_of_database_vars in src/bin/psql/tab-complete.c, duplicated
in src/bin/psql/tab-complete.in.c) references the system catalog table
pg_db_role_setting without the pg_catalog. schema qualifier. A user with
CREATE privilege on any schema earlier in search_path than pg_catalog can
therefore shadow the catalog table and influence tab-completion suggestions
for ALTER DATABASE SET.
---
## Commit under test
`850b9218c8e4aa7a56f4ec34a542d4a37f9e07eb` on branch `master`. The full
source HEAD at report
time is `850b9218c8e4aa7a56f4ec34a542d4a37f9e07eb` (`git describe` →
`850b921`).
---
## Reproduction
Repro file: `/tmp/repro_pg_db_role_setting.sql`
```sql
CREATE SCHEMA attacker;
CREATE TABLE attacker.pg_db_role_setting (
setdatabase oid,
setrole oid,
setconfig text[]
);
INSERT INTO attacker.pg_db_role_setting
SELECT oid, 0, ARRAY['custom_var=value', 'session_replication_role=replica']
FROM pg_database WHERE datname = 'postgres';
SET search_path = attacker, pg_catalog;
SELECT conf FROM (
SELECT setdatabase,
pg_catalog.split_part(pg_catalog.unnest(setconfig),'=',1) conf
FROM pg_db_role_setting
) s, pg_database d
WHERE s.setdatabase = d.oid
AND conf LIKE 'c%'
AND d.datname LIKE 'p%';
RESET search_path;
DROP SCHEMA attacker CASCADE;
```
### Actual output
```
Returned one row 'custom_var' that was read from attacker.pg_db_role_setting
rather than from the system catalog (which is empty for this database).
```
### Server log (last lines)
```
CREATE SCHEMA attacker;
CREATE SCHEMA
CREATE TABLE attacker.pg_db_role_setting (
setdatabase oid,
setrole oid,
setconfig text[]
);
CREATE TABLE
INSERT INTO attacker.pg_db_role_setting
SELECT oid, 0, ARRAY['custom_var=value', 'session_replication_role=replica']
FROM pg_database WHERE datname = 'postgres';
INSERT 0 1
SET search_path = attacker, pg_catalog;
SET
SELECT conf FROM (
SELECT setdatabase,
pg_catalog.split_part(pg_catalog.unnest(setconfig),'=',1) conf
FROM pg_db_role_setting
) s, pg_database d
WHERE s.setdatabase = d.oid
AND conf LIKE 'c%'
AND d.datname LIKE 'p%';
conf
------------
custom_var
(1 row)
RESET search_path;
RESET
DROP SCHEMA attacker CASCADE;
psql:/tmp/repro_pg_db_role_setting.sql:48: NOTICE: drop cascades to table
attacker.pg_db_role_setting
DROP SCHEMA
```
### Expected output
```
Zero rows; the query should resolve pg_db_role_setting to the system catalog
table regardless of search_path.
```
---
## Why is this a bug?
Commit bf5206f (psql: Add some missing schema qualifications in describe.c)
fixed the same class of bug for describe.c but missed tab-complete.c and
tab-complete.in.c. A user who can create a table named pg_db_role_setting in
a schema that appears before pg_catalog in search_path can have psql issue
its tab-completion query against the shadow table instead of the real
catalog, returning attacker- controlled GUC names. This violates the
documented psql behavior of suggesting actual database-level GUC settings.
---
## Suggested fix
Qualify the reference in src/bin/psql/tab-complete.in.c (line 1043) and the
generated src/bin/psql/tab-complete.c (line 1064) by changing FROM
pg_db_role_setting to FROM pg_catalog.pg_db_role_setting, then regenerate
tab-complete.c on rebuild.
---
## Severity
**medium** — see Environment block.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #19523: psql tab-completion shadows pg_db_role_setting
@ 2026-07-09 10:13 Vismay Tiwari <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Vismay Tiwari @ 2026-07-09 10:13 UTC (permalink / raw)
To: [email protected]
Hi,
Reproduced on current master. The tab-completion query for
"ALTER DATABASE ... RESET" (Query_for_list_of_database_vars) references
pg_db_role_setting and pg_database without a pg_catalog qualification, so a
same-named table earlier in search_path shadows the catalog:
CREATE SCHEMA attacker;
CREATE TABLE attacker.pg_db_role_setting (setdatabase oid, setrole
oid, setconfig text[]);
INSERT INTO attacker.pg_db_role_setting
SELECT oid, 0, ARRAY['evil_var=x'] FROM pg_database WHERE
datname = 'postgres';
SET search_path = attacker, pg_catalog;
-- "ALTER DATABASE postgres RESET <TAB>" then offers evil_var
The attached patch qualifies both catalogs with pg_catalog, matching the
qualification already used for unnest()/split_part() in the same query and for
the catalogs in the analogous subscription-variable completion query.
No test is included -- tab-completion query internals aren't covered by the
TAP suite, and this follows the earlier qualification fixes in the same area.
Affects v18 and master (the query was added in v18).
Regards,
Vismay Tiwari
Attachments:
[application/octet-stream] v1-0001-psql-schema-qualify-catalog-references-in-a-tab-c.patch (1.6K, ../../CALHMmB84qkCgv3QAR78YawgfqQZCxSPkRhppxzU=e6fg8RA+AA@mail.gmail.com/2-v1-0001-psql-schema-qualify-catalog-references-in-a-tab-c.patch)
download | inline diff:
From 4507723720f7105c5a58ab9fda776405f703b813 Mon Sep 17 00:00:00 2001
From: vismaytiwari <[email protected]>
Date: Thu, 9 Jul 2026 15:35:51 +0530
Subject: [PATCH v1] psql: schema-qualify catalog references in a
tab-completion query
The tab-completion query behind Query_for_list_of_database_vars referenced
pg_db_role_setting and pg_database without schema qualification. A table of
the same name earlier in the user's search_path therefore shadows the catalog
and feeds arbitrary values into the completion suggestions for
"ALTER DATABASE ... RESET". Qualify both with pg_catalog, matching the
qualification already applied to unnest()/split_part() in this same query and
to the catalogs in the analogous subscription-variable completion query.
Reported-by: Zhou Digoal
Bug: #19523
Discussion: https://postgr.es/m/[email protected]
---
src/bin/psql/tab-complete.in.c | 4 ++--
1 file changed, 2 insertions(+), 2 deletions(-)
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 49ea584..6a88470 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -1040,8 +1040,8 @@ static const SchemaQuery Query_for_trigger_of_table = {
#define Query_for_list_of_database_vars \
"SELECT conf FROM ("\
" SELECT setdatabase, pg_catalog.split_part(pg_catalog.unnest(setconfig),'=',1) conf"\
-" FROM pg_db_role_setting "\
-" ) s, pg_database d "\
+" FROM pg_catalog.pg_db_role_setting "\
+" ) s, pg_catalog.pg_database d "\
" WHERE s.setdatabase = d.oid "\
" AND conf LIKE '%s'"\
" AND d.datname LIKE '%s'"
--
2.50.1 (Apple Git-155)
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #19523: psql tab-completion shadows pg_db_role_setting
@ 2026-07-10 04:49 Kirill Reshke <[email protected]>
parent: Vismay Tiwari <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Kirill Reshke @ 2026-07-10 04:49 UTC (permalink / raw)
To: Vismay Tiwari <[email protected]>; +Cc: PostgreSQL mailing lists <[email protected]>
Hi! You seem to create two threads on the issue, so I don't quite
understand where to respond. anyway:
On Thu, 9 Jul 2026, 15:14 Vismay Tiwari, <[email protected]> wrote:
> Hi,
>
> Reproduced on current master. The tab-completion query for
> "ALTER DATABASE ... RESET" (Query_for_list_of_database_vars) references
> pg_db_role_setting and pg_database without a pg_catalog qualification, so a
> same-named table earlier in search_path shadows the catalog:
>
> CREATE SCHEMA attacker;
> CREATE TABLE attacker.pg_db_role_setting (setdatabase oid, setrole
> oid, setconfig text[]);
> INSERT INTO attacker.pg_db_role_setting
> SELECT oid, 0, ARRAY['evil_var=x'] FROM pg_database WHERE
> datname = 'postgres';
> SET search_path = attacker, pg_catalog;
> -- "ALTER DATABASE postgres RESET <TAB>" then offers evil_var
>
>
Preventing this makes sense in case somebody accidentally misconfigured
their server. Which is not very probable for a table with `
pg_db_role_setting` name.
Also note that this is not a vulnerability: from
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.
Anyway +1 on fixing
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #19523: psql tab-completion shadows pg_db_role_setting
@ 2026-07-10 05:51 Vismay Tiwari <[email protected]>
parent: Kirill Reshke <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: Vismay Tiwari @ 2026-07-10 05:51 UTC (permalink / raw)
To: Kirill Reshke <[email protected]>; +Cc: PostgreSQL mailing lists <[email protected]>
Hi Kirill,
Thanks, and sorry for the two threads — the second was an accidental
resend that didn't thread properly on my end , so let's keep it here.
You're right that it's not a vulnerability, and the "attacker" framing
was overblown — the docs are clear that removing publicly-writable
schemas from search_path is the real safeguard. The intent is just
consistency and robustness: the same query already qualifies
unnest()/split_part() with pg_catalog, and the analogous
subscription-variable completion query qualifies its catalogs, so this
only brings pg_db_role_setting/pg_database in line. As a side benefit
it avoids a surprising tab-completion result for anyone who happens to
have a same-named table earlier in search_path, misconfigured or not.
Thanks for the +1.
Regards,
Vismay
On Fri, Jul 10, 2026 at 10:19 AM Kirill Reshke <[email protected]> wrote:
>
>
> Hi! You seem to create two threads on the issue, so I don't quite understand where to respond. anyway:
>
> On Thu, 9 Jul 2026, 15:14 Vismay Tiwari, <[email protected]> wrote:
>>
>> Hi,
>>
>> Reproduced on current master. The tab-completion query for
>> "ALTER DATABASE ... RESET" (Query_for_list_of_database_vars) references
>> pg_db_role_setting and pg_database without a pg_catalog qualification, so a
>> same-named table earlier in search_path shadows the catalog:
>>
>> CREATE SCHEMA attacker;
>> CREATE TABLE attacker.pg_db_role_setting (setdatabase oid, setrole
>> oid, setconfig text[]);
>> INSERT INTO attacker.pg_db_role_setting
>> SELECT oid, 0, ARRAY['evil_var=x'] FROM pg_database WHERE
>> datname = 'postgres';
>> SET search_path = attacker, pg_catalog;
>> -- "ALTER DATABASE postgres RESET <TAB>" then offers evil_var
>>
>
> Preventing this makes sense in case somebody accidentally misconfigured their server. Which is not very probable for a table with `pg_db_role_setting` name.
> Also note that this is not a vulnerability: from 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.
>
> Anyway +1 on fixing
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2026-07-10 05:51 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-06-18 07:06 BUG #19523: psql tab-completion shadows pg_db_role_setting PG Bug reporting form <[email protected]>
2026-07-09 10:13 Re: BUG #19523: psql tab-completion shadows pg_db_role_setting Vismay Tiwari <[email protected]>
2026-07-10 04:49 ` Kirill Reshke <[email protected]>
2026-07-10 05:51 ` Vismay Tiwari <[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