AWS Database Blog

Introducing the Advanced JDBC Wrapper Driver for Amazon Aurora

Today’s modern applications are expected to be scalable and resilient. The top of this list is scalability, which depending on the size of the application workload could mean the ability to handle millions of users on demand. With stateful applications such as eCommerce, Financial Services and Games, this means having highly available databases.

With the release of Amazon Aurora in 2015, customers could run relational databases in an Aurora cluster comprising of one writer and up to 15 low-latency reader nodes. This enables applications to scale reads significantly. However, as with any database supporting multiple instances, developers have built complex application logic to deal with special events such as switchover or failover.

Instead of writing complex endpoint logic into the application, the functionality to handle things like switchover or failover is now available in the Advanced JDBC (Java Database Connectivity) Wrapper Driver. Additionally, the driver also integrates the handling of authentication using either AWS Secrets Manager or AWS Identity and Access Management (IAM). The Advanced JDBC Wrapper Driver has been released as an open-source project under the Apache 2.0 License. You can find the project on GitHub.

In this post, we provide details on how to use some of the features of the Advanced JDBC Wrapper Driver.

Features of the Advanced JDBC Wrapper Driver

The Advanced JDBC Wrapper Driver “wraps” the native PostgreSQL or the MySQL JDBC driver, adding failover and authentication features before delegating the function call to the underlying driver. Using the native driver to execute the API reduces the complexity of the code and focuses only on the additional features.

Aurora provides a cluster of DB instances instead of a single instance. Each connection is handled by a specific DB instance. When you connect to an Aurora cluster, the host name and port that you specify point to an intermediate handler is called an endpoint. Aurora uses the endpoint mechanism to abstract these connections.

In the following sections, we discuss some of the challenges that the driver mitigates.

Switchover

A switchover is when a reader is promoted to a writer. This can be due to changes in configuration of the writer causing it to be restarted.

Clients have the option to connect to either the cluster writer endpoint or the cluster reader endpoint. Normally, they would connect to the writer endpoint. This endpoint is directed to the current writer instance. When a restart of the cluster occurs due to either a change in the parameters of the cluster or a failover, one of the readers is promoted to be the new writer. When this occurs, the cluster writer endpoint is updated to point to the new writer by updating the instance endpoint in the DNS. Due to inherent delays in DNS propagation, this can take up to 30 seconds to resolve. Normally, the application would require logic to reconnect after the temporary lack of availability of the database. The Advanced JDBC Wrapper Driver has built in the capability to automatically detect and switch the connection to the new writer. By utilizing topology information inside the Aurora database, the driver can switch over the connection in around 6 seconds.

IAM integration

With IAM, you can specify which user can access services and resources in AWS, centrally manage fine-grained permissions, and analyze access to refine permissions across AWS. IAM enhances security by automatically changing the token (password) regularly. Although it’s completely acceptable to write the logic to acquire and use the IAM tokens in the application, this adds complexity to the application because you have to deal with acquiring and caching the token. The driver has built in this feature so that the application doesn’t have to deal with these complexities, even during switchover. See Creating and using an IAM policy for IAM database access – Amazon Aurora for details on how to create the IAM policy

Secrets Manager integration

Secrets Manager enables storing and managing credentials centrally. Storing the credentials centrally provides the ability to rotate them according to your security requirements. Secrets Manager can be used to store the user name and password for connection authentication. If this feature is enabled, the driver fetches and uses the credentials from Secrets Manager for connection authentication.

Solution overview

Without the Advanced JDBC Wrapper, the typical Java code to connect and query a database looks like the following:

while(true) {
    try (Connection connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD)) {
        try (PreparedStatement preparedStatement = connection.prepareStatement("SELECT SUM(a) FROM IntegerOverflowTest;")) {
            try (ResultSet resultSet = preparedStatement.executeQuery()) {
                resultSet.next();
                long l = resultSet.getLong(1);
                System.out.println("sum(a) -> " + l);
            }
        }catch (SQLException ex){
        /*
        * catch exceptions relative to the select and handle them 
        * if the exception is connection closed then reconnect and try again
        */
        }
    }catch (SQLException ex){
    /*
     * catch and handle connection errors here
     */
    }
}

When the connection fails due to a switchover, the attempt to acquire a connection will fail until the new writer comes online. Without the Advanced JDBC Wrapper Driver, the connection has to wait for the DNS to propagate and the application would be offline for upwards of 30 seconds.

