Integration with RDS

[maciejwalkowiak]

This issue collects all the requirements for RDS support in Spring Cloud AWS 3.0 (based on AWS SDK v2).

The key feature in RDS integration is support for read replicas:

• use RDS read replicas for readOnly connections (transactions started with @Transactional(readOnly=true)

Additionally we need to support:

• IAM Database authentication for RDS #320
• failover

Both can be achieved with official drivers:

• https://github.com/awslabs/aws-mysql-jdbc
• https://github.com/awslabs/aws-postgresql-jdbc

Regarding read replicas support, in addition to what has been offered in 2.x we must support:

• ability to choose connection pool (in 2.x only Tomcat pool is supported)
• ability to choose JDBC driver
• Add support for passing query parameters to JDBC url #679
• Aurora

To research:

• should we support RDS proxy?
• should we support Aurora Serverless?

[srikanthnutigattu-tomo]

Bump! Any plans to support this?

[maciejwalkowiak]

@srikanthnutigattu-tomo yes, still planned for 3.0, but the design work still pending. Is there something specific you are looking for in this integration or you would like to share ideas?

[srikanthnutigattu-tomo]

Specifically looking for support of Read replicas on AWS Postgres Aurora RDS. Currently all queries go to read/write, which no helping autoscaling of RDS.

[maciejwalkowiak]

Understood. Yes it is planned. For the time being, you can follow Vlad's tutorial: https://vladmihalcea.com/read-write-read-only-transaction-routing-spring/

[frankjkelly]

Excited for this!

[mandarbk]

Are there any plans to provide support for rotating credentials with spring cloud aws RDS ?
Also anything to support TLS connections (RDS instances with ACM managed Certificates) ?
Cloud AWS Documentation does not provide any view on either of these RDS features.

[maciejwalkowiak]

@mandarbk I only planned for supporting rotating credentials with RDS + Secrets Manager. I am missing knowledge about ACM Managed Crtificates.

[areddy23]

Hi I am assuming spring-cloud-starter-aws-jdbc is a "io.awspring.cloud" version of "spring-cloud-aws-jdbc" ( 2.x.x version)

I feel this enhancement request should be backed in to 3.x.x if not already.

Steps to recreate:

1. Create a Primary RDS(abc) with Read Replicas in 2 diff regions us-east-1(abc-rr-1) and us-west-2(abc-rr-2). When you configure current "spring-cloud-aws-jdbc" to manage traffic to RR's it fails the reason is as follows.

In 2.x.x the library is attempting to "createInstance" and calls describe-db-instances which results below: (using cli to replicate the issue)

Class: AmazonRdsReadReplicaAwareDataSourceFactoryBean
method: createInstance

1. "aws rds describe-db-instances --db-instance-identifier abc" Primary DB is yielding a list of RR associate with it across regions, please note it is returning DB Identifier for the RR in the same region and arn for the RR in diff region.
   ==============
   aws rds describe-db-instances --db-instance-identifier prod-pgsql

From result:
"ReadReplicaDBInstanceIdentifiers": [
"abc-rr1",
"arn:aws:rds:us-west-2:12345678910:db:abc-rr-2"
],

2. For the above list when li to do "describe-db-instances" for each one "arn:aws:rds:us-west-2:868769925241:db:prod-pgsql-pdx-replica" it is resulting in below error which is consistent with what we are receiving from the application.
   ==============
   aws rds describe-db-instances --db-instance-identifier arn:aws:rds:us-west-2:868769925241:db:prod-pgsql-pdx-replica

An error occurred (InvalidParameterValue) when calling the DescribeDBInstances operation: The parameter Filter: db-instance-id is not a valid identifier. Identifiers must begin with a letter; must contain only ASCII letters, digits, and hyphens; and must not end with a hyphen or contain two consecutive hyphens.

Class: AmazonRdsDataSourceFactoryBean
method: createDataSourceInstance -> getDbInstance

DescribeDBInstancesResult describeDBInstancesResult = this.amazonRds.describeDBInstances((new DescribeDBInstancesRequest()).withDBInstanceIdentifier(identifier)); -> This results in failure as we are doing describe-db-instances with an arn.

Potential solution: getDbInstance method should identify if aws returned an ARN and convert the describe-db-instances to be following
aws rds describe-db-instances --db-instance-identifier abc-rr-2 --region us-west-2 instead of calling directly using ARN.

I am unable to open a PR as I am not sure where this module exists.

[virajkanwade]

For handling clusters -

can we add a flag
cloud.aws.employee-db.isCluster=true

And in https://github.com/spring-attic/spring-cloud-aws/blob/e9e7b8cb158013464caa792b3e2de9cf7179678a/spring-cloud-aws-jdbc/src/main/java/org/springframework/cloud/aws/jdbc/rds/AmazonRdsReadReplicaAwareDataSourceFactoryBean.java#L73

Replace getDBInstance with getDBClusterInstance which uses this.amazonRds.describeDBClusters to get the writer instance and getReadReplicaIdentifiers to get read replica identifiers and rest of the code remains the same?

[maciejwalkowiak]

@virajkanwade we do not plan anymore to include JDBC module in 3.0 version. It MAY be included again in the future, but we prefer to wait for AWS to stabilize work on their JDBC drivers that partially cover what Spring Cloud AWS 2.x offered.

[virajkanwade]

so whats the solution for people who want to leverage the auto failover of Aurora RDS? Implement and maintain the convoluted work around available? When there could just be a small patch in the current version?

[maciejwalkowiak]

AWS has implemented/is implementing failover capabilities on the driver level:

• for PostgreSQL: https://github.com/awslabs/aws-advanced-jdbc-wrapper
• for MySQL: https://github.com/awslabs/aws-mysql-jdbc

[areddy23]

Hi I am assuming spring-cloud-starter-aws-jdbc is a "io.awspring.cloud" version of "spring-cloud-aws-jdbc" ( 2.x.x version)

I feel this enhancement request should be backed in to 3.x.x if not already.

Steps to recreate:

1. Create a Primary RDS(abc) with Read Replicas in 2 diff regions us-east-1(abc-rr-1) and us-west-2(abc-rr-2). When you configure current "spring-cloud-aws-jdbc" to manage traffic to RR's it fails the reason is as follows.

In 2.x.x the library is attempting to "createInstance" and calls describe-db-instances which results below: (using cli to replicate the issue)

Class: AmazonRdsReadReplicaAwareDataSourceFactoryBean method: createInstance

1. "aws rds describe-db-instances --db-instance-identifier abc" Primary DB is yielding a list of RR associate with it across regions, please note it is returning DB Identifier for the RR in the same region and arn for the RR in diff region.

   aws rds describe-db-instances --db-instance-identifier prod-pgsql

From result:

"ReadReplicaDBInstanceIdentifiers": [
"abc-rr1",
"arn:aws:rds:us-west-2:12345678910:db:abc-rr-2"
],
2. For the above list when li to do "describe-db-instances" for each one "arn:aws:rds:us-west-2:868769925241:db:prod-pgsql-pdx-replica" it is resulting in below error which is consistent with what we are receiving from the application.

aws rds describe-db-instances --db-instance-identifier arn:aws:rds:us-west-2:868769925241:db:prod-pgsql-pdx-replica

An error occurred (InvalidParameterValue) when calling the DescribeDBInstances operation: The parameter Filter: db-instance-id is not a valid identifier. Identifiers must begin with a letter; must contain only ASCII letters, digits, and hyphens; and must not end with a hyphen or contain two consecutive hyphens.

Class: AmazonRdsDataSourceFactoryBean method: createDataSourceInstance -> getDbInstance

DescribeDBInstancesResult describeDBInstancesResult = this.amazonRds.describeDBInstances((new DescribeDBInstancesRequest()).withDBInstanceIdentifier(identifier)); -> This results in failure as we are doing describe-db-instances with an arn.

Potential solution: getDbInstance method should identify if aws returned an ARN and convert the describe-db-instances to be following

aws rds describe-db-instances --db-instance-identifier abc-rr-2 --region us-west-2 instead of calling directly using ARN.
I am unable to open a PR as I am not sure where this module exists.

@maciejwalkowiak any recommendation on this ?

[maciejwalkowiak]

The recommendation is to use one of the solutions linked above as we are not adding JDBC module to 3.0 yet.

[areddy23]

The recommendation is to use one of the solutions linked above as we are not adding JDBC module to 3.0 yet.

@maciejwalkowiak I am not sure if you read my summary, this is not entirely related to Fail over management which can be acheived by your suggestion. The above issue is with leveraging read replicas for an RDS that are cross regional(like we do using spring-cloud-aws-jdbc - @transaction annotation), I don't see how https://github.com/awslabs/aws-advanced-jdbc-wrapper can achieve that. Please let me know if I am missing anything ?

[maciejwalkowiak]

Oh apologies, I missed that I did not add 3rd link there.

Follow this approach for read & write replica.

https://vladmihalcea.com/read-write-read-only-transaction-routing-spring/

Vlad explained approach that we internally do in Spring Cloud AWS 2.x

[virajkanwade]

For anyone interested in keeping it simple and wishing to use the spring-cloud-aws-jdbc:

virajkanwade/spring-attic-spring-cloud-aws@d0ed817

Also a compiled jar available. https://github.com/virajkanwade/spring-attic-spring-cloud-aws/releases/tag/v2.2.7.RELEASE

You can use local maven repo for using the jar.

NOTE:
https://github.com/awslabs/aws-advanced-jdbc-wrapper was released very recently on 6th Oct 2022. It still has open issues related to transactions. Also, it currently only manages auto-failover. It still does not support read replica. So I personally feel its atleast months away from being useful.

The vladmihalcea approach is good. But it uses a custom JPA configuration file https://github.com/vladmihalcea/high-performance-java-persistence/blob/c75c3df14daf043d4014307e07efac4b5129739c/core/src/test/java/com/vladmihalcea/book/hpjp/util/spring/config/jpa/AbstractJPAConfiguration.java. It seems to hardcode additionalProperties. We would need additional logic to parse spring application.properties to dynamically configure these options.

[virajkanwade]

Submitted PR in correct repo
#534

[ghost]

Regarding

should we support RDS proxy?

Yes please!
Is there a temporary solution for using a proxy right now?

[maciejwalkowiak]

@roelmang you should be able to connect to RDS Proxy without Spring Cloud AWS? Perhaps you can explain what bits are problematic.

[ghost]

@roelmang you should be able to connect to RDS Proxy without Spring Cloud AWS? Perhaps you can explain what bits are problematic.
@maciejwalkowiak

I was at first looking into overriding some beans from the library to fetch the rds proxy details, but opted for not using the library at all for datasource configuration.
No issues configuring the datasource directly instead of using Spring Cloud AWS to autoconfigure it.
Just would have been nice to be able to just replace the dbInstanceIdentifier instead of configuring the rds proxy jdbc url.

[zeagord]

@maciejwalkowiak - Are you planning to support IAM based auth for the RDS? I will be happy to support and implement if the work for 3.0 release has been started.

[maciejwalkowiak]

@zeagord there is a project from AWS that covers IAM auth: https://github.com/awslabs/aws-advanced-jdbc-wrapper

Or maybe you have something different in mind?

[gnagy]

Hi, any plans for r2dbc support?

[maciejwalkowiak]

Hi @gnagy. No. Perhaps you can ask AWS folks if they have any plans: https://github.com/awslabs/aws-advanced-jdbc-wrapper

[avivunitq]

@roelmang you should be able to connect to RDS Proxy without Spring Cloud AWS? Perhaps you can explain what bits are problematic.

native read-replica support with spring boot + rds proxy would be useful.

[serhiikartashov]

Hi folks! Have you made any progress with this task?