4

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"
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
Nolan
  • 748
  • 7
  • 11
  • 2
    Works for me on SQLA 2.0 and 2.0.1, although I didn't add the options after "?". Note that 2.0 includes a change to obscure the password in the string representation of the engine or its url. How are you constructing the engine & url in your migration script? – snakecharmerb Feb 02 '23 at 19:18
  • Note also that if I create a pure psycopg2 connection (`conn.psycopg2.connect(...)`) then the password is also obscured: `conn.dsn` -> `'user=sotest password=xxx dbname=test host=localhost'` – snakecharmerb Feb 02 '23 at 20:28

2 Answers2

4

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

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

Nolan
  • 748
  • 7
  • 11
2

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

Miguel Grinberg
  • 65,299
  • 14
  • 133
  • 152