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:xxx@example.com: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"
Release 4.0.4 of Flask-Migrate addresses this issue. Please upgrade.
The issue was caused by a backwards incompatible change in SQLAlchemy that masks the database password when obtaining the database URL.
Bug report: https://github.com/miguelgrinberg/flask-migrate/issues/505