Troubleshooting Database MFA

Known problems with database logins, by symptom. Commands run on the agent host; docker commands run on the AuthNull server of an on-premises deployment.

ProxySQL

ProxySQL is failed with Result: protocol

ProxySQL runs as the proxysql user and cannot read its configuration file. Check it:

ls -l /etc/proxysql.cnf

If it shows -rw------- root root, fix the permissions and start it:

sudo chown root:proxysql /etc/proxysql.cnf
sudo chmod 640 /etc/proxysql.cnf
sudo systemctl reset-failed proxysql
sudo systemctl start proxysql

Admin login fails: Lost connection to MySQL server at 'reading authorization packet'

This ProxySQL build requires a comma in every username. Log in as admin,x instead of admin:

mysql -u 'admin,x' -padmin -h 127.0.0.1 -P 6032 --ssl-mode=DISABLED

api_url points at the wrong address

Check the authnull section:

sudo grep -n -A4 -E '^\s*authnull\s*=' /etc/proxysql.cnf

When the agent runs on the same server as the stack, set api_url to the local authn-service, which does not depend on the server's IP:

sudo sed -i -E 's#^(\s*api_url\s*=\s*).*#\1"http://127.0.0.1:2882/authnull0/api/v1/authn/v3/do-authenticationV4"#' /etc/proxysql.cnf
sudo systemctl restart proxysql

This section is read on every login, so a restart is enough.

Database Login

FATAL: User not found

ProxySQL has no entry for the database user, so it refuses before any MFA check. List the users it knows:

mysql -u 'admin,x' -padmin -h 127.0.0.1 -P 6032 --ssl-mode=DISABLED -e "SELECT username, default_hostgroup FROM runtime_pgsql_users;"

If the user is missing, the agent has not provisioned it - typically because ProxySQL was reinstalled or reset after the policy was created. The agent provisions users only from queued jobs, so queue one again: open the policy's ⋯ menu, choose Update policy, and save it. The agent provisions the user on its next sync (within a minute).

On an on-premises deployment you can also re-queue the original job directly in the organization's database (named after ORG_NAME):

docker exec -it $(docker ps --format '{{.Names}}' | grep -i postgres) sh -c 'psql -U "$POSTGRES_USER" -d customer-org-name -c "select j.id, u.user_name, j.status, j.db_id from did.database_job_queue j join did.db_user u on u.id = j.db_user_id order by j.id desc limit 10;"'

Take the newest job for the user, then set it back to QUEUED:

docker exec -it $(docker ps --format '{{.Names}}' | grep -i postgres) sh -c 'psql -U "$POSTGRES_USER" -d customer-org-name -c "update did.database_job_queue set status = '\''QUEUED'\'' where id = <JOB_ID>;"'

Access denied for user ... (using password: YES) and no push arrives

ProxySQL is asking clients for SCRAM authentication, so the MFA check never runs. Check and fix the setting:

mysql -u 'admin,x' -padmin -h 127.0.0.1 -P 6032 --ssl-mode=DISABLED -e "UPDATE global_variables SET variable_value='1' WHERE variable_name='pgsql-authentication_method'; LOAD PGSQL VARIABLES TO RUNTIME; SAVE PGSQL VARIABLES TO DISK; SELECT variable_value FROM runtime_global_variables WHERE variable_name='pgsql-authentication_method';"

The result must be 1.

Access denied ... (using password: YES) after ProxySQL calls authn-service

Read authn-service's log for the attempt; it records the reason for every refusal:

docker logs --since 5m $(docker ps --format '{{.Names}}' | grep -i authn-service) 2>&1 | grep -iE 'DoAuthenticationDatabase|PushMFA|challeng|isValid'

Common causes:

Log shows	Fix
No push-MFA lines at all; wallet not found	authn-service is older than 2.0.0. Set the tag to 2.0.0 in docker-compose.yml, then docker compose pull authn-service && docker compose up -d authn-service
No MFA provider configured	Configure one under Settings → MFA Provider (AuthNull Authenticator, Duo Security, or Okta Verify)
No policy covers the user	The policy's IAM user must be the console user who connects
Token not recognised or expired	Generate a new connection - tokens last 180 seconds
Push denied or timed out	Approve the push on the device enrolled for the console user's email

The push is approved but the session hangs or fails with no backend

ProxySQL has no database server for the user's hostgroup:

mysql -u 'admin,x' -padmin -h 127.0.0.1 -P 6032 --ssl-mode=DISABLED -e "SELECT hostgroup_id, hostname, port, status FROM runtime_pgsql_servers;"

If the list is empty, restart the agent (sudo systemctl restart db-agent) - it registers the server on its next sync. To add it by hand:

mysql -u 'admin,x' -padmin -h 127.0.0.1 -P 6032 --ssl-mode=DISABLED -e "INSERT INTO pgsql_servers (hostgroup_id, hostname, port) VALUES (0, 'DB_SERVER_IP', 5432); LOAD PGSQL SERVERS TO RUNTIME; SAVE PGSQL SERVERS TO DISK;"

SSL SYSCALL error: EOF detected

ProxySQL's Postgres port does not offer TLS. Connect with SSL disabled: PGSSLMODE=disable psql ....

Useful Logs

Component	Command
Database agent	sudo tail -f /var/log/authnull-db-agent.log
ProxySQL	sudo tail -f /var/lib/proxysql/proxysql.log
authn-service (on-premises; MFA decisions)	docker compose logs -f authn-service