With the advanced driver, the reconnection is handled automatically. Upon the initial connection, the driver reads the topology of the cluster and gets the instance endpoints so that if the connection fails, the driver knows the IP addresses of all the instances in the cluster. When the connection fails on the user application, the driver starts polling the instances to get a connection. As soon as a connection is established, we can read the new topology and get the IP address of the new writer and connect. Because this is all done inside the driver, the user application never sees the dropped connection or the connection failure. The driver throws an exception to let the user application know that the connection has been switched to a new server. In the event of a failure in the middle of an in-flight transaction, an exception is thrown to the user application to indicate that the transaction failed and has to be retried. Small changes in the user application are required to identify and handle the exception appropriately.

The scheme of the URL is jdbc:aws-wrapper:postgresql: for the PostgreSQL JDBC driver and jdbc:aws-wrapper:mysql: for the MySQL JDBC driver. The aws-advanced-wrapper driver accepts anything that starts with jdbc:aws-wrapper and then uses the next part of the scheme to determine which underlying driver to wrap.

The following code demonstrates how to connect to an Aurora PostgreSQL cluster and configure failover:

properties.setProperty(PropertyDefinition.PLUGINS.name,
                        "auroraConnectionTracker,failover,efm");
while( true ) {
    try (Connection connection =
        DriverManager.getConnection("jdbc:aws-wrapper:postgresql://" + WRITER + "/read_db", properties)) {
        try (Statement statement = connection.createStatement()) {
            try (ResultSet rs = statement.executeQuery("select 1")) {
                while (rs.next()) ;
            }
        } catch (FailoverSuccessSQLException ex) {
            /*
            we can safely ignore this error
            the connection was re-established
            create the statement again and execute the query.
            */

        } catch (TransactionStateUnknownSQLException ex) {
        // ignore this as well since we aren't really in a transaction
        }

    } catch (FailoverFailedSQLException ex) {
    /*
      At this point the driver was unable to re-establish the connection so
      just loop and get a new connection.
    */
    } catch (SQLException ex) {
    /*
    any number of exceptions above, handle as normal
    */
    }
    try {
        Thread.sleep(1000);
    } catch (InterruptedException e) {
    //ignore
    }
}

Because we are looking for the new writer using the instance endpoints and doesn’t depend on the DNS cache, the driver is able to reconnect much faster, typically around 6 seconds after the driver notices the failure.

Prerequisites

The AWS Advanced JDBC Wrapper is implemented using the JAVA Proxy pattern. This means that the native PostgreSQL or MySQL drivers are required to be added as dependencies.

Creating an application that uses the AWS Advanced JDBC Wrapper Driver

The following section outlines the steps required to create an application that uses the AWS Advanced JDBC Wrapper Driver.

Add maven dependencies

Add the following maven dependencies to your pom.xml file if you are using Maven to build:

<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.6.0</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.31</version
</dependency
<dependency>
<groupId>software.amazon.jdbc</groupId>
<artifactId>aws-advanced-jdbc-driver</artifactId>
<version>2.2.3</version>
</dependency>

If you want to use the IAM plugin, you also need the following:

<dependency>
    <groupId>software.amazon.awssdk</groupId>
    <artifactId>rds</artifactId>
    <version>2.20.49</version>  
</dependency>

If you want to use the Secrets Manager plugin, you also need the following:

<dependency>
    <groupId>software.amazon.awssdk</groupId>
    <artifactId>secretsmanager</artifactId>
    <version>2.20.88</version>    
</dependency>
<dependency>
    <groupId>com.fasterxml.jackson.core</groupId>
    <artifactId>jackson-databind</artifactId>
    <version>2.15.2</version>    
</dependency>

Update your code

After you have the correct dependencies, there are a few changes required to your application to use the AWS Advanced JDBC Wrapper. The connection URL needs to be jdbc:aws-wrapper:postgresql for PostgreSQL or jdbc:aws-wrapper:mysql for MySQL. The first part of the URL loads the AWS Advanced JDBC Wrapper, and the second part tells the driver which underlying driver to load and proxy (Java proxy pattern).

Specify driver features

Determine the features of the driver you want to use. The AWS JDBC Driver uses plugins to run JDBC methods. You can think of a plugin as an extensible code module that adds extra logic around any JDBC method calls. The AWS Advanced JDBC Wrapper Driver has a number of built-in plugins. This is configured in the wrapperPlugins driver property. For more information, see Connection Plugin Manager Parameters. The default setting for this is auroraConnectionTracker,failover,efm. The auroraConnectionTracker plugin makes sure that all open connections to the failed node are closed in the event of a failover. The failover plugin handles the actual failover detection and reconnection. The efm plugin actively monitors the hosts to decrease the reconnection time upon failover.

