When I go through the documents, using session manager we can connect instance in private subnet without having bastion host itself [direct port forwarding from local to private ec2].
But in RDS case, even though we are making connection using session manager we need a EC2 instance in between local and private RDS.
Could you anyone explain me why it is like that? please share some document that explains that as well.
AWS Systems Manager Session Manager allows you to connect to an instance in a Private Subnet because the instance is actually running an 'SSM Agent'. This piece of code creates an outbound connection to the AWS Systems Manager service.
Then, when you request a connection to the instance, your computer connects to the AWS Systems Manager service, which forwards the request to the agent on the instance. The AWS Systems Manager service is effectively acting as a Bastion for your connection.
AWS Systems Manager Session Manager cannot provide a connection to an Amazon RDS server because there is no ability to 'login' to an Amazon RDS server. Given that your RDS server is running in a Private Subnet, it is therefore necessary to port-forward via an EC2 instance in the same VPC as the RDS server. This can be done via a traditional Bastion EC2 instance in a Public Subnet, or via an EC2 instance in a Private Subnet by taking advantage of the Port Forwarding capabilities of AWS Systems Manager Session Manager.
I have got answered the same question in the AWS repost by @Uwe K. Please refer below.
SSM allows many more functions - and changes! - to an instance then just connecting to it. Having full SSM functionality on an RDS instance thus would undermine the Shared Responsibility Model we use for RDS (you could also say: it would violate the "Black Box" principle of RDS). Therefore, you need an intermediary instance that forwards the TCP Port exposed by RDS to your local machine.
Further reading:
In order to connect to any EC2 instance with AWS systems manager, the SSM agent must be installed on that machine and the appropriate permissions need to be set up for the instance.
At the moment, AWS does not support this to RDS directly. In order for them to support such a setup, they'd probably need to install the agent on all RDS instances which generates quite some overhead and who knows what else the complexities of such a setup would have.
So at the present moment the most effective way to connect is setting up a tunnel via an EC2 instance.
