Password authentication failed for PostgreSQL user with environment variables in file

[davidbo]

Describe the problem/error/question

n8n start
User settings loaded from: /home/app/.n8n/config
Initializing n8n process
Error: There was an error initializing DB
    at Start.exitWithCrash (/usr/lib/node_modules/n8n/src/commands/base-command.ts:152:23)
    at /usr/lib/node_modules/n8n/src/commands/base-command.ts:66:39
    at processTicksAndRejections (node:internal/process/task_queues:95:5)
    at Start.init (/usr/lib/node_modules/n8n/src/commands/base-command.ts:65:3)
    at Start.init (/usr/lib/node_modules/n8n/src/commands/start.ts:195:3)
    at Start._run (/usr/lib/node_modules/n8n/node_modules/@oclif/core/lib/command.js:301:13)
    at Config.runCommand (/usr/lib/node_modules/n8n/node_modules/@oclif/core/lib/config/config.js:424:25)
    at run (/usr/lib/node_modules/n8n/node_modules/@oclif/core/lib/main.js:94:16)
    at /usr/lib/node_modules/n8n/bin/n8n:65:2 {
  [cause]: error: password authentication failed for user "app"
      at Parser.parseErrorMessage (/usr/lib/node_modules/n8n/node_modules/pg-protocol/src/parser.ts:368:69)
      at Parser.handlePacket (/usr/lib/node_modules/n8n/node_modules/pg-protocol/src/parser.ts:187:21)
      at Parser.parse (/usr/lib/node_modules/n8n/node_modules/pg-protocol/src/parser.ts:102:30)
      at Socket.<anonymous> (/usr/lib/node_modules/n8n/node_modules/pg-protocol/src/index.ts:7:48)
      at Socket.emit (node:events:519:28)
      at Socket.emit (node:domain:488:12)
      at addChunk (node:internal/streams/readable:559:12)
      at readableAddChunkPushByteMode (node:internal/streams/readable:510:3)
      at Socket.Readable.push (node:internal/streams/readable:390:5)
      at TCP.onStreamRead (node:internal/stream_base_commons:191:23) {
    length: 99,
    severity: 'FATAL',
    code: '28P01',
    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: '335',
    routine: 'auth_failed'
  }
} undefined
error: password authentication failed for user "app"
    at Parser.parseErrorMessage (/usr/lib/node_modules/n8n/node_modules/pg-protocol/src/parser.ts:368:69)
    at Parser.handlePacket (/usr/lib/node_modules/n8n/node_modules/pg-protocol/src/parser.ts:187:21)
    at Parser.parse (/usr/lib/node_modules/n8n/node_modules/pg-protocol/src/parser.ts:102:30)
    at Socket.<anonymous> (/usr/lib/node_modules/n8n/node_modules/pg-protocol/src/index.ts:7:48)
    at Socket.emit (node:events:519:28)
    at Socket.emit (node:domain:488:12)
    at addChunk (node:internal/streams/readable:559:12)
    at readableAddChunkPushByteMode (node:internal/streams/readable:510:3)
    at Socket.Readable.push (node:internal/streams/readable:390:5)
    at TCP.onStreamRead (node:internal/stream_base_commons:191:23) {
  length: 99,
  severity: 'FATAL',
  code: '28P01',
  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: '335',
  routine: 'auth_failed'
} undefined

What is the error message (if any)?

error: password authentication failed for user "app"

I tried to use the _FILE to store the database pasword :

$ env  |grep PASSWORD
DB_POSTGRESDB_PASSWORD_FILE=/home/app/.n8n-pgsql-pass

I tried many syntax in this file :

• export DB_POSTGRESDB_PASSWORD="thepasswordiused"
• DB_POSTGRESDB_PASSWORD="thepasswordiused"
• DB_POSTGRESDB_PASSWORD_FILE="thepasswordiused"
• thepasswordiused

With the same password, I tried manually with psql :

psql -U app -W app
Password:
psql (14.13 (Ubuntu 14.13-0ubuntu0.22.04.1))
Type "help" for help.

app=> \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 app       |                                                            | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

app=> \dt
Did not find any relations.
app=>

Last thing, when I tried with the DB_POSTGRESDB_PASSWORD in the env, n8n starts fine. So the password is good but I probably have syntax issue in this file. I read the doocumentation here : Configuration methods | n8n Docs but it doesn’t give the syntax in the file itself.

Here’s where I’m stuck actually.

Information on your n8n setup

• n8n version: 1.60.1
• Database (default: SQLite): Postgresql
• n8n EXECUTIONS_PROCESS setting (default: own, main):
• Running n8n via (Docker, npm, n8n cloud, desktop app): npm
• Operating system: Ubuntu 22.04.5 LTS

Thanks for your support,
David

[Jon]

Hey @davidbo

This was reported on GitHub recently as well and it looks like it could be an issue with a new line character at the end of the file.

For now to get up and running are you able to put the password in a .env file and load it from that instead? As your noticed that bit is currently working and it will get you running until we get to the bottom of this.

[davidbo]

Hi @Jon ,

Thanks for your suggestion! I followed your advice and made sure the password is loaded into memory using environment variables through the following script:

sudo bash -c "cat > /etc/profile.d/postgresql_password.sh" <<- EOS
export DB_POSTGRESDB_PASSWORD="\$PGPASS"
EOS

This way, the password is handled via environment variables, and everything is working fine now.

Thanks again for the support!

Best,
David