Error: error: password authentication failed for user "postgres"

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 when wrong ?

1 Like

In .env you have password as my_pass and actual database password is my_db_pass :wink:

oh, my bad, that’s a silly mistake have been done in here.

It is my_db_pass both in .env and postgres username. The both are same.
It’s a mistake while typing this question.

Nevermind, I’ll edit the question.

I can only figure out 2 reasons for it

  1. Auth data is incorrect (which shouldn’t be the case when it works with CLI)
  2. External access is denied. Perhaps postgres user is allowed to connect from localhost only, but your API is on other server? Perhaps it should be 0.0.0.0 not 127.0.0.1?

Something like in here: