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

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.