Authentication failure with Flask-Migrate and SQLAlchemy 2.0

Question

After upgrading my flask application from SQLAlchemy 1.4.46 to 2.0.1 I'm seeing that I get a password authentication failed error during flask db upgrade (Flask-Migrate). I'm able to connect fine running flask normally. DB upgrades were working fine before the SQLAlchemy upgrade, and they work fine if I downgrade back to 1.4.46.

Has anything changed? I'm providing the same SQLALCHEMY_DATABASE_URL is constructed with:

SQLALCHEMY_DATABASE_URI = f"postgresql+psycopg2://{PG_USER}:{PG_PASSWORD}@{PG_HOST}:5432/{PG_DB}?{urlencode(LIBPQ_PARAMS)}"

and becomes

postgresql+psycopg2://user:[email protected]:5432/exampledb?connect_timeout=10&keepalives=1&keepalives_idle=60&keepalives_interval=10&keepalives_count=5&sslmode=require

in the logs, I see:

PG-00000 LOG:  connection received: host=10.101.15.236 port=53150
PG-28P01 FATAL:  password authentication failed for user "user"
PG-28P01 DETAIL:  Connection matched pg_hba.conf line 18: "hostssl all             all             0.0.0.0/0               scram-sha-256"

  File "/home/so/venv/lib64/python3.8/site-packages/psycopg2/__init__.py", line 122, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) connection to server at "example.com" (10.101.1.28), port 5432 failed: FATAL:  password authentication failed for user "user"

Answer 1

This is a known issue in Flask-Migrate at this time:

https://github.com/miguelgrinberg/Flask-Migrate/issues/505