MySQL and PostgreSQL
Amazon’s AWS Identity and Access Management (IAM) database authentication tool can authenticate your DB instance. The process works with MySQL and PostgreSQL, and by using this type of authentication, you do not need passwords for connecting to any instance of a DB. Instead, you will be using an authentication token shared and managed by AWS.
What is an authentication token?
Upon request, Amazon’s RDS generates a string of unique characters/Hash values to make up an authentication token, the lifetime of which is limited to 15 minutes. User credentials are not required to be stored on the database, as authentication will be managed externally using IAM. Authentication tokens are generated by using AWS Signature Version 4.
IAM DB authentication benefits:
- SSL Encryption: A Secure Sockets Layer (SSL) encrypts all the traffic going to and coming from the RDS database.
- EC2 specific profiles: Profile credentials unique to your EC2 instances both power applications run on that platform and provide secure access to the database.
- Central managed DB access by IAM: The programming also manages all your database resources, eliminating the necessity of personally accessing each and every instance.
IAM Database Authentication: Disabling and Enabling
On the RDS databases, IAM database authentication is automatically disabled by default. Re-enabling it requires the use of the AWS Management Console, the API or by AWS CLI.
Enabling the IAM authentication for RDS PostgreSQL DB instances requires setting the SSL value to 1; it won’t work when that setting is at 0. Further, any changes will prevent resetting the SSL value back to 0 when a PostgreSQL instance is enabled by IAM authentication.
For an existing DB instance, you can enable or disable the IAM authentication by following these steps:
1. Login to the AWS console
2. Open the Amazon RDS console.
3. Choose the navigation pane and click on the Databases.
4. Choose the RDS DB instance to be changed.
5. Choose Modify.
6. For IAM DB authentication in the database options, choose: “Enable” IAM DB authentication or “Disable.”
Then, click on Continue
7. On making the changes to apply these changes immediately, choose Apply immediately.
8. Select “Modify DB instance.”
Create Policies to Govern IAM Policy for IAM Database Access
To track any IAM user or role that is connected to your RDS DB instances, you should generate a governing IAM policy and ensure that it is attached to that IAM user or role.
Effect – Specifies ‘Allow’ to grant access to RDS DB instance. If not explicitly specified as an ‘allow’ access, then the access to RDS DB is denied by default.
Action – Specify rds-db:connect to allow the connections to the DB instance.
Resource – Select one database account/username used in this instance of the RDS DB that describes an Amazon Resource Name (ARN).
Once the IAM policy is generated to allow IAM database authentication, be sure to attach it to the IAM user or role.
IAM Authentication Also Creates Database Accounts
Once you’ve created user accounts in the RDS, the IAM database authentication ensures that you don’t need to assign passwords to those accounts, too. Removing users is a two-step process: first, remove them from the IAM and then use the DROP USER statement to also remove them from the database account.
PostgreSQL and IAM Authentication
PostgreSQL allows you to use IAM authentication by connecting to the RDS DB instance and both creating and granting access to users in the rds_iam role.
CREATE USER db_user WITH LOGIN;
Then rds_iam TO db_user grants access.
MySQL and IAM Authentication
AWSAuthenticationPlugin handles authentication for MySQL.
CREATE your USER db_user to be IDENTIFIED WITH AWSAuthenticationPlugin AS ‘RDS’;
MySQL uses the phrase, ‘IDENTIFIED WITH’ to access the AWSAuthenticationPlugin for authentication of the database account (db_user).
Using IAM Authentication to Connect With to Your DB Instance
With RDS’s IAM database authentication, connecting to the RDS DB instance also generates an authentication token, a string of characters/hash value, to use instead of a password. Its life cycle is also only valid for 15 minutes and expired token connections will be denied.
You can also use the authentication token to Amazon RDS from other AWS services such as AWS Lambda. By using the authentication tokens, you are avoiding writing passwords into your code.
How to Generate an IAM Authentication Token
Example showing how to use the AWS CLI to get a signed authentication token:
aws rds generate-db-auth-token \
--hostname rdsmysql. QWERTYUIOP.us-west-1.rds.amazonaws.com \
--port 3306 \
--region us-west-1 \
--hostname – The hostname of the RDS DB instances.
--port – port number used for connecting to RDS DB instance.
--region – The AWS Region (DB instance is running region).
--username – The database access account name.
Limitations for IAM Database Authentication with MySQL
There are only 256 new connections per second permitted between MySQL RDS and IAM authentication, which drops to 10 connections per second if using a db.t2.micro instance.
Restrictions for Using PostgreSQL and IAM Database Authentication
The instance type and volume of workload on the RDS database will limit the maximum number of connections per second to the RDS database instances available to you.
Subscribe to Our Blog
Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.
The “ORA-12154: TNS:could not resolve the connect identifier specified” Oracle error is a commonly seen message for database administrators.
This blog reviews how you can generate scripts for SQL server logins, role assignments, and server permissions for a smooth migration.