Postgresql Error: error: password authentication failed for user “postgres” in Adonis.js

Question

I have a Ubuntu 18.04 LTS vps in where I've installed and configured postgresql. I can log in to the postgresql via command line. But I can not be able to establish a connection into my project.

But right after deployment of an Adonis project, It shoes me error message of below error: error: password authentication failed for user "postgres"

Full Error is below:

error: error: password authentication failed for user "postgres"
0|server  |     at Connection.parseE (project_directory/node_modules/pg/lib/connection.js:614:13)
0|server  |     at Connection.parseMessage (project_directory/node_modules/pg/lib/connection.js:413:19)
0|server  |     at Socket.<anonymous> (project_directory/node_modules/pg/lib/connection.js:129:22)
0|server  |     at Socket.emit (events.js:315:20)
0|server  |     at addChunk (_stream_readable.js:295:12)
0|server  |     at readableAddChunk (_stream_readable.js:271:9)
0|server  |     at Socket.Readable.push (_stream_readable.js:212:10)
0|server  |     at TCP.onStreamRead (internal/stream_base_commons.js:186:23)
0|server  | From previous event:
0|server  |     at Client_PG.acquireRawConnection (project_directory/node_modules/knex/lib/dialects/postgres/index.js:147:12)
0|server  |     at create (project_directory/node_modules/knex/lib/client.js:280:23)
0|server  |     at project_directory/node_modules/tarn/lib/Pool.js:315:34
0|server  |     at Object.tryPromise (project_directory/node_modules/tarn/lib/utils.js:53:24)
0|server  |     at project_directory/node_modules/tarn/lib/Pool.js:315:17
0|server  |     at new Promise (<anonymous>)
0|server  |     at callbackOrPromise (project_directory/node_modules/tarn/lib/Pool.js:306:12)
0|server  |     at Pool._create (project_directory/node_modules/tarn/lib/Pool.js:236:9)
0|server  |     at Pool._doCreate (project_directory/node_modules/tarn/lib/Pool.js:208:36)
0|server  |     at Pool._tryAcquireOrCreate (project_directory/node_modules/tarn/lib/Pool.js:159:18)
0|server  |     at Pool.acquire (project_directory/node_modules/tarn/lib/Pool.js:85:14)
0|server  |     at project_directory/node_modules/knex/lib/client.js:335:26
0|server  | From previous event:
0|server  |     at Client_PG.acquireConnection (project_directory/node_modules/knex/lib/client.js:334:34)
0|server  |     at Runner.ensureConnection (project_directory/node_modules/knex/lib/runner.js:228:24)
0|server  |     at Runner.run (project_directory/node_modules/knex/lib/runner.js:34:42)
0|server  |     at Builder.Target.then (project_directory/node_modules/knex/lib/interface.js:20:43) {
0|server  |   length: 104,
0|server  |   severity: 'FATAL',
0|server  |   code: '28P01',
0|server  |   detail: undefined,
0|server  |   hint: undefined,
0|server  |   position: undefined,
0|server  |   internalPosition: undefined,
0|server  |   internalQuery: undefined,
0|server  |   where: undefined,
0|server  |   schema: undefined,
0|server  |   table: undefined,
0|server  |   column: undefined,
0|server  |   dataType: undefined,
0|server  |   constraint: undefined,
0|server  |   file: 'auth.c',
0|server  |   line: '333',
0|server  |   routine: 'auth_failed'
0|server  | }

Though I did some research and as per these instructions I updated the pg_hba.conf file. Here it is below:

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            peer
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5

Here is my project environemnt(.env) file

HOST=127.0.0.1
PORT=8989
NODE_ENV=development

APP_NAME=
APP_URL=http://${HOST}:${PORT}
CACHE_VIEWS=false
APP_KEY=my_app_key

DB_CONNECTION=pg
DB_HOST=127.0.0.1
DB_PORT=5432
DB_USERNAME=postgres
DB_PASSWORD=my_db_pass
DB_DATABASE=my_db_name

... other credential goes here ....

Note: my_db_pass is my postgres username's password. I can successfully get logged in using this password in command-line.

Can anyone help me figuring out about what went wrong ?

Answer 1

Basically, change your user or username in .env.

username -> db_uername
user -> db_user

Answer 2

I ran into the same issue. In my case, I added special characters in my password with escaping them.

https://docs.adonisjs.com/guides/environment-variables#escape-the--sign

Answer 3

I just changed this

let client = new Client({
  user: process.env.USER,
  host: process.env.HOST,
  database: process.env.DATABASE,
  password: process.env.PASSWORD,
  port: process.env.DB_PORT,
});

to this

let client = new Client({
  user: "postgres",
  host: process.env.HOST,
  database: process.env.DATABASE,
  password: "aaaaa",
  port: process.env.DB_PORT,
});

In server the process.env.USER is not my given username. IDK why but the value was changed.

Answer 4

go in config/database.js
find PostgreSQL configuration and replace password value like this

password: "direct set your password",

and try I have same issue in Cpanel hosting and I try this method it's work