Subject: BUG #19523: psql tab-completion shadows pg_db_role_setting

From: PG Bug reporting form <[email-masked]>
Date: Thu, 18 Jun 2026 07:06:44 +0000

The following bug has been logged on the website:

Bug reference:      19523
Logged by:          Zhou Digoal
Email address:      [email-masked]
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.

---

From: Vismay Tiwari <[email-masked]>
Date: Thu, 9 Jul 2026 17:53:07 +0530

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-masked]
---
Resending in-thread: my earlier copy of this patch reached the list without an
In-Reply-To header, so it landed unthreaded rather than under the report.
Same patch, no changes.

 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)