can AWS Lambda connect to RDS mySQL database and update the database?

Question

I am trying to connect AWS Lambda function to RDS mysql database.
I just wanted to update the database from my lambda function. Is it possible to access RDS by specifiying IAM Role and access Policy?.
I can connect to mysql databse using mysql client.but when i try on lambda i can't do that. here is my code.

console.log('Loading function');
var doc = require('dynamodb-doc');
var dynamo = new doc.DynamoDB();
var mysql = require('mysql');
exports.handler = function(event, context) {
    //console.log('Received event:', JSON.stringify(event, null, 2));  
    var operation = event.operation;
    delete event.operation;
    switch (operation) {
        case 'create':
            var conn = mysql.createConnection({
                host: 'lamdatest.********.rds.amazonaws.com', // RDS endpoint 
                user: 'user', // MySQL username 
                password: 'password', // MySQL password 
                database: 'rdslamda'
            });
            conn.connect();
            console.log("connecting...");
            conn.query('INSERT INTO login (name,password) VALUES("use6","password6")', function(err, info) {
                console.log("insert: " + info.msg + " /err: " + err);
            });
            console.log("insert values in to database");
            break;
        case 'read':
            dynamo.getItem(event, context.done());
            break;

        default:
            context.fail(new Error('Unrecognized operation "' + operation + '"'));

    }
    context.succeed();
};

Solution 1

Yes. You can access a MySql RDS database from AWS Lambda.

You can use node-mysql library.

• Link: https://github.com/felixge/node-mysql/

However, there is a big caveat that goes with it.

AWS Lambda does not (currently) have access to private subnets inside a VPC. So in order for AWS Lambda to access your RDS database, it must be publicly accessible, which could be a security risk for you.

Update (2015-10-30): AWS Lambda announced upcoming VPC support (as of re:Invent 2015), so this won't be an issue for much longer.

Update (2015-11-17): AWS Lambda still does not have VPC support.

Update (2016-02-11): AWS Lambda can now access VPC resources:

https://aws.amazon.com/blogs/aws/new-access-resources-in-a-vpc-from-your-lambda-functions/

To achieve this functionality, your Lambda function will actually execute inside your VPC in a subnet. Some caveats come with this functionality:

• The VPC subnet needs enough free IP addresses to handle Lambda's scaling
• If your Lambda function needs internet access, then it's designated VPC subnet will need an Internet Gateway or NAT

Solution 2

try this tutorial: http://docs.aws.amazon.com/lambda/latest/dg/vpc-rds.html

In this tutorial, you do the following:

Launch an Amazon RDS MySQL database engine instance in your default Amazon VPC.

In the MySQL instance, you create a database (ExampleDB) with a sample table (Employee) in it.

Create a Lambda function to access the ExampleDB database, create a table (Employee), add a few records, and retrieve the records from the table.

Invoke the Lambda function manually and verify the query results.

Solution 3

Since Lambda uses Node.js, Java and Python as a backend programming/scripting language, you can definitely use it to connect to RDS. (Link)

Finally, This is the documentation on specifying IAM Roles when connecting to RDS. (See image below):

Solution 4

I just wanted to update the database from my lambda function. Is it possible to access RDS by specifiying IAM Role and access Policy?.

No you cannot. You need to provide DB url/username/password to connect. You may need to run Lambda in same VPC if it is in private subnet. See my pointers below.

I can connect to mysql databse using mysql client.but when i try on lambda i can't do that.

This is strict No , No! Your RDS should not be accessible from Internet unless you really need it. Try to run it in private subnet and configure other AWS services accordingly.

Two cents from my end if you are getting timeouts accessing resourced from Lambda-

1. By default Lambda has internet access and can access online resources.
2. Lambda cannot access services rurnning in private subnet of your VPC.
3. To connect to services in private subnet you need to run the lambda is private subnet. For this you need to go to Network section and configure your VPC, subnets and security group.
4. However note that when you do this you will loose Internet access. If you still need Internet access you will have to spin up a NAT gateway or NAT instance in public subnet and configure route from private subnet to this NAT.
5. I faced this when I was trying to connect to RDS in private subnet from my lambda. Since I used KMS to encrypt some environment variables and decryption part requires Internet access I had to use a NAT gateway.

