Suddenly, Heroku credentials to a PostgreSQL server gives FATAL password for user error

Question

Without changing anything in my settings, I can't connect to my PostgreSQL database hosted on Heroku. I can't access it in my application, and is given error

OperationalError: (psycopg2.OperationalError) FATAL:  password authentication failed for user "<heroku user>" FATAL:  no pg_hba.conf entry for host "<address>", user "<user>", database "<database>", SSL off

It says SSL off, but this is enabled as I have confirmed in PgAdmin. When attempting to access the database through PgAdmin 4 I get the same problem, saying that there is a fatal password authentication for user '' error.

I have checked the credentials for the database on Heroku, but nothing has changed. Am I doing something wrong? Do I have to change something in pg_hba.conf?

Edit: I can see in the notifications on Heroku that the database was updated right around the time the database stopped working for me. I am not sure if I triggered the update, however.

Here's the notification center:

Are you using the `DATABASE_URL` every time you connect, or have you hard-coded credentials somewhere? — ChrisGPT was on strike, Jun 16 '20 at 20:17
Right now I have hard-coded the credentials through the URI in an app.config for SQL Alchemy. — Fredrik Alvsaker, Jun 16 '20 at 20:25
That's not a good idea, and it could easily bite you in the future, but it might not be your current problem. I just saw this: "I have checked the credentials for the database on Heroku, but nothing has changed". How did you check the credentials on Heroku? — ChrisGPT was on strike, Jun 16 '20 at 20:26
I am going to change it before taking the application into production. In Heroku, under the PostgreSQL resource, I went to "Settings" and clicked on "View Credentials". Here, generated keys for database, user, password, together with a combined URI, host, and port. I followed this guide when setting up the database in PgAdmin: https://medium.com/@vapurrmaid/getting-started-with-heroku-postgres-and-pgadmin-run-on-part-2-90d9499ed8fb — Fredrik Alvsaker, Jun 16 '20 at 20:32
Gotcha. Yeah, Heroku can rotate those credentials at any moment. If you're 100% sure they haven't changed that's probably not your immediate problem, but it's still a good idea to always connect via `DATABASE_URL` since that value will be updated if and when your credentials change. — ChrisGPT was on strike, Jun 16 '20 at 20:35
Oh. That screenshot you just added explicitly says, "Once it has completed, your database URL will have changed". Do you see the same host and database name as before? It looks like `DATABASE_URL` will in fact have helped here. I'll add an answer to that effect. To be clear, are you connecting from an app running on Heroku, or from elsewhere? — ChrisGPT was on strike, Jun 16 '20 at 20:37
Right now I am running a localhost application with Flask as backend, so that's where I'm connecting to the database. I haven't checked into using ```DATABASE_URL``` as a dynamic/more secure way of connecting to the database, so I'll have to read up on how to do it, but it does seem to be the problem! I have attempted to change the URI in the application, and have also changed everything else although the credentials are identical to before. Maybe I'm not getting the updated credentials, somehow? — Fredrik Alvsaker, Jun 16 '20 at 20:43
`DATABASE_URL` _should_ reflect the new credentials. Are you sure you're looking at the `DATABASE_URL` for the correct application? Do you have multiple databases? — ChrisGPT was on strike, Jun 16 '20 at 20:46

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

In general, it isn't a good idea to hard-code credentials when connecting to Heroku Postgres:

Do not copy and paste database credentials to a separate environment or into your application’s code. The database URL is managed by Heroku and will change under some circumstances such as:

User-initiated database credential rotations using heroku pg:credentials:rotate.

Catastrophic hardware failures that require Heroku Postgres staff to recover your database on new hardware.

Security issues or threats that require Heroku Postgres staff to rotate database credentials.

Automated failover events on HA-enabled plans.

It is best practice to always fetch the database URL config var from the corresponding Heroku app when your application starts. For example, you may follow 12Factor application configuration principles by using the Heroku CLI and invoke your process like so:
DATABASE_URL=$(heroku config:get DATABASE_URL -a your-app) your_process
This way, you ensure your process or application always has correct database credentials.

Based on the messages in your screenshot, I suspect you were affected by the second bullet. Whatever the cause, one of those messages explicitly says

Once it has completed, your database URL will have changed

Thank you so much for your time! Instead of using the URI, I have now included the ```DATABASE_URL``` in my Flask application. Since I'm using Flask-SQLAlchemy, I did this through ```app.config['SQLALCHEMY_DATABASE_URI'] = 'database_url...'```. I am able to connect to my database again, and I can see on Heroku that the credentials have changed now. — Fredrik Alvsaker, Jun 16 '20 at 21:31
I'm glad you got this working! Though the URL itself might change, which is why relying on the `DATABASE_URL` environment variable is a safer choice. I suggest taking that extra step before you run this in production. — ChrisGPT was on strike, Jun 16 '20 at 23:11
For some reason, Heroku no longer updated `DATABASE_URL` automatically. I had to manually copy from the new env variable `HEROKU_POSTGRESQL_MAROON_URL` into the more env variable name used by my code. Is Heroku degrading? — Josiah Yoder, Sep 16 '22 at 21:43

score 0 · Answer 2 · answered Aug 06 '20 at 15:25

I had the same issue. Thx to @Chris I solved it this way. This file is in config/database.js (Strapi 3.1.3)

var parseDbUrl = require("parse-database-url");

if (process.env.NODE_ENV === 'production') {
  module.exports = ({ env }) => {
    var dbConfig = parseDbUrl(env('DATABASE_URL', ''));
    return {
      defaultConnection: 'default',
      connections: {
        default: {
          connector: 'bookshelf',
          settings: {
            client: dbConfig.driver,
            host: dbConfig.host,
            port: dbConfig.port,
            database: dbConfig.database,
            username: dbConfig.user,
            password: dbConfig.password,
          },
          options: {
            ssl: false,
          },
        },
      },
    }
  };
} else {
  // to use the default local provider you can return an empty configuration
  module.exports = ({ env }) => ({
    defaultConnection: 'default',
    connections: {
      default: {
        connector: 'bookshelf',
        settings: {
          client: 'sqlite',
          filename: env('DATABASE_FILENAME', '.tmp/data.db'),
        },
        options: {
          useNullAsDefault: true,
        },
      },
    },
  });
}

Suddenly, Heroku credentials to a PostgreSQL server gives FATAL password for user error

2 Answers2