I have a password for a remote PostgreSQL server that I cannot change. This password contains hashtags (#). From searching, I know that this can be a problem.
I can connect to the server from the command line:
psql -h host -U username -d dbname -p 5432 (and then manually entering the ##pw##).
I can connect to it with pgAdmin, with VSCode, and so on.
I cannot manage to connect to the server with node-postgres, whichever of the following connection methods I attempt, I get two types of errors:
// Error 1
// error: no pg_hba.conf entry for host "IP", user "username", database "dbname", no encryption
// at C:\repos\perfo\node_modules\pg-pool\index.js:45:11
// at process.processTicksAndRejections (node:internal/process/task_queues:105:5)
// at async C:\repos\perfo\server.js:118:24 {
// length: 160,
// severity: 'FATAL',
// code: '28000',
// detail: undefined,
// hint: undefined,
// position: undefined,
// internalPosition: undefined,
// internalQuery: undefined,
// where: undefined,
// schema: undefined,
// table: undefined,
// column: undefined,
// dataType: undefined,
// constraint: undefined,
// file: 'auth.c',
// line: '646',
// routine: 'ClientAuthentication'
// }
// Error 2
//TypeError: Cannot read properties of undefined (reading 'searchParams')
// at parse (C:\repos\perfo\node_modules\pg-connection-string\index.js:39:30)
// at new ConnectionParameters (C:\repos\perfo\node_modules\pg\lib\connection-parameters.js:56:42)
// at new Client (C:\repos\perfo\node_modules\pg\lib\client.js:18:33)
// at BoundPool.newClient (C:\repos\perfo\node_modules\pg-pool\index.js:233:20)
// at BoundPool.connect (C:\repos\perfo\node_modules\pg-pool\index.js:227:10)
// at BoundPool.query (C:\repos\perfo\node_modules\pg-pool\index.js:411:10)
// at C:\repos\perfo\server.js:117:35
// at Layer.handleRequest (C:\repos\perfo\node_modules\router\lib\layer.js:152:17)
// at next (C:\repos\perfo\node_modules\router\lib\route.js:157:13)
// at Route.dispatch (C:\repos\perfo\node_modules\router\lib\route.js:117:3)
Here is what I tried:
// #1
const pool = new Pool({connectionString: 'postgresql://username:##pw##@host:5432/dbname'})
// #2 - replacing hashtag with '%23'
const pool = new Pool({connectionString: 'postgresql://username:%23%23pw%23%23@host:5432/dbname'})
I have tried setting the password in a variable and then adding it to the connectionString and also tried encoding it.
// #3
var pw = "##pw##"
const pool = new Pool({connectionString: `postgresql://username:${pw}@host:5432/dbname`})
// or
const pool = new Pool({connectionString: "postgresql://username:"+pw+"@host:5432/dbname"})
// #4
var pw = encodeURIComponent('##pw##')
const pool = new Pool({connectionString: `postgresql://username:${pw}@host:5432/dbname`})
// or
const pool = new Pool({connectionString: "postgresql://username:"+pw+"@host:5432/dbname"})
Tried encoding the whole connection string:
// #5
var connstring = encodeURIComponent("postgres://username:##pw##@host:5432/dbname")
const pool = new Pool({connectionString: connstring})
// here I get Error: getaddrinfo ENOTFOUND base
Also tried to set the config not in a connection string:
// #6
const pool = new Pool({
user: 'username',
password: '##pw##', // or '%23%23pw%23%23' or encodeURIComponent('##pw##'),
host: 'host',
port: '5432',
database: 'dbname'
})
// or
var config = {
user: 'username',
password: '##pw##', // or '%23%23pw%23%23' or encodeURIComponent('##pw##'),
host: 'host',
port: '5432',
database: 'dbname'
}
const pool = new Pool(config)
Any idea on what I'm doing wrong?
Thank you!
encodeURIComponentmethod should work.