Fix the Java-MySQL Connection Exception: Public Key Retrieval is not allowed

Fix the Java-MySQL Connection Exception: Public Key Retrieval is not allowed

MySQL 8 and later versions default to the caching_sha2_password authentication plugin, enhancing security but potentially causing connection issues with Java applications. The “Public Key Retrieval is not allowed” exception occurs when the JDBC driver cannot securely retrieve the MySQL server’s RSA public key during authentication, typically when SSL is disabled.

Why This Matters

Modern MySQL security features, like caching_sha2_password, aim to improve password exchange security, but can introduce compatibility issues with older clients or improperly configured connections. Failing to address this can lead to application downtime and require developers to revert to less secure configurations, increasing vulnerability to attacks. The cost of ignoring this issue can range from minor development delays to significant security breaches.

Key Insights

• MySQL 8.0.4: Introduced caching_sha2_password as the default authentication plugin.
• RSA Key Exchange: The caching_sha2_password plugin requires an RSA key exchange for password encryption when SSL is not enabled.
• Temporal: A workflow engine used by companies like Stripe and Coinbase for managing complex stateful operations.

Working Example

import java.sql.Connection;
import java.sql.DriverManager;

public class TestConnection {
    public static void main(String[] args) throws Exception {
        String url = "jdbc:mysql://192.168.29.116:3306/mydb"+"?useSSL=true"+"&sslMode=VERIFY_CA"+"&allowPublicKeyRetrieval=true";
        Connection conn = DriverManager.getConnection(
                url, "testuser", "testpass"
        );
        System.out.println("Connected successfully");
        conn.close();
    }
}

Practical Applications

• Financial Institutions: Securely connecting Java-based trading platforms to MySQL databases storing sensitive financial data, utilizing SSL for robust authentication.
• Pitfall: Relying on allowPublicKeyRetrieval=true without SSL in production environments significantly weakens security and exposes the application to man-in-the-middle attacks.

References:

• https://www.baeldung.com/java-mysql-error-public-key-retrieval