Fresh setup of PostgreSQL database Docker container fails

[BOHEUS]

As I transitioned from old Postgres bitnami container to new Postgres spilo container as it's mentioned in docs, I stumped upon error when running npx nx database:reset twenty-server. It turns out that script responsible for resetting database and configuring it, has problem with creating new user.

Here's my workaround:

• docker exec -it twenty_pg bash to access directly container's console
• psql postgres -c "CREATE DATABASE default;" -c "CREATE DATABASE test;" -c "CREATE USER twenty PASSWORD 'twenty';" -c "ALTER ROLE twenty superuser;" taken from docs (if it doesn't work, run psql -U postgres and type each command separately)
  After that, when running npx nx database:reset twenty-server worked fine like before

Logs:

> npx nx database:reset twenty-server

   ✔  1/1 dependent project tasks succeeded [1 read from cache]

   Hint: you can run the command with --verbose to see the full dependent project outputs

——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————


> nx run twenty-server:build  [local cache]

> rimraf dist

> nest build --path ./tsconfig.build.json

-  TSC  Initializing type checker...
✔  TSC  Initializing type checker...
>  TSC  Found 0 issues.
>  SWC  Running...
Successfully compiled: 1653 files with swc (707.62ms)

> nx run twenty-server:"database:reset"

> nx ts-node-no-deps -- ./scripts/truncate-db.ts


> nx run twenty-server:ts-node-no-deps ./scripts/truncate-db.ts

> ts-node ./scripts/truncate-db.ts

Error during schema dropping: error: password authentication failed for user "twenty"
    at Parser.parseErrorMessage (/home/user/Desktop/projects/twenty/node_modules/pg/node_modules/pg-protocol/src/parser.ts:369:69)
    at Parser.handlePacket (/home/user/Desktop/projects/twenty/node_modules/pg/node_modules/pg-protocol/src/parser.ts:188:21)
    at Parser.parse (/home/user/Desktop/projects/twenty/node_modules/pg/node_modules/pg-protocol/src/parser.ts:103:30)
    at Socket.<anonymous> (/home/user/Desktop/projects/twenty/node_modules/pg/node_modules/pg-protocol/src/index.ts:7:48)
    at Socket.emit (node:events:514:28)
    at Socket.emit (node:domain:489:12)
    at addChunk (node:internal/streams/readable:324:12)
    at readableAddChunk (node:internal/streams/readable:297:9)
    at Socket.Readable.push (node:internal/streams/readable:234:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
  length: 102,
  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: '326',
  routine: 'auth_failed'
}

——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————

 NX   Successfully ran target ts-node-no-deps for project twenty-server (7s)

      With additional flags:
        ./scripts/truncate-db.ts

> nx ts-node-no-deps -- ./scripts/setup-db.ts


> nx run twenty-server:ts-node-no-deps ./scripts/setup-db.ts

> ts-node ./scripts/setup-db.ts

Error during Data Source initialization: error: password authentication failed for user "twenty"
    at Parser.parseErrorMessage (/home/user/Desktop/projects/twenty/node_modules/pg/node_modules/pg-protocol/src/parser.ts:369:69)
    at Parser.handlePacket (/home/user/Desktop/projects/twenty/node_modules/pg/node_modules/pg-protocol/src/parser.ts:188:21)
    at Parser.parse (/home/user/Desktop/projects/twenty/node_modules/pg/node_modules/pg-protocol/src/parser.ts:103:30)
    at Socket.<anonymous> (/home/user/Desktop/projects/twenty/node_modules/pg/node_modules/pg-protocol/src/index.ts:7:48)
    at Socket.emit (node:events:514:28)
    at Socket.emit (node:domain:489:12)
    at addChunk (node:internal/streams/readable:324:12)
    at readableAddChunk (node:internal/streams/readable:297:9)
    at Socket.Readable.push (node:internal/streams/readable:234:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
  length: 102,
  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: '326',
  routine: 'auth_failed'
}

——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————

 NX   Successfully ran target ts-node-no-deps for project twenty-server (6s)

      With additional flags:
        ./scripts/setup-db.ts

> nx database:migrate


   ✔  1/1 dependent project tasks succeeded [1 read from cache]

   Hint: you can run the command with --verbose to see the full dependent project outputs

——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————


> nx run twenty-server:build  [existing outputs match the cache, left as is]

> rimraf dist

> nest build --path ./tsconfig.build.json

-  TSC  Initializing type checker...
✔  TSC  Initializing type checker...
>  TSC  Found 0 issues.
>  SWC  Running...
Successfully compiled: 1653 files with swc (707.62ms)

> nx run twenty-server:"database:migrate"

> nx typeorm -- migration:run -d src/database/typeorm/metadata/metadata.datasource


> nx run twenty-server:typeorm migration:run -d src/database/typeorm/metadata/metadata.datasource

> ts-node ../../node_modules/typeorm/cli.js migration:run -d src/database/typeorm/metadata/metadata.datasource

Error during migration run:
error: password authentication failed for user "twenty"
    at Parser.parseErrorMessage (/home/user/Desktop/projects/twenty/node_modules/pg/node_modules/pg-protocol/src/parser.ts:369:69)
    at Parser.handlePacket (/home/user/Desktop/projects/twenty/node_modules/pg/node_modules/pg-protocol/src/parser.ts:188:21)
    at Parser.parse (/home/user/Desktop/projects/twenty/node_modules/pg/node_modules/pg-protocol/src/parser.ts:103:30)
    at Socket.<anonymous> (/home/user/Desktop/projects/twenty/node_modules/pg/node_modules/pg-protocol/src/index.ts:7:48)
    at Socket.emit (node:events:514:28)
    at Socket.emit (node:domain:489:12)
    at addChunk (node:internal/streams/readable:324:12)
    at readableAddChunk (node:internal/streams/readable:297:9)
    at Socket.Readable.push (node:internal/streams/readable:234:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
  length: 102,
  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: '326',
  routine: 'auth_failed'
}
Warning: command "ts-node ../../node_modules/typeorm/cli.js migration:run -d src/database/typeorm/metadata/metadata.datasource" exited with non-zero status code
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————

 NX   Ran target typeorm for project twenty-server (829ms)

      With additional flags:
        migration:run
        --d=src/database/typeorm/metadata/metadata.datasource

   ✖  1/1 failed
   ✔  0/1 succeeded [0 read from cache]

Warning: command "nx typeorm -- migration:run -d src/database/typeorm/metadata/metadata.datasource" exited with non-zero status code
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————

 NX   Ran target database:migrate for project twenty-server and 2 task(s) they depend on (1s)

   ✖  1/3 failed
   ✔  2/3 succeeded [2 read from cache]

Warning: command "nx database:migrate" exited with non-zero status code
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————

 NX   Ran target database:reset for project twenty-server and 2 task(s) they depend on (14s)

   ✖  1/3 failed
   ✔  2/3 succeeded [2 read from cache]

➜  twenty git:(6638_authorization_token_env) ✗ npx nx database:reset twenty-server

   ✔  1/1 dependent project tasks succeeded [1 read from cache]

   Hint: you can run the command with --verbose to see the full dependent project outputs

——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————


> nx run twenty-server:build  [existing outputs match the cache, left as is]

> rimraf dist

> nest build --path ./tsconfig.build.json

-  TSC  Initializing type checker...
✔  TSC  Initializing type checker...
>  TSC  Found 0 issues.
>  SWC  Running...
Successfully compiled: 1653 files with swc (707.62ms)

> nx run twenty-server:"database:reset"

> nx ts-node-no-deps -- ./scripts/truncate-db.ts


> nx run twenty-server:ts-node-no-deps ./scripts/truncate-db.ts

> ts-node ./scripts/truncate-db.ts

Error during schema dropping: error: password authentication failed for user "twenty"
    at Parser.parseErrorMessage (/home/user/Desktop/projects/twenty/node_modules/pg/node_modules/pg-protocol/src/parser.ts:369:69)
    at Parser.handlePacket (/home/user/Desktop/projects/twenty/node_modules/pg/node_modules/pg-protocol/src/parser.ts:188:21)
    at Parser.parse (/home/user/Desktop/projects/twenty/node_modules/pg/node_modules/pg-protocol/src/parser.ts:103:30)
    at Socket.<anonymous> (/home/user/Desktop/projects/twenty/node_modules/pg/node_modules/pg-protocol/src/index.ts:7:48)
    at Socket.emit (node:events:514:28)
    at Socket.emit (node:domain:489:12)
    at addChunk (node:internal/streams/readable:324:12)
    at readableAddChunk (node:internal/streams/readable:297:9)
    at Socket.Readable.push (node:internal/streams/readable:234:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
  length: 102,
  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: '326',
  routine: 'auth_failed'
}

——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————

 NX   Successfully ran target ts-node-no-deps for project twenty-server (6s)

      With additional flags:
        ./scripts/truncate-db.ts

> nx ts-node-no-deps -- ./scripts/setup-db.ts


> nx run twenty-server:ts-node-no-deps ./scripts/setup-db.ts

> ts-node ./scripts/setup-db.ts

Error during Data Source initialization: error: password authentication failed for user "twenty"
    at Parser.parseErrorMessage (/home/user/Desktop/projects/twenty/node_modules/pg/node_modules/pg-protocol/src/parser.ts:369:69)
    at Parser.handlePacket (/home/user/Desktop/projects/twenty/node_modules/pg/node_modules/pg-protocol/src/parser.ts:188:21)
    at Parser.parse (/home/user/Desktop/projects/twenty/node_modules/pg/node_modules/pg-protocol/src/parser.ts:103:30)
    at Socket.<anonymous> (/home/user/Desktop/projects/twenty/node_modules/pg/node_modules/pg-protocol/src/index.ts:7:48)
    at Socket.emit (node:events:514:28)
    at Socket.emit (node:domain:489:12)
    at addChunk (node:internal/streams/readable:324:12)
    at readableAddChunk (node:internal/streams/readable:297:9)
    at Socket.Readable.push (node:internal/streams/readable:234:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
  length: 102,
  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: '326',
  routine: 'auth_failed'
}

——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————

 NX   Successfully ran target ts-node-no-deps for project twenty-server (6s)

      With additional flags:
        ./scripts/setup-db.ts

> nx database:migrate


   ✔  1/1 dependent project tasks succeeded [1 read from cache]

   Hint: you can run the command with --verbose to see the full dependent project outputs

——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————


> nx run twenty-server:build  [existing outputs match the cache, left as is]

> rimraf dist

> nest build --path ./tsconfig.build.json

-  TSC  Initializing type checker...
✔  TSC  Initializing type checker...
>  TSC  Found 0 issues.
>  SWC  Running...
Successfully compiled: 1653 files with swc (707.62ms)

> nx run twenty-server:"database:migrate"

> nx typeorm -- migration:run -d src/database/typeorm/metadata/metadata.datasource


> nx run twenty-server:typeorm migration:run -d src/database/typeorm/metadata/metadata.datasource

> ts-node ../../node_modules/typeorm/cli.js migration:run -d src/database/typeorm/metadata/metadata.datasource

Error during migration run:
error: password authentication failed for user "twenty"
    at Parser.parseErrorMessage (/home/user/Desktop/projects/twenty/node_modules/pg/node_modules/pg-protocol/src/parser.ts:369:69)
    at Parser.handlePacket (/home/user/Desktop/projects/twenty/node_modules/pg/node_modules/pg-protocol/src/parser.ts:188:21)
    at Parser.parse (/home/user/Desktop/projects/twenty/node_modules/pg/node_modules/pg-protocol/src/parser.ts:103:30)
    at Socket.<anonymous> (/home/user/Desktop/projects/twenty/node_modules/pg/node_modules/pg-protocol/src/index.ts:7:48)
    at Socket.emit (node:events:514:28)
    at Socket.emit (node:domain:489:12)
    at addChunk (node:internal/streams/readable:324:12)
    at readableAddChunk (node:internal/streams/readable:297:9)
    at Socket.Readable.push (node:internal/streams/readable:234:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
  length: 102,
  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: '326',
  routine: 'auth_failed'
}
Warning: command "ts-node ../../node_modules/typeorm/cli.js migration:run -d src/database/typeorm/metadata/metadata.datasource" exited with non-zero status code
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————

 NX   Ran target typeorm for project twenty-server (814ms)

      With additional flags:
        migration:run
        --d=src/database/typeorm/metadata/metadata.datasource

   ✖  1/1 failed
   ✔  0/1 succeeded [0 read from cache]

Warning: command "nx typeorm -- migration:run -d src/database/typeorm/metadata/metadata.datasource" exited with non-zero status code
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————

 NX   Ran target database:migrate for project twenty-server and 2 task(s) they depend on (1s)

   ✖  1/3 failed
   ✔  2/3 succeeded [2 read from cache]

Warning: command "nx database:migrate" exited with non-zero status code
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————

 NX   Ran target database:reset for project twenty-server and 2 task(s) they depend on (13s)

   ✖  1/3 failed
   ✔  2/3 succeeded [2 read from cache]

[Bonapara]

@BOHEUS did you experienced it again since then? Thanks for reporting it!

[BOHEUS]

@Bonapara no as I set it up and I haven't touched it since ("if it's working, don't touch it" 😆)

[charlesBochet]

@prastoin could you take a look at this one?

[prastoin]

Hello there, I remember encountering the very much pattern when initializing my local setup a while ago.
Thing is by default psql user twenty does not exist, the default superuser created by psql will follow current /home or window's profile name. e.g prastoin.
I think we should add to the docs a line regarding postgres superuser such as described in @BOHEUS workaround

@charlesBochet what do you think ?

Note

Current twenty-server .env default PG_DATABASE_URL:

PG_DATABASE_URL=postgres://postgres:postgres@localhost:5432/default

[charlesBochet]

I would add a check to the entrypoint.sh file to check if the user postgres exists and otherwise we should display an error and push the user to update the PG_USER

[charlesBochet]

Tagging this as good first issue