The AwsSecretsManagerConnectionPlugin.java still use old password from AWS DB secret after rotatation

[testereg]

Describe the bug

the following codes in MySQLExceptionHandler.java return false which is supposed to be true when the exception was root caused by a staled password.

public boolean isLoginException(final Throwable throwable) {
Throwable exception = throwable;

while (exception != null) {
  if (exception instanceof SQLLoginException) {
    return true;
  }
  _**if (exception instanceof SQLException) {
    return isLoginException(((SQLException) exception).getSQLState());**_
  } else if (exception instanceof CJException) {
    return isLoginException(((CJException) exception).getSQLState());
  }

  exception = exception.getCause();
}

return false;

}

since it returns false, which causes AwsSecretsManagerConnectionPlugin to not do the forceReFetch from secretManger.

I made some changes in the class to fix it in my local. but want to make sure this is a bug. Thanks!

Daniel

Expected Behavior

if the return is true, the AwsSecretsManagerConnectionPlugin will do forceRefectch to get the new password for the connection.

What plugins are used? What other connection properties were set?

autoReconnect=true&serverTimezone=UTC&enabledTLSProtocols=TLSv1.2&wrapperPlugins=auroraConnectionTracker,failover,efm2,awsSecretsManager&wrapperDialect=aurora-mysql&exception-override-class-name=software.amazon.jdbc.util.HikariCPSQLException

Current Behavior

Make sure that there is a MySQL server running on the machine/port you are trying to connect to and that the machine this software is running on is able to connect to this host/port (i.e. not firewalled). Also make sure that the server has not been started with the --skip-networking flag.

