Stale SPIFFE identity after toggling `use_ssl`

[debakarr]

ProxySQL version

3.0.6 (also confirmed on 2.7.3)

Description

If a user has use_ssl=1 and a spiffe_id attribute, and an admin later
sets use_ssl=0 without clearing the SPIFFE identity, ProxySQL continues to
enforce the SPIFFE rule. The result is a generic Access denied for user
error that looks like a password/auth problem, when the real cause is that
ProxySQL is trying to extract a SPIFFE identity from a plaintext connection
where none exists.

Steps to reproduce

Reproduction repo: https://github.com/debakroy/proxysql-ssl-bypass

1. Create a user with use_ssl=1 and spiffe_id in attributes:

INSERT INTO mysql_users (username, password, active, use_ssl, attributes)
VALUES ('spiffe_user', 'spiffe_password', 1, 1,
        '{"spiffe_id": "spiffe://example.org/ns/default/sa/client"}');
LOAD MYSQL USERS TO RUNTIME;

2. Verify the user can connect with a valid SPIFFE client certificate.

3. Simulate an admin disabling SSL but forgetting to clear SPIFFE:

UPDATE mysql_users SET use_ssl=0 WHERE username='spiffe_user';
LOAD MYSQL USERS TO RUNTIME;

4. Attempt a plaintext connection:

import mysql.connector
conn = mysql.connector.connect(
    host='127.0.0.1', port=6033, user='spiffe_user',
    password='spiffe_password', ssl_disabled=True,
)

Expected behavior

Since use_ssl=0, plaintext connections should be accepted, or at minimum
the error message should indicate that the SPIFFE rule is still active.

Actual behavior

1045 (28000): ProxySQL Error: Access denied for user 'spiffe_user'

A generic Access denied misleading administrators into thinking the password
is wrong.

Suggested fix

Either:

• Automatically clear SPIFFE enforcement when use_ssl is set to 0 at the
  ProxySQL level (LOAD MYSQL USERS TO RUNTIME), or
• Return a clearer error like "SPIFFE identity requires TLS" when a
  plaintext connection is attempted by a user with spiffe_id set.

[coderabbitai]

🔗 Related PRs

#5093 - Avoid usage of deleted connection [merged]
#5285 - Fix: pgsql_users.use_ssl not being enforced on frontend connections [merged]
#5456 - feat: Add stats_tls_certificates and stats_global tables for TLS certificate metadata and tracking [merged]
#5744 - fix(mysql): advertise CLIENT_PLUGIN_AUTH_LENENC_CLIENT_DATA in server greeting [merged]
#5847 - MySrvC::connect_error(): skip ER_MUST_CHANGE_PASSWORD_LOGIN (1862) [closed]

---

📝 Issue Planner

_{Check the box below or use the @coderabbitai plan command to generate an implementation plan and prompts that you can use with your favorite coding assistant.}

• Create Plan

---

🧪 Issue enrichment is currently in open beta.

You can configure auto-planning by selecting labels in the issue_enrichment configuration.

To disable automatic issue enrichment, add the following to your .coderabbit.yaml:

issue_enrichment:
  auto_enrich:
    enabled: false

💬 Have feedback or questions? Drop into our discord!

[renecannao]

Hi @debakarr , thank you for the report.

I want your feedback on "suggested fix" .
I think a better approach is to enforce use_ssl if SPIFFE is used.
That would be in line with how ProxySQL automatically sets mysql-have_ssl=1 when mysql-default_authentication_plugin is set to caching_sha2_password

Thoughts?

[debakarr]

Hi @renecannao, thanks for looking at this.

I agree — auto-enforcing use_ssl=1 when spiffe_id is present is a cleaner
fix than the options I proposed. SPIFFE identity is meaningless without TLS,
so preventing the contradictory state at the config level makes sense.

I took a look at a 3.0 source tree. The contradictory state can be prevented with a small change in MySQL_Authentication.cpp — after attributes are parsed, if spiffe_id is found in the JSON, force use_ssl = true. This mirrors how caching_sha2_password already auto-enables mysql-have_ssl. Happy to open a PR if the approach sounds right. Is that acceptable, or would you prefer a different direction?