I am working on a simple API to query Redshift and I am encountering nothing but problems. The current one is that I am getting a SocketTimeoutException when I deploy it to Lambda. Googling this exception has tons of recommendations to add "client CIDR/IP address to the VPC security group". However, my credentials (and IP) work fine for me to access the Redshift DB from my DB Client (DBeaver), and when I run my Spring Boot application locally and call it from Postman. But once it is on Lambda I get the SocketTimeoutException.
I am reaching out to the team to check if I do need to whitelist an IP, but the headache I was having before this was about Spring Boot taking too long to build causing other types of time outs and I have a feeling that this issue has more to do with Spring Boot than it does with my Redshift connection.
Reasons I suspect this:
1. as I mentioned, I have had timeout issues for days but it only switched to the socket timeout when I went from variations of the suggested:
public StreamLambdaHandler() throws ContainerInitializationException {
long startTime = Instant.now().toEpochMilli();
handler = new SpringBootProxyHandlerBuilder()
.defaultProxy()
.asyncInit(startTime)
.springBootApplication(Application.class)
.buildAndInitialize();
}
to what a different API my company is using has:
private static SpringBootLambdaContainerHandler<AwsProxyRequest, AwsProxyResponse> handler;
static {
try {
handler = SpringBootLambdaContainerHandler.getAwsProxyHandler(Application.class);
} catch (ContainerInitializationException e) {
e.printStackTrace();
throw new RuntimeException("Could not initialize Spring Boot application", e);
}
}
2 My company deploys a much heavier api (with many endpoints, service classes, etc) that is only 60kb whereas my single endpoint api I am packaging as shaded with all the dependencies which put it at a whopping 19.6MB! I am guessing this might be affecting the load time?
3 it takes 4.227 seconds to load locally. Full Stack Trace is really really long, but here is the bit I think is most relevant:
2023-02-06T07:13:30.139-06:00 INIT_START Runtime Version: java:11.v15 Runtime Version ARN: arn:aws:lambda:us-east-1::runtime:blahhalb
2023-02-06T07:13:30.715-06:00 13:13:30.711 [main] INFO com.amazonaws.serverless.proxy.internal.LambdaContainerHandler - Starting Lambda Container Handler
*****Starts app at 7:13:31*****
2023-02-06T07:13:31.634-06:00 . ____ _ __ _ _
2023-02-06T07:13:31.634-06:00 /\\ / ___'_ __ _ _(_)_ __ __ _ \ \ \ \
2023-02-06T07:13:31.634-06:00 ( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
2023-02-06T07:13:31.634-06:00 \\/ ___)| |_)| | | | | || (_| | ) ) ) )
2023-02-06T07:13:31.634-06:00 ' |____| .__|_| |_|_| |_\__, | / / / /
2023-02-06T07:13:31.634-06:00 =========|_|==============|___/=/_/_/_/
2023-02-06T07:13:31.638-06:00 :: Spring Boot ::
2023-02-06T07:13:31.834-06:00 2023-02-06 13:13:31.833 INFO 9 --- [ main] lambdainternal.AWSLambda : Starting AWSLambda using Java 11.0.14.1 on 169.254.10.245 with PID 9 (/var/runtime/lib/aws-lambda-java-runtime-0.2.0.jar started by sbx_user1051 in /var/task)
2023-02-06T07:13:31.835-06:00 2023-02-06 13:13:31.835 INFO 9 --- [ main] lambdainternal.AWSLambda : No active profile set, falling back to default profiles: default
2023-02-06T07:13:32.722-06:00 2023-02-06 13:13:32.722 INFO 9 --- [ main] .s.d.r.c.RepositoryConfigurationDelegate : Bootstrapping Spring Data JDBC repositories in DEFAULT mode.
2023-02-06T07:13:32.787-06:00 2023-02-06 13:13:32.787 INFO 9 --- [ main] .s.d.r.c.RepositoryConfigurationDelegate : Finished Spring Data repository scanning in 58 ms. Found 1 JDBC repository interfaces.
2023-02-06T07:13:33.194-06:00 2023-02-06 13:13:33.194 INFO 9 --- [ main] c.a.s.p.i.servlet.AwsServletContext : Initializing Spring embedded WebApplicationContext
2023-02-06T07:13:33.194-06:00 2023-02-06 13:13:33.194 INFO 9 --- [ main] w.s.c.ServletWebServerApplicationContext : Root WebApplicationContext: initialization completed in 1281 ms
2023-02-06T07:13:33.587-06:00 2023-02-06 13:13:33.585 INFO 9 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...
2023-02-06T07:13:40.762-06:00 13:13:40.758 [main] INFO
***** After failing to make connection after 7 seconds, restarts app*****
com.amazonaws.serverless.proxy.internal.LambdaContainerHandler - Starting Lambda Container Handler
2023-02-06T07:13:41.613-06:00 . ____ _ __ _ _
2023-02-06T07:13:41.613-06:00 /\\ / ___'_ __ _ _(_)_ __ __ _ \ \ \ \
2023-02-06T07:13:41.613-06:00 ( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
2023-02-06T07:13:41.613-06:00 \\/ ___)| |_)| | | | | || (_| | ) ) ) )
2023-02-06T07:13:41.613-06:00 ' |____| .__|_| |_|_| |_\__, | / / / /
2023-02-06T07:13:41.613-06:00 =========|_|==============|___/=/_/_/_/
2023-02-06T07:13:41.616-06:00 :: Spring Boot ::
2023-02-06T07:13:41.807-06:00 2023-02-06 13:13:41.805 INFO 12 --- [ main] lambdainternal.AWSLambda : Starting AWSLambda using Java 11.0.14.1 on 169.254.10.245 with PID 12 (/var/runtime/lib/aws-lambda-java-runtime-0.2.0.jar started by sbx_user1051 in /var/task)
2023-02-06T07:13:41.807-06:00 2023-02-06 13:13:41.807 INFO 12 --- [ main] lambdainternal.AWSLambda : No active profile set, falling back to default profiles: default
2023-02-06T07:13:42.699-06:00 2023-02-06 13:13:42.699 INFO 12 --- [ main] .s.d.r.c.RepositoryConfigurationDelegate : Bootstrapping Spring Data JDBC repositories in DEFAULT mode.
2023-02-06T07:13:42.762-06:00 2023-02-06 13:13:42.761 INFO 12 --- [ main] .s.d.r.c.RepositoryConfigurationDelegate : Finished Spring Data repository scanning in 56 ms. Found 1 JDBC repository interfaces.
2023-02-06T07:13:43.160-06:00 2023-02-06 13:13:43.160 INFO 12 --- [ main] c.a.s.p.i.servlet.AwsServletContext : Initializing Spring embedded WebApplicationContext
2023-02-06T07:13:43.160-06:00 2023-02-06 13:13:43.160 INFO 12 --- [ main] w.s.c.ServletWebServerApplicationContext : Root WebApplicationContext: initialization completed in 1277 ms
2023-02-06T07:13:43.549-06:00 2023-02-06 13:13:43.548 INFO 12 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...
2023-02-06T07:14:14.685-06:00 2023-02-06 13:14:14.684 ERROR 12 --- [ main]
*****Tries to make a connection for 31 seconds before giving me the SocketTimeoutException*****
com.zaxxer.hikari.pool.HikariPool : HikariPool-1 - Exception during pool initialization.
2023-02-06T07:14:14.685-06:00 java.sql.SQLException: [Amazon](500150) Error setting/closing connection: SocketTimeoutException.
2023-02-06T07:14:14.685-06:00 at com.amazon.redshift.client.PGClient.connect(Unknown Source) ~[task/:na]
2023-02-06T07:14:14.685-06:00 at com.amazon.redshift.client.PGClient.<init>(Unknown Source) ~[task/:na]
2023-02-06T07:14:14.685-06:00 at com.amazon.redshift.core.PGJDBCConnection.connect(Unknown Source) ~[task/:na]
2023-02-06T07:14:14.685-06:00 at com.amazon.jdbc.common.BaseConnectionFactory.doConnect(Unknown Source) ~[task/:na]
2023-02-06T07:14:14.685-06:00 at com.amazon.jdbc.common.AbstractDriver.connect(Unknown Source) ~[task/:na]
2023-02-06T07:14:14.685-06:00 at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:138) ~[task/:na]
Is it possible that this is a Spring Boot build timeout exception? or is it much more likely that it is in fact a Redshift connection issue?
My biggest issue here was that I was trying to use a Lamda function in one AWS account to access a Redshift Cluster in a different AWS account. Because of this and the securities of the cluster, the Hikari was unable to create that connection which led to loads of timeout issues in many different forms.
I was able to get the team to put my function into the same AWS account as the cluster and everything works.
TLDR: Even with a JDBC connection using valid credentials, the lambda still needs permissions to be able to access the Redshift DB. For me, the easiest way was to put them in the same account as the administrators couldn't figure out the cross account CIDR/IP permissions.
@smac2020's answer about using the RedshiftDataClient can be an easier way to make this connection, permissions will still be and issue and since I am only allowed to have read-only access attached to my IAM Role and the Lambda function itself, this will not work for this use case as my role is not authorized to perform: redshift-data:ExecuteStatement (even if the statement is read-only).