db_query: the DATABASE_URL password is silently ignored, so any connection fails with `SASL: client password must be a string` unless ~/.pgpass happens to have a matching entry #281

New issue

Closed

opened 2026-09-20 22:19:44 +00:00 by david · 0 comments

david commented

2026-09-20 22:19:44 +00:00

Owner

Summary

The postgres extension never uses the password in DATABASE_URL. parseConnUrl() deliberately returns only { host, port, database, user }, and the pool is built with a passwordProvider option that pg does not support, so it is silently ignored. The only remaining source of a password is pg's built-in ~/.pgpass lookup — which means a perfectly valid DATABASE_URL fails to connect whenever ~/.pgpass has no entry for that host/user.

The resulting error names SCRAM, not the missing credential, so the failure looks like a bad password rather than an unread password:

SASL: SCRAM-SERVER-FIRST-MESSAGE: client password must be a string

Impact

Any DATABASE_URL whose host/user/database triple is absent from ~/.pgpass cannot be used at all. Switching from a remote dev DB to a local one is enough to break the tool with no configuration change on the extension side.
The URL's password is ignored even when explicitly present, so the fix is not "the password is wrong" — it is never read.
pg's pgpass support is deprecated and removed in pg@9 (node_modules/pg/lib/client.js:23-27), so on a pg major upgrade the extension loses password resolution entirely, including the .pgpass path that currently works by accident.
Affects all three tools: db_query, db_list_tables, db_table_schema. The extension loads, logs a success line, and only fails at first query, which makes it look like a server-side problem.

Environment

repo pi-extensions-and-skills @ 1a73f36 (2026-09-18), branch main
pg 8.23.0, pgpass 1.0.6 (package.json declares pg: ^8.13.0, pgpass: ^1.0.6)
config comes from the .env of process.cwd() (the project pi is launched in)
observed against PostgreSQL 18.6, pgvector 0.8.6, DATABASE_URL=postgresql://postgres:***@localhost:5432/shoppy_dev

Steps to reproduce

In any project with a .env containing a complete URL, e.g.:
DATABASE_URL=postgresql://postgres:secret@localhost:5432/shoppy_dev
Ensure ~/.pgpass has no line matching localhost:5432:shoppy_dev:postgres. (Confirmed by an existing ~/.pgpass that only lists a different host, e.g. 10.1.1.243:5432:shoppy_dev:shoppy_dev:….)
Start pi in that project and call db_query with SELECT 1.
Observe SASL: SCRAM-SERVER-FIRST-MESSAGE: client password must be a string.

Adding localhost:5432:shoppy_dev:postgres:secret to ~/.pgpass makes the identical call succeed — which isolates the cause to password resolution, not to the URL, the server, or the credentials.

Expected

The password from DATABASE_URL is used for the connection. ~/.pgpass remains a fallback for password-less URLs and for deployments that deliberately keep the secret out of .env.

Actual

The URL password is discarded before the pool is constructed, passwordProvider is ignored, pg finds no .pgpass entry, and the password stays undefined.

Root cause

The password is dropped during URL parsing. extensions/postgres/env.ts:
```
export interface ConnInfo {
  host: string;
  port: number;
  database: string;
  user: string;
}
```
parseConnUrl() maps URL → exactly those four fields (parsed.hostname, parsed.port, parsed.pathname, parsed.username). There is no password field anywhere in the file (grep -n password env.ts → no matches).
The intended fallback is not a pg option. extensions/postgres/index.ts:131-143 builds the pool with:
```
const pool = new pg.Pool({
  ...connInfo,
  passwordProvider: async () => { /* pgpass(connInfo, …) */ },
})
```
passwordProvider does not exist in pg 8.23.0 — nothing in node_modules/pg/lib/ or pg-pool/ references it — so it is dropped on the floor and the closure never runs.
pg then supplies its own pgpass lookup, and gives up silently on a miss. node_modules/pg/lib/client.js:296-310:
```
const pgPass = require('pgpass')
pgPass(this.connectionParameters, (pass) => {
  if (undefined !== pass) {
    pgPassDeprecationNotice()
    this.connectionParameters.password = this.password = pass
  }
  cb()
})
```
With no matching .pgpass line, pass is undefined, no password is set, and the SCRAM handshake throws client password must be a string. Note the file's own comment on the supported form:

You can provide an async function as the password property to the Client/Pool constructor that returns a password instead. Within this function you can call the pgpass module in your own code.

i.e. the supported option is password, not passwordProvider.

Proposed fix

