The goal of this post is to show how to Create Database and manage IAM to have access to database from EC2
IAM DB Authentication allows Developers and DBA to connect to RDS instances passwordlessly also this provide an additional layer of security because Authentication token is only available for 15 Minutes
To Setup we need to follow some steps : this work for Postgres and mysql database and you should check the compatible version from AWS
- Launch RDS with traditional method the only thing we need to enable is IAM DB Authentication. You can also Modify your DB instance and enable this setting if you have existing database.
Once you created the database ,let launch the Ec2.
2. Create a new IAM Role “ngpec2role” with main policy as AmazonRDSFullAccess and following inline policy
Note: Change the attributes within Resources option.(RessourceId is your database instance resourceId, awsAccountNumber,region..)
{
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Action": [
"rds-db:connect"
],
"Resource": [
"arn:aws:rds-db:region:account-id:dbuser:dbi-resource-id/database-user-name"
]
}
]
}
- 3. Launch an Amazon Windows EC2 Instance. It will be used to host the dotnet Core application or connect to Postgres database. For our demo we are using windows server 2019. The step s also available for Linux. Select the IAM role ngpec2role
*Attach this new IAM role “ngpec2role” with your Amazon EC2 Instance if you already have an Ec2 runing by going to Modify Role from the Action dropown,when selecting the Instance from Ec2 dashboard
4. Create a database user account with no stored password, but instead accept authentication token in its. This may be confusing for you. It is just for you to create a login User (role) in Pgadmin and give it the role of RDS_IAM:
RDP to Ec2
Install Postgres with default pgadmin
CREATE USER dbuser;
GRANT rds_iam TO dbuser;
At this point you can Test this Using PGadmin
5. Run the AWS command to generate a token
C:>aws rds generate-db-auth-token –hostname database-1-instance-1.cnebndbxspk.us-east-2.rds.amazonaws.com –port 5432 –region us-east-2 –username dbuser
Replace the hostname with your RdS database host and dbuser with your database login you created.
Copy the output and connect to PGAdmin with the dbuser and token.
ET voila. Works
6. Deploy your application In IIs and you should be able to run you .net MvC app.
The source code to deploy can be found here Source
https://github.com/davidzongo/AuroraPostgreSql_Aws_IamAuthentication_DotNet6_MVCCore_Demo
In case your user does not have permission please GRANT rds_superuser TO dbuser
If you have a better way , please do not hesitate. I am here to listen and learn from you.
Update march 15, 2022
As for today this method work with .netcore EntityFramework. I have tried to use nhibernate to accomplish the same goal, but no luck so far.