External Postgresql (AWS RDS) does not connect properly

[DrPersico]

I'm trying to use Planka with an external PostgreSQL 16.1 server that is hosted in AWS RDS, however, it seems I've come across what appears to be the same issue mentioned in: External Postgresql with SSL does not connect properly #494

I have no issues connecting to PostgreSQL via PgAdmin4.

Here's my Docker-compose.yml

version: '3'

services:
  planka:
    image: planka:latest
    restart: on-failure
    volumes:
      - user-avatars:/app/public/user-avatars
      - project-background-images:/app/public/project-background-images
      - attachments:/app/private/attachments
    ports:
      - 3000:1337
    environment:
      - BASE_URL=http://localhost:3000
      - DATABASE_URL=postgresql://user:[email protected]:5321/planka?ssl=true&sslmode=required&sslrootcert=/app/db/global-bundle.pem
      - SECRET_KEY=xxxxxxxxxxxxxxxx
      # - TRUST_PROXY=0
      # - TOKEN_EXPIRES_IN=365 # In days

      # related: https://github.com/knex/knex/issues/2354
      # As knex does not pass query parameters from the connection string we
      # have to use environment variables in order to pass the desired values, e.g.
      # - PGSSLMODE=required

      # Configure knex to accept SSL certificates
      # - KNEX_REJECT_UNAUTHORIZED_SSL_CERTIFICATE=false

      # - [email protected] # Do not remove if you want to prevent this user from being edited/deleted
      # - DEFAULT_ADMIN_PASSWORD=demo
      # - DEFAULT_ADMIN_NAME=Demo Demo
      # - DEFAULT_ADMIN_USERNAME=demo

      # - OIDC_ISSUER=
      # - OIDC_CLIENT_ID=
      # - OIDC_CLIENT_SECRET=
      # - OIDC_SCOPES=openid email profile
      # - OIDC_ADMIN_ROLES=admin
      # - OIDC_EMAIL_ATTRIBUTE=email
      # - OIDC_NAME_ATTRIBUTE=name
      # - OIDC_USERNAME_ATTRIBUTE=preferred_username
      # - OIDC_ROLES_ATTRIBUTE=groups
      # - OIDC_IGNORE_USERNAME=true
      # - OIDC_IGNORE_ROLES=true
      # - OIDC_ENFORCED=true

      # Email Notifications (https://nodemailer.com/smtp/)
      # - SMTP_HOST=
      # - SMTP_PORT=587
      # - SMTP_SECURE=true
      # - SMTP_USER=
      # - SMTP_PASSWORD=
      # - SMTP_FROM="Demo Demo" <[email protected]>

      # - SLACK_BOT_TOKEN=
      # - SLACK_CHANNEL_ID=

volumes:
  user-avatars:
  project-background-images:
  attachments:
  db-data:

Error:

planka-master2-planka-1  | debug: It looks like your "sails.config.sockets.onlyAllowOrigins" array only includes
planka-master2-planka-1  | debug: references to the "localhost" origin.  This is completely valid, but be sure
planka-master2-planka-1  | debug: to add any other origins to this list that you'd like to accept socket
planka-master2-planka-1  | debug: connections from!
planka-master2-planka-1  | debug: 
planka-master2-planka-1  | 2024-04-10 09:35:11 [E] A hook ("orm") failed to load!
planka-master2-planka-1  | 2024-04-10 09:35:11 [E] Failed to lift app: "getConnection" failed ("failed").  Could not acquire a connection to the database using the specified manager.
planka-master2-planka-1  | Additional data:
planka-master2-planka-1  | 
planka-master2-planka-1  | {
planka-master2-planka-1  |   error: error: no pg_hba.conf entry for host "xxx.xxx.xxx.xxx", user "kanban", database "planka", no encryption
planka-master2-planka-1  |       at Parser.parseErrorMessage (/app/node_modules/.pnpm/[email protected]/node_modules/pg-protocol/dist/parser.js:287:98)
planka-master2-planka-1  |       at Parser.handlePacket (/app/node_modules/.pnpm/[email protected]/node_modules/pg-protocol/dist/parser.js:126:29)
planka-master2-planka-1  |       at Parser.parse (/app/node_modules/.pnpm/[email protected]/node_modules/pg-protocol/dist/parser.js:39:38)
planka-master2-planka-1  |       at Socket.<anonymous> (/app/node_modules/.pnpm/[email protected]/node_modules/pg-protocol/dist/index.js:11:42)
planka-master2-planka-1  |       at Socket.emit (node:events:517:28)
planka-master2-planka-1  |       at addChunk (node:internal/streams/readable:368:12)
planka-master2-planka-1  |       at readableAddChunk (node:internal/streams/readable:341:9)
planka-master2-planka-1  |       at Readable.push (node:internal/streams/readable:278:10)
planka-master2-planka-1  |       at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
planka-master2-planka-1  |     length: 163,
planka-master2-planka-1  |     severity: 'FATAL',
planka-master2-planka-1  |     code: '28000',
planka-master2-planka-1  |     detail: undefined,
planka-master2-planka-1  |     hint: undefined,
planka-master2-planka-1  |     position: undefined,
planka-master2-planka-1  |     internalPosition: undefined,
planka-master2-planka-1  |     internalQuery: undefined,
planka-master2-planka-1  |     where: undefined,
planka-master2-planka-1  |     schema: undefined,
planka-master2-planka-1  |     table: undefined,
planka-master2-planka-1  |     column: undefined,
planka-master2-planka-1  |     dataType: undefined,
planka-master2-planka-1  |     constraint: undefined,
planka-master2-planka-1  |     file: 'auth.c',
planka-master2-planka-1  |     line: '542',
planka-master2-planka-1  |     routine: 'ClientAuthentication'
planka-master2-planka-1  |   },
planka-master2-planka-1  |   meta: undefined
planka-master2-planka-1  | }
planka-master2-planka-1  | 
planka-master2-planka-1  | 2024-04-10 09:35:11 [E] More details (raw):

I've tried the following:

• Uncomment and set PGSSLMODE to allow, required, and no-verify.
• Uncomment and set KNEX_REJECT_UNAUTHORIZED_SSL_CERTIFICATE to false.
• Uncomment ssl: true in server/config/env/production.js.
• Tried on different machines

[meltyshev]

Hi! Thanks for reporting this and providing the logs. We can't test this right now because we need to register in AWS to reproduce this, but that requires providing payment information. If anyone has a way to reproduce this without registering in AWS, we'd be happy to test and find the problem.

[tomudding]

I have also seen this with a non-AWS PostgreSQL server that requires SSL. To fix it, we had to se PGSSLMODE to required and add ssl=true to the database URL. Having sslmode=require in the database URL does not work (and is also the reason PGSSLMODE was introduced in #404 from what I can tell).

If you do not want to set ssl=true in the database URL you will have to also set KNEX_REJECT_UNAUTHORIZED_SSL_CERTIFICATE to false such that the buildSSLConfig() will not return false (which would result in knex config having ssl: false). However, that is very bad in terms of security (you might as well disable SSL).