The two changes together make the URL authoritative and keep .pgpass as a genuine fallback:

env.ts — carry the password through. Add password?: string to ConnInfo, return password: parsed.password || undefined from parseConnUrl() (normalising "" to undefined so password-less URLs still fall back), and include it in extractConnInfo().
index.ts — use the supported option. Either let connInfo.password flow into the pool and delete the provider entirely (relying on pg's own .pgpass fallback when the URL has no password), or rename the option to password as the deprecation notice instructs. If the explicit pgpass call is kept, a no-match must resolve to null/undefined rather than "" — an empty string is a valid string to SCRAM and produces a different, equally confusing password authentication failed error.

Worth deciding explicitly whether ConnInfo omitting the password is a deliberate security boundary. As it stands it is not one: the extension already reads the same secret out of .env via loadEnvFile(), so dropping it after parsing only breaks the connection without protecting anything. Fix 1 above assumes that is true; if the omission is intentional, then the pgpass fallback is the load-bearing path and must work for pg@9 — which its deprecation rules out.

Workaround (no code change)

Add a matching line to ~/.pgpass (mode 600):

localhost:5432:shoppy_dev:postgres:<password>

This works today but leaves the extension dependent on a deprecated pg feature.

Acceptance criteria

With a complete DATABASE_URL and no matching ~/.pgpass entry, all three tools connect and return results.
With a password-less DATABASE_URL and a matching ~/.pgpass entry, behaviour is unchanged.
A genuinely wrong password fails with a clear authentication error, not client password must be a string.
No behaviour depends on pg's deprecated built-in pgpass path.

Test plan

Unit: extensions/postgres/env.test.ts — parseConnUrl() carries the password; an empty password normalises to undefined; extractConnInfo() preserves it.
Unit: extensions/postgres/index.test.ts — the pool/client config passed to pg contains the resolved password (fake pg module), and a password-less URL still falls back rather than passing "".
Integration (manual, documented in the README): the two ~/.pgpass states above against a local database.

## Summary The postgres extension never uses the password in `DATABASE_URL`. `parseConnUrl()` deliberately returns only `{ host, port, database, user }`, and the pool is built with a `passwordProvider` option that `pg` does not support, so it is silently ignored. The only remaining source of a password is `pg`'s built-in `~/.pgpass` lookup — which means a perfectly valid `DATABASE_URL` fails to connect whenever `~/.pgpass` has no entry for that host/user. The resulting error names SCRAM, not the missing credential, so the failure looks like a bad password rather than an unread password: ``` SASL: SCRAM-SERVER-FIRST-MESSAGE: client password must be a string ``` ## Impact - Any `DATABASE_URL` whose host/user/database triple is absent from `~/.pgpass` cannot be used at all. Switching from a remote dev DB to a local one is enough to break the tool with no configuration change on the extension side. - The URL's password is ignored even when explicitly present, so the fix is not "the password is wrong" — it is never read. - `pg`'s `pgpass` support is **deprecated and removed in `pg@9`** (`node_modules/pg/lib/client.js:23-27`), so on a `pg` major upgrade the extension loses password resolution entirely, including the `.pgpass` path that currently works by accident. - Affects all three tools: `db_query`, `db_list_tables`, `db_table_schema`. The extension loads, logs a success line, and only fails at first query, which makes it look like a server-side problem. ## Environment - repo `pi-extensions-and-skills` @ `1a73f36` (2026-09-18), branch `main` - `pg` 8.23.0, `pgpass` 1.0.6 (`package.json` declares `pg: ^8.13.0`, `pgpass: ^1.0.6`) - config comes from the `.env` of `process.cwd()` (the project pi is launched in) - observed against PostgreSQL 18.6, pgvector 0.8.6, `DATABASE_URL=postgresql://postgres:***@localhost:5432/shoppy_dev` ## Steps to reproduce 1. In any project with a `.env` containing a complete URL, e.g.: `DATABASE_URL=postgresql://postgres:secret@localhost:5432/shoppy_dev` 2. Ensure `~/.pgpass` has **no** line matching `localhost:5432:shoppy_dev:postgres`. (Confirmed by an existing `~/.pgpass` that only lists a different host, e.g. `10.1.1.243:5432:shoppy_dev:shoppy_dev:…`.) 3. Start pi in that project and call `db_query` with `SELECT 1`. 4. Observe `SASL: SCRAM-SERVER-FIRST-MESSAGE: client password must be a string`. Adding `localhost:5432:shoppy_dev:postgres:secret` to `~/.pgpass` makes the identical call succeed — which isolates the cause to password resolution, not to the URL, the server, or the credentials. ## Expected The password from `DATABASE_URL` is used for the connection. `~/.pgpass` remains a fallback for password-less URLs and for deployments that deliberately keep the secret out of `.env`. ## Actual The URL password is discarded before the pool is constructed, `passwordProvider` is ignored, `pg` finds no `.pgpass` entry, and the password stays `undefined`. ## Root cause 1. **The password is dropped during URL parsing.** `extensions/postgres/env.ts`: ```ts export interface ConnInfo { host: string; port: number; database: string; user: string; } ``` `parseConnUrl()` maps `URL` → exactly those four fields (`parsed.hostname`, `parsed.port`, `parsed.pathname`, `parsed.username`). There is no `password` field anywhere in the file (`grep -n password env.ts` → no matches). 2. **The intended fallback is not a `pg` option.** `extensions/postgres/index.ts:131-143` builds the pool with: ```ts const pool = new pg.Pool({ ...connInfo, passwordProvider: async () => { /* pgpass(connInfo, …) */ }, }) ``` `passwordProvider` does not exist in `pg` 8.23.0 — nothing in `node_modules/pg/lib/` or `pg-pool/` references it — so it is dropped on the floor and the closure never runs. 3. **`pg` then supplies its own `pgpass` lookup, and gives up silently on a miss.** `node_modules/pg/lib/client.js:296-310`: ```js const pgPass = require('pgpass') pgPass(this.connectionParameters, (pass) => { if (undefined !== pass) { pgPassDeprecationNotice() this.connectionParameters.password = this.password = pass } cb() }) ``` With no matching `.pgpass` line, `pass` is `undefined`, no password is set, and the SCRAM handshake throws `client password must be a string`. Note the file's own comment on the supported form: > `You can provide an async function as the password property to the Client/Pool constructor that returns a password instead. Within this function you can call the pgpass module in your own code.` i.e. the supported option is **`password`**, not `passwordProvider`. ## Proposed fix The two changes together make the URL authoritative and keep `.pgpass` as a genuine fallback: 1. **`env.ts` — carry the password through.** Add `password?: string` to `ConnInfo`, return `password: parsed.password || undefined` from `parseConnUrl()` (normalising `""` to `undefined` so password-less URLs still fall back), and include it in `extractConnInfo()`. 2. **`index.ts` — use the supported option.** Either let `connInfo.password` flow into the pool and delete the provider entirely (relying on `pg`'s own `.pgpass` fallback when the URL has no password), or rename the option to `password` as the deprecation notice instructs. If the explicit `pgpass` call is kept, a no-match must resolve to `null`/`undefined` rather than `""` — an empty string is a valid string to SCRAM and produces a *different*, equally confusing `password authentication failed` error. Worth deciding explicitly whether `ConnInfo` omitting the password is a deliberate security boundary. As it stands it is not one: the extension already reads the same secret out of `.env` via `loadEnvFile()`, so dropping it after parsing only breaks the connection without protecting anything. Fix 1 above assumes that is true; if the omission is intentional, then the `pgpass` fallback is the load-bearing path and must work for `pg@9` — which its deprecation rules out. ## Workaround (no code change) Add a matching line to `~/.pgpass` (mode `600`): ``` localhost:5432:shoppy_dev:postgres:<password> ``` This works today but leaves the extension dependent on a deprecated `pg` feature. ## Acceptance criteria - With a complete `DATABASE_URL` and **no** matching `~/.pgpass` entry, all three tools connect and return results. - With a password-less `DATABASE_URL` and a matching `~/.pgpass` entry, behaviour is unchanged. - A genuinely wrong password fails with a clear authentication error, not `client password must be a string`. - No behaviour depends on `pg`'s deprecated built-in `pgpass` path. ## Test plan - Unit: `extensions/postgres/env.test.ts` — `parseConnUrl()` carries the password; an empty password normalises to `undefined`; `extractConnInfo()` preserves it. - Unit: `extensions/postgres/index.test.ts` — the pool/client config passed to `pg` contains the resolved password (fake `pg` module), and a password-less URL still falls back rather than passing `""`. - Integration (manual, documented in the README): the two `~/.pgpass` states above against a local database.

david added the

bug

label

2026-09-20 22:19:44 +00:00

david referenced this issue from a pull request that will close it,

2026-09-28 07:02:02 +00:00

issue-281: use the DATABASE_URL password for postgres connections #284

david closed this issue

2026-09-28 07:07:24 +00:00