Flask & Alchemy - (psycopg2.OperationalError) FATAL: password authentication failed

Question

I'm new to python. I have to develop a simple Flask app (in my local Ubuntu 16.4) with PostgreSQL as database.

I install pgadmin, Flask, SQLAlchemy and postgres and also this is my app code:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://dbUserName:userNamePassword@localhost/dbName'

db = SQLAlchemy(app)
class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True)
    email = db.Column(db.String(120), unique=True)

    def __init__(self, username, email):
        self.username = username
        self.email = email

    def __repr__(self):
        return '<User %r>' % self.username

@app.route('/')

def index():
    return "Hello Flask"


if __name__ == "__main__":
    app.run()

Also I create a database and new user in pgAdmin (and replace them with related variable in my code), but when I try to test this code in python shell I found error.

my python code:

from app import db

result:

/home/user/point2map2/venv/lib/python3.5/site-packages/flask_sqlalchemy/__init__.py:839: FSADeprecationWarning: SQLALCHEMY_TRACK_MODIFICATIONS adds significant overhead and will be disabled by default in the future.  Set it to True or False to suppress this warning.
  'SQLALCHEMY_TRACK_MODIFICATIONS adds significant overhead and '

Then:

db.create_all() 

result:

(psycopg2.OperationalError) FATAL:  password authentication failed for user "dbUserName"
FATAL:  password authentication failed for user "dbUserName"

after a lot of search in forum I found this guide:

in your pg_hba.conf

# IPv4 local connections:
# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
host    all         all         127.0.0.1/32         trust

But its not work for me.

Answer 1

I was stuck with this same error. The problem for me was that I hadn't set the password for the psql user. See similar question with answer here: https://askubuntu.com/questions/413585/postgres-password-authentication-fails

it got solved when I did

ALTER USER db_username PASSWORD 'new_password'

Answer 2

After some debugging of my sqlalchemy code, I saw that the url that sqlalchemy used was a decoded url-string (at least for postgres). This means that if you have substrings in your connection string such as %34, the sqlalchemy connection string will be 4, as that is the url-decoded string. The solution for this problem is simple: simply replace all occurences of % in the connection string with %25, as that is the url encoding for %. The code for this is simply:

from sqlalchemy import create_engine
connection_string_orig = "postgres://user_with_%34_in_the_string:pw@host:port/db"
connection_string = connection_string_orig.replace("%", "%25")
engine = create_engine(connection_string)
print(engine.url) # should be identical to connection_string_orig
engine.connect()

This probably doesn't solve everyone's problem, but it's nevertheless good to be aware of it.