at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:102)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:81)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:55)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:65)
at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:414)
at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:205)
at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:180)
at software.amazon.jdbc.DriverConnectionProvider.connect(DriverConnectionProvider.java:136)
at software.amazon.jdbc.plugin.DefaultConnectionPlugin.connectInternal(DefaultConnectionPlugin.java:188)
at software.amazon.jdbc.plugin.DefaultConnectionPlugin.connect(DefaultConnectionPlugin.java:176)
at software.amazon.jdbc.ConnectionPluginManager.lambda$connect$6(ConnectionPluginManager.java:373)
at software.amazon.jdbc.ConnectionPluginManager.lambda$null$0(ConnectionPluginManager.java:263)
at software.amazon.jdbc.ConnectionPluginManager.executeWithTelemetry(ConnectionPluginManager.java:240)
at software.amazon.jdbc.ConnectionPluginManager.lambda$makePluginChainFunc$1(ConnectionPluginManager.java:263)
at software.amazon.jdbc.ConnectionPluginManager.lambda$null$2(ConnectionPluginManager.java:268)
at software.amazon.jdbc.plugin.AwsSecretsManagerConnectionPlugin.connectInternal(AwsSecretsManagerConnectionPlugin.java:203)
at software.amazon.jdbc.plugin.AwsSecretsManagerConnectionPlugin.connect(AwsSecretsManagerConnectionPlugin.java:194)
at software.amazon.jdbc.ConnectionPluginManager.lambda$connect$6(ConnectionPluginManager.java:373)
at software.amazon.jdbc.ConnectionPluginManager.lambda$null$3(ConnectionPluginManager.java:267)
at software.amazon.jdbc.ConnectionPluginManager.executeWithTelemetry(ConnectionPluginManager.java:240)
at software.amazon.jdbc.ConnectionPluginManager.lambda$makePluginChainFunc$4(ConnectionPluginManager.java:267)
at software.amazon.jdbc.ConnectionPluginManager.lambda$null$2(ConnectionPluginManager.java:268)
at software.amazon.jdbc.plugin.efm2.HostMonitoringConnectionPlugin.connectInternal(HostMonitoringConnectionPlugin.java:234)
at software.amazon.jdbc.plugin.efm2.HostMonitoringConnectionPlugin.connect(HostMonitoringConnectionPlugin.java:229)
at software.amazon.jdbc.ConnectionPluginManager.lambda$connect$6(ConnectionPluginManager.java:373)
at software.amazon.jdbc.ConnectionPluginManager.lambda$null$3(ConnectionPluginManager.java:267)
at software.amazon.jdbc.ConnectionPluginManager.executeWithTelemetry(ConnectionPluginManager.java:240)
at software.amazon.jdbc.ConnectionPluginManager.lambda$makePluginChainFunc$4(ConnectionPluginManager.java:267)
at software.amazon.jdbc.ConnectionPluginManager.lambda$null$2(ConnectionPluginManager.java:268)
at software.amazon.jdbc.plugin.staledns.AuroraStaleDnsHelper.getVerifiedConnection(AuroraStaleDnsHelper.java:69)
at software.amazon.jdbc.plugin.failover.FailoverConnectionPlugin.connectInternal(FailoverConnectionPlugin.java:777)
at software.amazon.jdbc.plugin.failover.FailoverConnectionPlugin.connect(FailoverConnectionPlugin.java:770)
at software.amazon.jdbc.ConnectionPluginManager.lambda$connect$6(ConnectionPluginManager.java:373)
at software.amazon.jdbc.ConnectionPluginManager.lambda$null$3(ConnectionPluginManager.java:267)
at software.amazon.jdbc.ConnectionPluginManager.executeWithTelemetry(ConnectionPluginManager.java:240)
at software.amazon.jdbc.ConnectionPluginManager.lambda$makePluginChainFunc$4(ConnectionPluginManager.java:267)
at software.amazon.jdbc.ConnectionPluginManager.lambda$null$2(ConnectionPluginManager.java:268)
at software.amazon.jdbc.plugin.AuroraConnectionTrackerPlugin.connectInternal(AuroraConnectionTrackerPlugin.java:93)
at software.amazon.jdbc.plugin.AuroraConnectionTrackerPlugin.connect(AuroraConnectionTrackerPlugin.java:86)
at software.amazon.jdbc.ConnectionPluginManager.lambda$connect$6(ConnectionPluginManager.java:373)
at software.amazon.jdbc.ConnectionPluginManager.lambda$null$3(ConnectionPluginManager.java:267)
at software.amazon.jdbc.ConnectionPluginManager.executeWithTelemetry(ConnectionPluginManager.java:240)
at software.amazon.jdbc.ConnectionPluginManager.lambda$makePluginChainFunc$4(ConnectionPluginManager.java:267)
at software.amazon.jdbc.ConnectionPluginManager.executeWithSubscribedPlugins(ConnectionPluginManager.java:230)
at software.amazon.jdbc.ConnectionPluginManager.connect(ConnectionPluginManager.java:370)
at software.amazon.jdbc.wrapper.ConnectionWrapper.init(ConnectionWrapper.java:160)
at software.amazon.jdbc.wrapper.ConnectionWrapper.<init>(ConnectionWrapper.java:105)
at software.amazon.jdbc.Driver.connect(Driver.java:177)
at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:121)
at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:364)
at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:206)
at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:476)
at com.zaxxer.hikari.pool.HikariPool.access$100(HikariPool.java:71)
at com.zaxxer.hikari.pool.HikariPool$PoolEntryCreator.call(HikariPool.java:726)
at com.zaxxer.hikari.pool.HikariPool$PoolEntryCreator.call(HikariPool.java:712)
at java.base/java.util.concurrent.FutureTask.run$$$capture(FutureTask.java:264)
at java.base/java.util.concurrent.FutureTask.run(FutureTask.java)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
at java.base/java.lang.Thread.run(Thread.java:840)

Caused by: com.mysql.cj.exceptions.UnableToConnectException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.
at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:77)
Caused by: com.mysql.cj.exceptions.UnableToConnectException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.

at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:499)
at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:480)
at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:52)
at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:95)
at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:807)
at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:387)
... 55 common frames omitted

Caused by: java.sql.SQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:102)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:81)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:55)
Caused by: java.sql.SQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.

at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:65)
at com.mysql.cj.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:886)
at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:805)
... 56 common frames omitted

Caused by: com.mysql.cj.exceptions.CJException: Access denied for user 'dwu-cpa'@'172.22.1.186' (using password: YES)
at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
Caused by: com.mysql.cj.exceptions.CJException: Access denied for user 'dwu-cpa'@'172.22.1.186' (using password: YES)

