We are using a PostgreSQL database with AWS RDS IAM authorization feature – which means that our application needs to refresh the authorization token every 10 minutes or so (since the token is valid for 15 minutes). This token is used as a database password and I need to periodically update it. We are using the Dropwizard framework which is taking advantage of Apache Commons DBCP Component that handles connection pooling.
I was able to enhance the configuration class so that it performs an AWS API call to get the token instead of reading the password from configuration file. However this works only once, during application startup, for 15 minutes. I would like to call AWS API for the token perdiodically and handle the creation of connections as well as invalidating old ones.
import org.jooq.Configuration;
import org.jooq.impl.DefaultConfiguration;
import io.dropwizard.setup.Environment;
import org.example.myapp.ApplicationConfiguration;
// more less relevant imports...
@Override
public void run(ApplicationConfiguration configuration, Environment environment) {
Configuration postgresConfiguration = new DefaultConfiguration().set(configuration.getDbcp2Configuration()
.getDataSource())
.set(SQLDialect.POSTGRES_10)
.set(new Settings().withExecuteWithOptimisticLocking(true));
// this DSLContext object needs to be refreshed/recreated every 10 minutes with the new password!
KeysDAO.initialize(DSL.using(postgresConfiguration));
// rest of the app's config
}
How can I implement such a connection recreation mechanism? The org.jooq.ConnectionProvider looks promising, but I need some more guidance on how to inject the password on a periodic basis (and implement a custom ConnectionProvider). Any hints would be greatly appreciated.
EDIT: This morning I was able to confirm that after a fresh deployment the database interaction is possible, and after exactly 15 minutes I'm getting first exceptions:
org.postgresql.util.PSQLException: FATAL: PAM authentication failed for user "jikg_service"
at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:514)
at org.postgresql.core.v3.ConnectionFactoryImpl.tryConnect(ConnectionFactoryImpl.java:141)
at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:192)
at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49)
at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:195)
at org.postgresql.Driver.makeConnection(Driver.java:454)
at org.postgresql.Driver.connect(Driver.java:256)
at org.apache.commons.dbcp2.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:39)
at org.apache.commons.dbcp2.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:256)
at org.apache.commons.pool2.impl.GenericObjectPool.create(GenericObjectPool.java:868)
at org.apache.commons.pool2.impl.GenericObjectPool.ensureIdle(GenericObjectPool.java:927)
at org.apache.commons.pool2.impl.GenericObjectPool.ensureMinIdle(GenericObjectPool.java:906)
at org.apache.commons.pool2.impl.BaseGenericObjectPool$Evictor.run(BaseGenericObjectPool.java:1046)
at java.base/java.util.TimerThread.mainLoop(Timer.java:556)
at java.base/java.util.TimerThread.run(Timer.java:506)
Suppressed: org.postgresql.util.PSQLException: FATAL: pg_hba.conf rejects connection for host "172.30.19.218", user "my_db_user", database "my_db_development", SSL off
at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:514)
at org.postgresql.core.v3.ConnectionFactoryImpl.tryConnect(ConnectionFactoryImpl.java:141)
at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:201)
... 12 common frames omitted
Those exceptions are repeated every minute.
I owe you all an explanation on this one. I forgot to mention one significant detail - we're actually using a modified version of Dropwizard developed in-house that uses bundled Apache Commons DBCP (which afaik is not officially part of Dropwizard) as well as other components. I ended up dropping Apache Commons DBCP in favor of HikariCP - which made it possible to update the pool configuration at runtime. Although not officially supported, the creator of the library hinted that it might work, and in our scenario it indeed worked. Below is a sample solution.
import org.jooq.Configuration;
import org.jooq.impl.DefaultConfiguration;
import io.dropwizard.setup.Environment;
import org.example.myapp.ApplicationConfiguration;
// more less relevant imports...
@Override
public void run(ApplicationConfiguration configuration, Environment environment) {
HikariDataSource hikariDataSource = loadDatabaseConfiguration(configuration.getDatabaseConfiguration());
new DbConfigurationLoader(hikariDataSource).start();
// this DSLContext object now has the reference to DataSource object that has an always-fresh password!
KeysDAO.initialize(DSL.using(hikariDataSource, SQLDialect.POSTGRES_10, new Settings().withExecuteWithOptimisticLocking(true)));
// rest of the app's config
}
private HikariDataSource loadDatabaseConfiguration(DatabaseConfiguration configuration) {
HikariDataSource hikariDataSource = new HikariDataSource();
hikariDataSource.setJdbcUrl(configuration.getJdbcUrl());
hikariDataSource.setDriverClassName(configuration.getDriverClassName());
hikariDataSource.setMinimumIdle(configuration.getMinimumIdle());
hikariDataSource.setMaximumPoolSize(configuration.getMaximumPoolSize());
hikariDataSource.setUsername(configuration.getJdbcUser());
return hikariDataSource;
}
private class DbConfigurationLoader extends Thread {
private final HikariDataSource hikariDataSource;
private final RdsTokenProvider rdsTokenProvider;
public DbConfigurationLoader(HikariDataSource hikariDataSource) {
this.rdsTokenProvider = new RdsTokenProvider();
this.hikariDataSource = hikariDataSource;
}
@Override
public void run() {
while (true) {
hikariDataSource.setPassword(rdsTokenProvider.getToken());
try {
Thread.sleep(/* token is valid for 15 minutes, so it makes sense to refresh it more often */);
} catch (InterruptedException e) {
Thread.currentThread().interrupt();
}
}
}
}
Hope this saves somebody some time in the future.