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

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.