Implementing a connection recreation mechanism on periodic DB password change

Question

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 don't understand. Once a connection is established, it doesn't need to authenticate any more. You only have to use the current password when you establish a new connection. — Laurenz Albe, Jul 16 '19 at 16:07
@LaurenzAlbe Yes, these were exactly my thoughts. But this morning I verified that after a fresh deployment the db interaction is possible, and after exactly 15 minutes I'm getting first exceptions (I've updated the original question with the stacktrace). `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` This line is probably specific to AWS IAM auth plugin, I suppose my question should be asked at AWS Support forum instead... — szmktk, Jul 17 '19 at 08:18
Yes. It somehow sounds like there is a connection pooler in the background. — Laurenz Albe, Jul 17 '19 at 10:32
I don't think the solution you're working towards is a good approach. Dropwizard initializes connection pool during application startup which would be used throughout the life of the application.The new password wont be used unless the app is restarted. Perhaps, you avoid using the default connection pooling that is being used under the hood and manage your resources i.e. open/close DB connection? That would allow you to open connection per request with user/pass/host. Just a thought. — na-98, Jul 21 '19 at 18:53

score 3 · Accepted Answer · answered Jul 24 '19 at 16:20

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.

Implementing a connection recreation mechanism on periodic DB password change

1 Answers1