[Python] Operational Error - password authentication failed using SQLAlchemy in python

Initially, I executed these commands to set up a docker container:

docker pull postgres:latest

docker run --name postgres -e POSTGRES_USER=myusername -e POSTGRES_PASSWORD=mypassword -p 5432:5432 -v /data:/var/lib/postgresql/data -d postgres:latest

docker exec -it postgres bash


Things have been operating as expected. Then, I tried to create and connect an SQLAlchemy engine in Python:

from sqlalchemy import create_engine

DATABASE_URL = "postgresql://myusername:mypassword@localhost/postgres"

engine = create_engine(DATABASE_URL)

try:
engine.connect()
except Exception as e: print(str(e))


however, the interpreter always complains:

OperationalError: (psycopg2.OperationalError) connection to server at "localhost" :1), port 5432 failed: FATAL: password authentication failed for user "myusername"


I tried to directly connect it from local using

psql -h localhost -U myusername

\c postgres


and have succeeded. So I thought it should be a configuration problem. Thus I modified my pg_hba.conf to be like the following:

# TYPE DATABASE USER ADDRESS METHOD

host all all all trust
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all trust
host replication all 127.0.0.1/32 trust
host replication all ::1/128 trust


but that did not work too.

Why can't I connect from running the above python code, provided that I could connect from local without difficulty?

Continue reading...