Strapi giving me DB errors in production, even though I'm using correct credentials

Question

EDIT: I found a file at /config/database.js which is used to connect to sqlite in development. When I change the client name from sqlite to postgres, that's when the trouble starts.

Isn't strapi supposed to ignore files like this in production? How can I get strapi to ignore this file, and just use my postgres db?

module.exports = ({ env }) => ({
  defaultConnection: 'default',
  connections: {
    default: {
      connector: 'bookshelf',
      settings: {
        client: 'sqlite',
        filename: env('DATABASE_FILENAME', '.tmp/data.db'),
      },
      options: {
        useNullAsDefault: true,
      },
    },
  },
});

End edit.

I'm trying to get my strapi app to start up in production, but it keeps erroring out saying

[2020-07-22T01:15:40.246Z] debug ⛔️ Server wasn't able to start properly.
[2020-07-22T01:15:40.247Z] error error: password authentication failed for user "<redacted>"

The rest of the output is related to pg, which leads me to think that this is a DB connection error. I can log into my db from the command line using psql -U postgres -W, which confirms that I know my password.

In addition, I'm using pm2 to run things, and instead of using process.env in that file, I just added the db variables directly, but that made no difference.

The application has been built in production mode. I have 3 dbs in pg, one called postgres, one with my apps name, and another called strapi.

Thanks

my /config/enviroronments/production.database.json looks like this

{
  "defaultConnection": "default",
  "connections": {
    "default": {
      "connector": "bookshelf",
      "settings": {
        "client": "postgres",
        "host": "${process.env.DATABASE_HOST || '127.0.0.1'}",
        "port": "${process.env.DATABASE_PORT || 27017}",
        "database": "${process.env.DATABASE_NAME || 'strapi'}",
        "username": "${process.env.DATABASE_USERNAME || ''}",
        "password": "${process.env.DATABASE_PASSWORD || ''}"
      },
      "options": {
        "ssl": false
      }
    }
  }
}

and I have a .env file at the root of the backend app that looks like this

DATABASE_HOST=localhost
DATABASE_PORT=5432
DATABASE_NAME="<redacted - all letters>"
DATABASE_USERNAME="<redacted - all letters>"
DATABASE_PASSWORD="<redacted - all letters>"

Answer 1

Found the issue. When I created the app, I used sqlite as my db. As a result, the default database.js file wasn't set up in a way that could be overwritten with env variables.

I created a new local Strapi app with pgsql as my db, and copied the contents of the database.js file to my server. All working now.

New file for reference

module.exports = ({ env }) => ({
  defaultConnection: 'default',
  connections: {
    default: {
      connector: 'bookshelf',
      settings: {
        client: 'postgres',
        host: env('DATABASE_HOST', '127.0.0.1'),
        port: env.int('DATABASE_PORT', 5432),
        database: env('DATABASE_NAME', 'my-strapi-project'),
        username: env('DATABASE_USERNAME', 'testing'),
        password: env('DATABASE_PASSWORD', 'testing'),
        ssl: env.bool('DATABASE_SSL', false),
      },
      options: {}
    },
  },
});

Answer 2

I had the same situation in development. I created a strapi app with SQLite and decided to use PostgreSQL. That's where the trouble came in. So the fix was as follows:

app_name/config/database.js

module.exports = ({ env }) => ({
  connection: {
    client: 'postgres',
    connection: {
      host: env('DATABASE_HOST', '127.0.0.1'),
      port: env.int('DATABASE_PORT', 5432),
      database: env('DATABASE_NAME', 'db_name'),
      user: env('DATABASE_USERNAME', 'postgres'),
      password: env('DATABASE_PASSWORD', 'postgres'),
      ssl: env.bool('DATABASE_SSL', false),
    },
  },
});

Your dependencies under app_name/package.json should be like

"dependencies": {
    "@strapi/strapi": "4.1.8",
    "@strapi/plugin-users-permissions": "4.1.8",
    "@strapi/plugin-i18n": "4.1.8",
    "pg": "8.6.0"
}