Heroku Postgresql failed password authentication table creation

Question

I have created my own Flask app locally and I am trying to host it now on Heroku. I have created the database, but I still miss the tables in it.

app.config['SQLALCHEMY_DATABASE_URI'] = "postgresql://{username}:{password}@{hostname}:{port}/{databasename}".format(
    username="username_given_by_heroku",
    password="password_from_heroku",
    hostname="hostname",
    port="xxxx",
    databasename="name",

I have tried adding the tables the following way in the Heroku python console:

import psycopg2
from Website.__init__ import app, db
db.create_all()

This gives the following error:

sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) connection to server at "ec2-34-230-153-41.compute-1.amazonaws.com" (34.230.153.41), port 5432 failed: FATAL:  password authentication failed for user "username"
connection to server at "hostname" (34.230.153.41), port xxxx failed: FATAL:  no pg_hba.conf entry for host "34.205.2.34", user "username", database "database", no encryption

I have found the following link and gave the psql user a password in the PSQL shell, with no success. Flask & Alchemy - (psycopg2.OperationalError) FATAL: password authentication failed

Requirements.txt:

Flask==2.1.1
Flask_Login==0.6.0
Flask_SQLAlchemy==2.5.1
Werkzeug==2.1.1
gunicorn==20.0.4
psycopg2==2.9.3

Models.py:

from . import db

class User(db.Model):
    columns...

class otherTable(db.Model):
    ...

I also don't seem to have a pg_hba.conf or I can't find it. How can I create the tables in my Heroku database?

score 0 · Answer 1 · answered May 31 '22 at 11:43

In Heroku, you can add the PostgreSQL database as an Add-on. Just go to the resources tab on your application page in Heroku. In the Add-on search bar, search for "postgres" and select "Heroku Postgres". After selecting, click on "Attach as database". This will add the "DATABSE_URL" as a config variable in Heroku. In your python app write the following code:

SQLALCHEMY_DATABASE_URI = os.environ.get('DATABASE_URL')
if SQLALCHEMY_DATABASE_URI.startswith("postgres://"):
    SQLALCHEMY_DATABASE_URI = SQLALCHEMY_DATABASE_URI.replace(
        "postgres://", "postgresql://", 1)
app.config['SQLALCHEMY_DATABASE_URI'] = SQLALCHEMY_DATABASE_URI

Heroku Postgresql failed password authentication table creation

1 Answers1