PHP MySQLi Amazon Aurora RDS EC2 IAM Role Based Authentication

connect.php

<?php

/********* CONFIG ********/
$clusterEndpoint = "";
$clusterPort = 3306;
$clusterRegion = "us-east-1";
$dbUsername = "";
$dbDatabase = "";
/*************************/

// AWS-PHP-SDK installed via Composer
require 'vendor/autoload.php';

use Aws\Credentials\CredentialProvider;

$provider = CredentialProvider::defaultProvider();
$RdsAuthGenerator = new Aws\Rds\AuthTokenGenerator($provider);

$token = $RdsAuthGenerator->createToken($clusterEndpoint . ":" . $clusterPort, $clusterRegion, $dbUsername);

$mysqli = mysqli_init();

mysqli_options($mysqli, MYSQLI_READ_DEFAULT_FILE, "./my.cnf");

$mysqli->real_connect($clusterEndpoint, $dbUsername, $token, $dbDatabase, $clusterPort, NULL, MYSQLI_CLIENT_SSL);

if ($mysqli->connect_errno) {
    echo "Error: Failed to make a MySQL connection, here is why: <br />";
    echo "Errno: " . $mysqli->connect_errno . "<br />";
    echo "Error: " . $mysqli->connect_error . "<br />";
    exit;
}

/***** Example code to perform a query and return all tables in the DB *****/
$tableList = array();
$res = mysqli_query($mysqli,"SHOW TABLES");
while($cRow = mysqli_fetch_array($res))
{
    $tableList[] = $cRow[0];
}
echo '<pre>';
print_r($tableList);
echo '</pre>';

my.cnf

[client]
enable-cleartext-plugin

Follow the instructions on IAM Database Authentication for MySQL and Amazon Aurora to setup your database and IAM policies. This guide ends with Java examples, so complete the tutorial thru "Attaching an IAM Policy Account to an IAM User or Role".

Then, associate the newly created role to your EC2 instance, update the config at the top of the above PHP file and try connecting!

This took many hours of frustration with some notes of learning along the way:

• SSL is required for the client to connect and authenticate via IAM, however the password is sent in clear text...this is why the custom my.cnf file is necessary in order to enable cleartext passwords.
• You must create your database users using the SQL syntax CREATE USER iam_user IDENTIFIED WITH AWSAuthenticationPlugin as 'RDS'; as exampled here. This creates a database user called "iam_user" that can be authenticated via IAM. *Note you can replace "iam_user" with anything you wish. Don't confuse this with an actual IAM User - they are not related at all.
• Once a DB user has been created, you define that database username as the resource that the IAM policy is granting access to in the following format: arn:aws:rds-db:<region>:<account-id>:dbuser:<dbi-resource-id>/<database-user-name>. as exampled here. The dbi-resource-id can be found on the details page of your Aurora cluster.
• Nowhere in the official IAM documentation other than the IAM Database Authentication for MySQL and Amazon Aurora will you see reference to rds-db:connect or anything rds-db. Furthermore, the IAM Policy Summary will display this error: "This policy does not grant any permissions. To grant access, policies must have an action that has an applicable resource or condition." The page will also list rds-db as an unrecognized service. The policy still works regardless of these notices.

Thank you for creating this gist with clear documentation of your experience. Helped a lot!

Superb gist, really helpful - thanks.

One issue for me was that my PHP build was not built with the original mysql driver, but only the mysqli driver (which this gist uses). It meant that the constant MYSQL_CLIENT_SSL had to be changed to MYSQLI_CLIENT_SSL in the real_connect() method in order for this to work for me.

Good find @neilmcgibbon - updated, thanks!

Has anyone nailed this down in the form of a Dockerfile?

I managed to logging using real_connect but I am having issue by using PDO is any work around?
RDS responds Access denied for user 'iam_user'@'xxx.xxx.xx' (using password: YES)

@KikeMendez, I am having the same issue. Did you figure out what is needed for PDO?

When I was getting Access denied for user 'iam_user'@'xxx.xxx.xx' (using password: YES) and everything else seemed correct, it was because my EC2 Instance Profile did not have the rds-db:connect. I could not figure out how to use just the DB resource in question, but giving the Instance Profile role the following won the day:

- PolicyName: rds-connect PolicyDocument: Version: "2012-10-17" Statement: - Action: - rds-db:connect Effect: Allow Resource: "*"

@tomhuang12 @KikeMendez Did you guys figure this one out? I was able to connect using the method above, but I can't get it working with PDO.

@sators
There are a few challenges i've run into with creating new connections with the token. Creating new connections requires more CPU than an ordinary MySQL connection and there is a limit to the number of new RDS IAM connections which can be created per second.
Ideally, we want to re-use the token until it expires (+- 20 minutes) or at least keep re-using it for a fixed amount of time in order to not create a new connection with every request. Any idea how to do this? Do you need to create a cache?

@jonathanviber Interesting - yes, I suppose you would need some sort of server-side cache to cache those connection details...however certainly you would need additional logic to ensure the token is still valid upon attempting to use it.

@mdubinsky I know this is an old thread, but this is for anyone else who finds this through a search. This was how I was able to make PDO happy:

$pdo = new PDO(
    sprintf('mysql:host=%s;port=3306', $host),
    'iam_username',
    'password',
    [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::MYSQL_ATTR_SSL_CA => '/tmp/rds-combined-ca-bundle.pem',
        PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false,
    ]
)

If you would like to use doctrine/dbal for instance, this will work:

$args = [
    'params' => [
        'host' => $host,
        'user' => $username,
        'dbname' => $dbname,
        'port' => $port,
        'password' => $password,
        'driverOptions' => [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false,
        ],
    ],
    'driver' => new \Doctrine\DBAL\Driver\PDO\MySQL\Driver(),
];

$queryBuilder = new \Doctrine\DBAL\Query\QueryBuilder(
    new \Doctrine\DBAL\Connection(...$args)
);

Hey everyone,

I'm having trouble connecting my PHP script to an AWS RDS MySQL database using PDO. Despite double-checking credentials and permissions, I keep getting an "access denied" error.

I've attached the script for reference. Any ideas?
Maybe @kevinquinnyo can help ??

function generateRdsIamAuthToken(string $host, string $region): ?string
{
    try{
        // Create an SDK class used to share conofiguration across clients.
        $provider = CredentialProvider::defaultProvider();
        $creds = $provider()->wait();
        $authTokenGenerator = new AuthTokenGenerator($creds);
        $token = $authTokenGenerator->createToken($host, $region, "testuser", 10);

        return $token;
    } catch (AwsException $e) {
        // Handle potential errors during token generation (e.g., invalid credentials, network issues)
        error_log("Error generating RDS IAM authentication token: " . $e->getMessage());
        return null;
    }
}

// Example usage:

try {
    $host = 'XXX.eu-west-1.rds.amazonaws.com:3306';
    $region = 'eu-west-1';
    $token = generateRdsIamAuthToken($host, $region);
    echo $token;
    $pdo = new PDO(
        sprintf('mysql:host=%s;port=3306;dbname=<dbname>', $host),
        "<dbuser>",
        $token,
        [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false,
    ]
    );
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected to the database successfully!";
    }

@talhermon could it be the "testuser" hardcoded in the generateRdsIamAuthToken function?

@talhermon could it be the "testuser" hardcoded in the generateRdsIamAuthToken function?

Its the name of the user, I'm using the same name when calling the function.