at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:77)
at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:499)
at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:480)
at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:52)
at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:95)
at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:140)
at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:118)
at com.mysql.cj.protocol.a.NativeProtocol.checkErrorMessage(NativeProtocol.java:857)
at com.mysql.cj.protocol.a.NativeProtocol.checkErrorMessage(NativeProtocol.java:780)
at com.mysql.cj.protocol.a.NativeProtocol.checkErrorMessage(NativeProtocol.java:749)
at com.mysql.cj.protocol.a.NativeProtocol.checkErrorMessage(NativeProtocol.java:149)
at com.mysql.cj.protocol.a.NativeAuthenticationProvider.proceedHandshakeWithPluggableAuthentication(NativeAuthenticationProvider.java:459)
at com.mysql.cj.protocol.a.NativeAuthenticationProvider.connect(NativeAuthenticationProvider.java:205)
at com.mysql.cj.protocol.a.NativeProtocol.connect(NativeProtocol.java:1336)
at com.mysql.cj.NativeSession.connect(NativeSession.java:153)
at com.mysql.cj.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:826)
... 57 common frames omitted

08-08-2024 17:03:10.319 http-nio-8899-exec-1 [DEBUG] CustomUserInfoTokenServices - Getting user info

Reproduction Steps

run the request, wait a while based on the Hikari configuration, and then rotate the secret.

Possible Solution

fix the following codes in MySQLExceptionHandler.java

public boolean isLoginException(final Throwable throwable) {
Throwable exception = throwable;

while (exception != null) {
  if (exception instanceof SQLLoginException) {
    return true;
  }
  if (exception instanceof SQLException) {
    return isLoginException(((SQLException) exception).getSQLState());
  } else if (exception instanceof CJException) {
    return isLoginException(((CJException) exception).getSQLState());
  }

  exception = exception.getCause();
}

return false;

}

Additional Information/Context

No response

The AWS Advanced JDBC Driver version used

implementation("org.springframework.boot:spring-boot-starter-data-jdbc")     implementation("org.springframework.boot:spring-boot-starter-web")     implementation("com.mysql:mysql-connector-j:9.0.0")     implementation group: 'software.amazon.jdbc', name: 'aws-advanced-jdbc-wrapper', version: '2.3.8'     implementation group: 'software.amazon.awssdk', name: 'secretsmanager', version: '2.26.29'     implementation group: 'com.fasterxml.jackson.core', name: 'jackson-databind', version: '2.13.4'

JDK version used

daniel.wu@CAI-X66N2NCY71 cpa % java -version openjdk version "17.0.11" 2024-04-16 OpenJDK Runtime Environment Homebrew (build 17.0.11+0) OpenJDK 64-Bit Server VM Homebrew (build 17.0.11+0, mixed mode, sharing)

Operating System and version

Apple M2 Pro Sonoma 14.5

[testereg]

Any update on this? This is what I found:

In the MySQLExceptionHandler.isLoginException(final Throwable throwable) method, we currently retrieve the SQLState from the wrapper exception (SQLNonTransientConnectionException). I believe it would be more accrutate to use the rootCause of the SQLNonTransientConnectionException instead, which should resolve the issue.

[aaron-congo]

Hi @testereg, thank you for reaching out and raising this issue. I'll be taking a look at this to test out your proposed fix. You mention that to reproduce the issue you have to "run the request" and then "wait a while based on the Hikari configuration". I have a couple of related questions:

1. Can you please clarify which Hikari setting you are referring to when you say you have to wait based on the hikari configuration?
2. By "run the request", are you referring to calling HikariDataSource#getConnection, which triggers the secrets fetch?

Thanks for your patience!

[aaron-congo]

Hi @testereg, I believe I have reproduced the issue and have just merged in a fix. Could you kindly checkout our snapshot build and let us know if the issue still persists? The build should be here and has the name "aws-advanced-jdbc-wrapper-2.3.10-20240906.163227-18.jar"

Thank you!

[Clete2]

@aaron-congo We are running RDS Aurora PostgreSQL, and the snapshot version fixed the issue for us.

I hope it will be published officially soon! Thank you for your effort on this issue.

[aaron-congo]

@Clete2 Glad to hear that worked for you! We are aiming for a release by this Thursday.

[aaron-congo]

Hi @Clete2, we just released this fix in version 2.4.0. I'll close this issue now that the fix is in 2.4.0, since you mentioned the snapshot version fixed the issue for you, but please feel free to reopen or to open a new issue if you encounter any problems. Thanks!

[Clete2]

Thank you @aaron-congo ! We will upgrade.