Unable to connect to PostgreSQL DB (FATAL: password authentication failed for user "mathesar")

[salim-b]

I'm trying to set up a Mathesar (v0.6.0) instance on fly.io using an external PostgreSQL (v18) DB managed by aiven.io. You can find my current configuration (excl. sensitive credentials) in this Git repository.

I think I configured everything correctly according to the docs and the v0.6.0 release notes. But when launching the Fly app, I encounter an error django.db.utils.OperationalError: connection failed: connection to server at "207.154.244.163", port 23913 failed: FATAL: password authentication failed for user "mathesar" and I'm in the dark about the reason.

Full log excerpt:

2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]The command 'python3 -m mathesar.install' failed with status '1' with the following error,
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]Traceback (most recent call last):
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]  File "/usr/local/lib/python3.13/site-packages/django/db/backends/base/base.py", line 288, in ensure_connection
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]    self.connect()
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]    ~~~~~~~~~~~~^^
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]  File "/usr/local/lib/python3.13/site-packages/django/utils/asyncio.py", line 26, in inner
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]    return func(*args, **kwargs)
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]  File "/usr/local/lib/python3.13/site-packages/django/db/backends/base/base.py", line 269, in connect
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]    self.connection = self.get_new_connection(conn_params)
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]                      ~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]  File "/usr/local/lib/python3.13/site-packages/django/utils/asyncio.py", line 26, in inner
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]    return func(*args, **kwargs)
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]  File "/usr/local/lib/python3.13/site-packages/django/db/backends/postgresql/base.py", line 275, in get_new_connection
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]    connection = self.Database.connect(**conn_params)
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]  File "/usr/local/lib/python3.13/site-packages/psycopg/connection.py", line 117, in connect
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]    raise last_ex.with_traceback(None)
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]psycopg.OperationalError: connection failed: connection to server at "207.154.244.163", port 23913 failed: FATAL:  password authentication failed for user "mathesar"
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]The above exception was the direct cause of the following exception:
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]Traceback (most recent call last):
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]  File "<frozen runpy>", line 198, in _run_module_as_main
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]  File "<frozen runpy>", line 88, in _run_code
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]  File "/code/mathesar/install.py", line 20, in <module>
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]    main()
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]    ~~~~^^
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]  File "/code/mathesar/install.py", line 13, in main
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]    management.call_command('migrate')
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]    ~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]  File "/usr/local/lib/python3.13/site-packages/django/core/management/__init__.py", line 194, in call_command
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]    return command.execute(*args, **defaults)
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]           ~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]  File "/usr/local/lib/python3.13/site-packages/django/core/management/base.py", line 458, in execute
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]    output = self.handle(*args, **options)
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]  File "/usr/local/lib/python3.13/site-packages/django/core/management/base.py", line 106, in wrapper
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]    res = handle_func(*args, **kwargs)
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]  File "/usr/local/lib/python3.13/site-packages/django/core/management/commands/migrate.py", line 117, in handle
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]    executor = MigrationExecutor(connection, self.migration_progress_callback)
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]  File "/usr/local/lib/python3.13/site-packages/django/db/migrations/executor.py", line 18, in __init__
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]    self.loader = MigrationLoader(self.connection)
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]                  ~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]  File "/usr/local/lib/python3.13/site-packages/django/db/migrations/loader.py", line 58, in __init__
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]    self.build_graph()
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]    ~~~~~~~~~~~~~~~~^^
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]  File "/usr/local/lib/python3.13/site-packages/django/db/migrations/loader.py", line 235, in build_graph
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]    self.applied_migrations = recorder.applied_migrations()
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]                              ~~~~~~~~~~~~~~~~~~~~~~~~~~~^^
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]  File "/usr/local/lib/python3.13/site-packages/django/db/migrations/recorder.py", line 81, in applied_migrations
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]    if self.has_table():
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]       ~~~~~~~~~~~~~~^^
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]  File "/usr/local/lib/python3.13/site-packages/django/db/migrations/recorder.py", line 57, in has_table
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]    with self.connection.cursor() as cursor:
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]         ~~~~~~~~~~~~~~~~~~~~~~^^
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]  File "/usr/local/lib/python3.13/site-packages/django/utils/asyncio.py", line 26, in inner
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]    return func(*args, **kwargs)
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]  File "/usr/local/lib/python3.13/site-packages/django/db/backends/base/base.py", line 329, in cursor
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]    return self._cursor()
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]           ~~~~~~~~~~~~^^
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]  File "/usr/local/lib/python3.13/site-packages/django/db/backends/base/base.py", line 305, in _cursor
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]    self.ensure_connection()
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]    ~~~~~~~~~~~~~~~~~~~~~~^^
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]  File "/usr/local/lib/python3.13/site-packages/django/utils/asyncio.py", line 26, in inner
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]    return func(*args, **kwargs)
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]  File "/usr/local/lib/python3.13/site-packages/django/db/backends/base/base.py", line 287, in ensure_connection
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]    with self.wrap_database_errors:
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]         ^^^^^^^^^^^^^^^^^^^^^^^^^
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]  File "/usr/local/lib/python3.13/site-packages/django/db/utils.py", line 91, in __exit__
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]    raise dj_exc_value.with_traceback(traceback) from exc_value
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]  File "/usr/local/lib/python3.13/site-packages/django/db/backends/base/base.py", line 288, in ensure_connection
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]    self.connect()
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]    ~~~~~~~~~~~~^^
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]  File "/usr/local/lib/python3.13/site-packages/django/utils/asyncio.py", line 26, in inner
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]    return func(*args, **kwargs)
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]  File "/usr/local/lib/python3.13/site-packages/django/db/backends/base/base.py", line 269, in connect
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]    self.connection = self.get_new_connection(conn_params)
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]                      ~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]  File "/usr/local/lib/python3.13/site-packages/django/utils/asyncio.py", line 26, in inner
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]    return func(*args, **kwargs)
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]  File "/usr/local/lib/python3.13/site-packages/django/db/backends/postgresql/base.py", line 275, in get_new_connection
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]    connection = self.Database.connect(**conn_params)
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]  File "/usr/local/lib/python3.13/site-packages/psycopg/connection.py", line 117, in connect
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]    raise last_ex.with_traceback(None)
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]django.db.utils.OperationalError: connection failed: connection to server at "207.154.244.163", port 23913 failed: FATAL:  password authentication failed for user "mathesar"
2025-09-30T20:59:46Z app[08053e1f633d78] fra [info]ERROR: Unable to continue

I set the environment variable PGSSLMODE=require to make Mathesar connect via TLS¹, but no luck.

I also tried configuring Postgres to use the now deprecated md5 password hashing algorithm instead of the recommended scram-sha-256 (and did an ALTER ROLE "mathesar" WITH LOGIN PASSWORD 'REDACTED'; to materialize the change), but still no luck.

OTOH I can successfully connect with the provided config via psql from my local machine (i.e. psql --dbname=postgres://mathesar:[email protected]:23913/mathesar?sslmode=require).

Is this a bug? Or am I missing something?

Footnotes

1. Otherwise the error above grows by an additional line connection to server at "207.154.244.163", port 23913 failed: FATAL: no pg_hba.conf entry for host "89.222.119.21", user "mathesar", database "mathesar", no encryption. ↩

[salim-b]

Nevermind, I was too tired last night and didn't realize I forgot to update the Fly secret for POSTGRES_PASSWORD to the latest value 🙈