Integrate Secrets Manager

AWS provides a service to store secrets. The AWS Advanced JDBC Wrapper Driver provides a plugin that facilitates storing the values for username and password in a secret and accessing it to provide the values for USER and PASSWORD for authentication.

The only three properties required are REGION_PROPERTY, SECRET_ID_PROPERTY, and PLUGINS.

The following code block shows an example:

public static void main(String[] args) throws SQLException {
    /* Set the AWS Secrets Manager Connection Plugin parameters and
    the JDBC Wrapper parameters.
    */
    final Properties properties = new Properties();
    REGION_PROPERTY.set(properties, "us-east-2");
    SECRET_ID_PROPERTY.set(properties, "secretId");

    // Enable the AWS Secrets Manager Connection Plugin.
    PLUGINS.set(properties, "awsSecretsManager");

    // Try and make a connection:
    try (final Connection conn = DriverManager.getConnection(CONNECTION_STRING, properties);
         final Statement statement = conn.createStatement();
         final ResultSet rs = statement.executeQuery("SELECT * FROM employees")) {
      while(rs.next()){
        System.out.println(rs.getString()...);
      }
    }
  }

Integrate IAM authentication

The Advanced JDBC Wrapper supports IAM authentication. When using IAM database authentication, the host URL must be a valid Amazon endpoint, and not a custom domain or an IP address. For example: db-identifier.cluster-XYZ.us-east-2.rds.amazonaws.com.

IAM authentication requires the AWS Java SDK RDS v2.x to be included separately in the CLASSPATH. The AWS Java SDK RDS is a runtime dependency and must be resolved. For the AWS SDK, you will be required to set up your AWS_ACCESS_KEY_ID and AWS_SECRET_ACCESS_KEY to authenticate.

IAM database authentication is limited to certain database engines. For more information on limitations and recommendations, refer to IAM database authentication for MariaDB, MySQL, and PostgreSQL.

Configure IAM authentication

Complete the following steps to set up IAM authentication:

  1. Enable IAM database authentication on an existing database or create a new database with IAM database authentication on the Amazon RDS console.
  2. Set up an IAM policy for IAM database authentication.
  3. Create a database account using IAM database authentication. Connect to your database of choice using primary logins:
    1. For a MySQL database, use the following command to create a new user: 
      CREATE USER example_user_name IDENTIFIED WITH AWSAuthenticationPlugin AS 'RDS';
    2. For a PostgreSQL database, use the following command to create a new user: 
      CREATE USER db_userx; GRANT rds_iam TO db_userx;

Use IAM authentication with the Advanced JDBC Wrapper Driver

The following code provides an example of how to use IAM authentication with the driver:

public class AwsIamAuthenticationPostgresqlExample {
  public static final String POSTGRESQL_CONNECTION_STRING =
      "jdbc:aws-wrapper:postgresql://db-identifier.XYZ.us-east-2.rds.amazonaws.com:5432/employees";
  private static final String USERNAME = "john_smith";

  public static void main(String[] args) throws SQLException {

    final Properties properties = new Properties();

    // Enable AWS IAM database authentication and configure driver property values
    properties.setProperty(PropertyDefinition.PLUGINS.name, "iam");
    properties.setProperty(PropertyDefinition.USER.name, USERNAME);

    // Attempt a connection
    try (Connection conn = DriverManager.getConnection(POSTGRESQL_CONNECTION_STRING,
    properties);
        Statement statement = conn.createStatement();
        ResultSet result = statement.
                    executeQuery("select aurora_db_instance_identifier()")) {

      System.out.println(Util.getResult(result));
    }
  }
}

Clean up

If you have created any Aurora clusters IAM credentials or Secrets Manager credentials while following this blog, make sure you delete them.

Summary

The AWS Advanced JDBC Wrapper uses IAM or Secrets Manager from AWS and Cluster configuration provided by Aurora to provide a solution for authentication and failover. Additionally, by taking advantage of the features in Aurora, the driver reduces failover time significantly. There are many more features of the driver, which we will explore in future posts. Stay tuned! In the meantime, please share your comments on this post and visit the GitHub project for more details and examples.

About the author

Dave Cramer is a Senior Software Engineer for Amazon Web Services. He is also a major contributor to PostgreSQL as the maintainer of the PostgreSQL JDBC driver. His passion is client interfaces and working with clients