Connecting your Java AWS Lambda to an RDS database and RDS Proxy
Troubleshooting RDS IAM Authentication, and other tips
Now that we covered the setup for AWS Lambdas to connect to RDS instances and RDS Proxies, let’s go over the code necessary to authenticate your Java AWS Lambdas via IAM Authentication, as well as the traditional user/pwd auth. We’ll also cover troubleshooting, since this rarely works on the first try! 😅😅😅
Disclaimer
I Love My Local Farmer is a fictional company inspired by customer interactions with AWS Solutions Architects. Any stories told in this blog are not related to a specific customer. Similarities with any real companies, people, or situations are purely coincidental. Stories in this blog represent the views of the authors and are not endorsed by AWS.
Authenticate against RDS database (or RDS Proxy) via User/Pwd in Java
The code just looks like a regular JDBC connection:
public static Connection createConnectionViaUserPwd(
@NonNull String username, @NonNull String pwd,
@NonNull String dbEndpoint) {
Connection connection; try {
connection =
DriverManager.getConnection(JDBC_PREFIX + dbEndpoint,
username, pwd);
logger.info("Connection Established");
return connection; } catch (SQLException e) {
logger.info("Connection FAILED");
logger.error(e.getMessage(), e);
} return null;
}
The thing that will stand out though, is that the db-connection should be established in the static block of the Lambda handler:
public class PopulateFarmDb
implements RequestHandler<Object, String> {
…
static {
try {
con = DbUtil.createConnectionViaUserPwd(
DB_ADMIN_USER, DB_ADMIN_PWD, DB_ENDPOINT); } catch (Exception e) {
logger.info("INIT connection FAILED");
logger.error(e.getMessage(), e);
}
}@Override
public String handleRequest(Object event, Context context) {
…
The reason for initializing the database connection in the static block and not within the handleRequest() method is that we want to persist the DB connection within the Lambda between calls to handleRequest. In other words we want the Lambda to keep the connection object alive even after a request finishes. This way, we only pay the connection latency penalty on the first request the Lambda handles, and any subsequent calls use the established connection (which complete in milliseconds).
In the case of PopulateFarmDb Lambda, doing this is not that crucial since we only call this Lambda once to populate the tables and create the lambda_iam user.. but we still do it this way so that everyone gets into the habit.
Also, note that we’re passing in the user credentials and DB endpoint into the function. We do that by storing the ARNs of the user secrets and the db endpoint in the Lambda’s environment variables, which can be retrieved in the code through System.getenv().
Then we use the SecretsUtil.java class to invoke Secrets Manager to retrieve the user secrets and decode the credentials:
static final JSONObject DB_ADMIN_SECRET =
SecretsUtil.getSecret(DB_REGION,
System.getenv("DB_ADMIN_SECRET"));
static final String DB_ADMIN_USER =
(String) DB_ADMIN_SECRET.get("username");
static final String DB_ADMIN_PWD =
(String) DB_ADMIN_SECRET.get("password");Authenticate via RDS IAM Authentication in Java
Although with RDS IAM auth we also initialize the DB connection in the static block, the java code to do IAM auth is a different story altogether. One of the main reasons is that we need to generate an authentication token instead of just feeding in the password.
The connection code looks like this:
public Connection createConnectionViaIamAuth(
@NonNull String username,
@NonNull String dbEndpoint,
@NonNull String region) {
Connection connection;
try {
setSslProperties();
connection =
DriverManager.getConnection(
JDBC_PREFIX + dbEndpoint,
setMySqlConnectionProperties(username,
dbEndpoint,
region));
return connection; } catch (Exception e) {
logger.info("Connection FAILED");
logger.error(e.getMessage(), e);
}
return null;
}
To generate the auth token, we downloaded the RDS cert first (we opted for the global one but you could also try the regional ones), and then stored that in the ApiHandlers/resources directory.
Once the RDS cert is in place, the auth token can be generated using Java SDK v2’s RdsUtilities:
private static String generateAuthToken(
String username, String dbEndpoint, String region){ RdsUtilities utilities = RdsUtilities.builder()
.credentialsProvider(DefaultCredentialsProvider.create())
.region(Region.of(region))
.build(); GenerateAuthenticationTokenRequest authTokenRequest =
GenerateAuthenticationTokenRequest.builder()
.username(username)
.hostname(dbEndpoint)
.port(3306)
.build(); String authenticationToken =
utilities.generateAuthenticationToken(authTokenRequest); return authenticationToken;
}
Please note that RdsUtilities is the Java SDK v2 version of Java SDK v1’s RdsIamAuthTokenGenerator. We were initially using v1 but then found out that v2 is the more performant cousin of v1. To see the full implementation, please see DbUtils.java .
To make sure the RDS cert (*.pem file) gets packaged into the Lambda, we include the resources directory in the build.gradle instructions:
main.resources.srcDirs = ['scripts','resources']
This puts the contents of the resources directory in the root folder of the generated lambda.zip file, and therefore makes it accessible just by the filename inside the Lambda:
private static final String SSL_CERTIFICATE =
"rds-ca-2019-root.pem";
…
private static X509Certificate createCertificate()
throws Exception {
URL url = new File(SSL_CERTIFICATE).toURI().toURL();The complete code is too lengthy to copy in its entirety in this post, but you can find it in DbUtil.java.
Deciding on Failure handling:
Even though the RDS Proxy can mask some connectivity issues, you’ll still need failure-handling on your client code. For example, it cannot replay in-flight transactions during a failure… so if you issued a CRUD statement and the database failed during it for whatever reason, you’ll have to reissue your statement again.
Another thing we noticed was that we had to build in checking our connection object’s validity before executing any Sql statements, because if the live connection gets closed (e.g. during a DB failover event), the Lambda would hold on to the now-dead connection object and the RDS Proxy won’t recycle it. To avoid this issue, we check and re-initialize the connection at the beginning of each request.
So the question now is, how much failure handling do we need? The general best practice is to use exponential backoff retry logic and so on… but we decided to “fail fast”, which in our case means we’re doing one retry after a few seconds on failure. This is because we’re using ApiGateway in front of our Lambdas, and it caps requests at a max of 30s.., and significant database issues probably wouldn’t be resolved within that timeframe anyway.
Thus, for now we’re leaving the client of our Lambda client (in our case, a Magento application) to handle long-winded retries, and are only concerned with connectivity failures at the Lambda level which recover more easily (e.g. network blips).
Troubleshooting an RDS Proxy Connection
Tip: First of all, turn on your RDS Proxy’s Enhanced Logging.. trust me, it’ll save you loads of time. When you do, you’ll find CloudWatch logs under the name of your RDS Proxy with some helpful connectivity information.
For example, where your Lambda might only log:
[ERROR] ProgrammingError: 1045 (28000): Access denied for user ‘lambda_iam’@’%’ (using password: YES)
in the RDS Proxy log you’ll find some more useful gems like:
Credentials couldn’t be retrieved. The IAM role “arn:aws:iam::123456789012:role/DeliveryProject-Db-RdsProxyRole4…” is not authorized to read the AWS Secrets Manager secret with the ARN “arn:aws:secretsmanager:eu-west-2:123456789012:secret:DbUserSecret-…”
or:
Proxy authentication with MySQL native password authentication failed for user “lambda_iam” with TLS on. Reason: Invalid credentials. If you provide an IAM token, make sure to either use the correct password or enable IAM authentication.
This information helped me realize that I was missing the right IAM policy for accessing one of the Secrets and also that my cert wasn’t being used properly to generate the auth token.
Keep in mind that the setting will turn itself off after 24hrs!
Tip: Set the RDS Proxy’s Idle Timeout to 1 (just do it in the AWS Console) while you test. I’ve noticed that sometimes I change settings to break the RDS Proxy connectivity and yet my Lambdas keep being able to connect.. so apparently they can piggyback on an already established connection which worked previously, even though I now had a bad configuration. For this same reason, make sure to also ‘recycle’ your Lambda when you change settings (e.g. by changing the memory footprint on the console).
Tip: If you can’t connect to the RDS Proxy, try connecting to the RDS database from your local machine. Keep in mind, you cannot connect from your local desktop to RDS proxy directly. To be able to do this, you’ll have to allow incoming connections from your IP address into the RDS database.
Go to the AWS Console and edit the RDS instance’s VPC Security Group. Add an Inbound Rule for the MySql/Aurora Type, and choose MyIp as the Source. This will allow your device’s IP address to connect to the RDS instance. Now try connecting with MySql Workbench/Toad/command line as the admin user with his password, and then also try connecting with the lambda_iam user and its password. You can grab the generated passwords by going into your Secrets Manager console and pulling up the DbUserSecret**(i.e. the lambda_iam DB user) and DeliveryProjectDbFarmer** (i.e. the admin DB user) secrets.
Tip: This one perplexed us from the very beginning: as of the time of this post (June 2021), the RDS Proxy does not support RDS MySql 8 yet. Our infrastructure is stood up using the CDK since it helps us save some of the boilerplate needed to get things working, but in this case the cdk deployment will fail w/o a hint of what’s wrong 😑.
Make sure to double check the RDS MySql version you’re using against the RDS Proxy limitations.
More Connectivity Issues and their Solutions
Error: Proxy authentication with IAM authentication failed for user “lambda_iam” with TLS on. Reason: Invalid credentials. If you provide an IAM token, make sure to either use the correct password or enable IAM authentication.
Solution: the authentication token is not being generated properly. Make sure your cert is being included in your lambda’s zip file.
Error: java.sql.SQLException: Access denied for user ‘lambda_iam’@’%’ (using password: NO)
Solution: Make sure your IAM-Auth Lambda’s DB_ENDPOINT environment variable points to the proxy’s endpoint, without including the “:3306” denoting the port.
If you’re still having trouble, here are more resources to help you regain your sanity:
- This post has a simple list of things to check for when you have to troubleshoot.
- This post helped me set up everything on the console so that I could be certain that the services and IAM policies were set up right.
- This codeset helped me check through a simple Python Lambda, whether my RDS Proxy set up works properly.
- These docs have a ton more information on everything to do with the RDS Proxy
Hope you enjoyed this post and that it was useful to you. As always, feedback is welcomed, so don’t be shy and leave a comment!
Useful Links
Part I of this post:
Other Java topics we’ve covered for this project:
