Skip to content

Instantly share code, notes, and snippets.

@sators sators/connect.php
Last active Feb 25, 2019

Embed
What would you like to do?
PHP MySQLi Amazon Aurora RDS EC2 IAM Role Based Authentication
<?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>';
[client]
enable-cleartext-plugin
@sators

This comment has been minimized.

Copy link
Owner Author

commented Oct 13, 2017

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.
@mvanbaak

This comment has been minimized.

Copy link

commented Oct 13, 2017

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

@neilmcgibbon

This comment has been minimized.

Copy link

commented Nov 30, 2017

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.

@sators

This comment has been minimized.

Copy link
Owner Author

commented Dec 7, 2017

Good find @neilmcgibbon - updated, thanks!

@kaihendry

This comment has been minimized.

Copy link

commented Feb 8, 2019

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.