More details - http://docs.aws.amazon.com/lambda/latest/dg/vpc.html#vpc-internet

How to connect to postgres RDS from AWS Lambda

PS: Above links go to my personal blog that has additional relevant information.

View more solutions

Comments

[adamkonrad]

You can't use IAM Role to access your RDS.

[ARUNBALAN NV]

so what is the procedure to connect to RDS mysql?

[adamkonrad]

You need to make your RDS public (not in VPC) and then use one of it's public hostnames to connect to it.

[ARUNBALAN NV]

okay. now i am trying to do so.but there is an error shows when i try to include mysql module in my lambda function. like this : var mysql= require("mysql"); . It show error like "errorMessage": "Cannot find module 'mysql'

[ARUNBALAN NV]

okay. now i am trying to do so.but it shows an error when i try to include mysql module in my lambda function. like this : var mysql= require("mysql"); . It show error like "errorMessage": "Cannot find module 'mysql'

[Matt Houser]

Then you're not including the module in your deployment package correctly. Make sure you're including it in your ZIP package (in the node_modules sub-folder).

[ARUNBALAN NV]

i included the zip folder which contains my index.js file and node_modules folder. but it shows error message like below { "errorMessage": "Cannot find module 'index'", "errorType": "Error" etc..

[ARUNBALAN NV]

okay.. the above described issue solved ( errorMessage "cannot find module 'index'). because i made the zip of the parent folder instead of index.js file and node-module. that was the problem. Now i need to know ,do i wanted to setup any additional configurations like IAM role or policy?

[adamkonrad]

It's incorrect to point to IAM Roles for RDS. RDS connection from Lambda is not inside a VPC. You need to setup your RDS as public and use regular MySQL credentials to connect to it.

[Jordan]

I just pointed out the documentation. The downvote was definitely not needed here. Also, the original post had nothing to do with a VPC.

[Matt Houser]

If your Lambda function is only accessing your MySQL database, then you don't need any IAM permissions other than the usual execution policy: docs.aws.amazon.com/lambda/latest/dg/…

[ARUNBALAN NV]

okay.thanks. But when i running the lambda function it can't connect to my RDS mysql database. I am using the security group rule like this. Type: MYSQL/Aurora, Protocol:TCP, PortRange:3306, Source: 0.0.0.0/0 .

[ARUNBALAN NV]

okay.thanks. But when i running the lambda function it can't connect to my RDS mysql database. I am using the security group rule like this. Type: MYSQL/Aurora, Protocol:TCP, PortRange:3306, Source: 0.0.0.0/0 .

[Matt Houser]

That is not an IAM issue. Make sure your RDS instance is publicly accessible and you can access it from your own local computer.

[ARUNBALAN NV]

i can access it from my local machine.it's working perfectly.

[ARUNBALAN NV]

This is the errro.error connecting: Error: connect ETIMEDOUT at Connection._handleConnectTimeout (/var/task/node_modules/mysql/lib/Connection.js:373:13)

[jugutier]

Why is this a security risk?, assuming the connection to the db is over https and password protected, even if the port is publicly accessible there is some level of security

[Matt Houser]

Even though it's protected by password, it's still open to possible abuse to mysql exploits, DoS, etc. Preventing open connections is more secure. It all depends on the data that you're holding. eg. Even if password protected, financial institutions would never have a server open to the world.

[jugutier]

Thanks @MattHouser I'm evaluating an analytics server running lambda and storing in Cassandra db. But I guess I'll have my own AWS instance running node.js

[Katharine Osborne]

Is this compatible with Amazon Aurora?

[blahblah]

I believe your first answer is no longer truly correct - aws.amazon.com/premiumsupport/knowledge-center/…

[AnBisw]

This answer is outdated.