AWS RDS SSL/TLS Certificate Upgrade
It is best to start the certificate upgrade process by first testing it on a copy of the database to ensure that if there is an issue it will not effect live users.
The creation of a copy of the RDS database and Upgrade for the certificate process for the database on AWS is as follows:
Step 1. Overview: Download the root certificate and move it to your application and then setup a script on your live server to test the connection to a test instance of the RDS DB without effecting end users. (Instructions for this section are for php but you could use other languages to achieve the same thing.)
Download the root cert that works for all AWS Regions from https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.SSL.html
Under the heading Using SSL/TLS to Encrypt a Connection to a DB Instance click on the link https://s3.amazonaws.com/rds-downloads/rds-ca-2019-root.pem (Only Download the Intermediate certificate for the region where the servers are located if the previous chain certificate does not work. Make sure to choose from the column with the newest cert.)
Use filezilla or some other method of moving the certificate to the development server. On one server we moved the certificate to /etc/ssl/certs/ and on another we moved it to /data/webs/[base file for site]/certs/.
(The following instructions were for a server using opsworks where we have dynamic scripts setup to create files on the instances through a deployment to a specific stack. If you are not using opsworks you could just move the file strait to your production server in the correct location and setup the permissions manually.)
OPSWorks Server: Modify the build scrip for the apache recipe to grab the version of the file on the development server, recreate it, and put it on the aws server in the right location.
We added a section to the file like the following:
file ‘/etc/ssl/certs/rds-ca-2019-root.pem’ do
content <<-EOH<?=file_get_contents(“/etc/ssl/certs/rds-ca-2019-root.pem”)?>
EOH
mode ‘0644’
owner ‘root’
group ‘root’
end
Run the build script from the custom developer opsworks area and verify the file shows up in the body of the apabche.rb recipe. Then deploy the recipe so it will be ran on the production stack on aws and create the file in the correct location. Go on one of the instances on the production stack and verify the file has been created.
(End of OPSWORKS Server specific section.)
Modify your database connection for your site (ours was in a specific function controlling the connection and making it available to the rest of our pages) to change the connection string for a certificate requirement.
Mysql database specific section:
For mysql databases we modified the line:
$dblink = mysqli_connect($server, $user, $pass);
To instead use:
$certpath=trim($_SERVER[‘DOCUMENT_ROOT’].’/certs/rds-ca-2019-root.pem’);
$dblink = mysqli_init();
$dblink->options(MYSQLI_OPT_SSL_VERIFY_SERVER_CERT, true);
$dblink->ssl_set(NULL, NULL, $certpath, NULL, NULL);
$dblink->real_connect($server, $user, $pass, $name);
We first left this new section of code commented out and modified our db_connect_single function so we could include a global variable testcertrequirements that was an array containing certpath and host. If this global variable was set we would use the data to override the default connection data so we could test the connection with a developer user without causing issues for our other users on the system. This is a snippet of the code for the connection function: (d function calls just display info for developers)
function db_connect_single($name, $user, $pass, $server='')
{
global $testcertrequirements;
if($_GET[debug]||$_GET[db_connect_debug])
d("db_connect_single($name, $user, $pass, $server='')");
if(!$server)
$server='localhost';
if (defined("ENVIRONMENT_CURRENT"))
$currentenv=ENVIRONMENT_CURRENT;
if (
(strtolower($currentenv)=='prod')
&& $testcertrequirements
&& is_array($testcertrequirements)
&& trim($testcertrequirements['host'])
&& trim($testcertrequirements['certpath'])
&& file_exists(trim($testcertrequirements['certpath']))
) //Method for testing new certificate requirements without effecting current users on AWS.
{
$server=trim($testcertrequirements['host']); //AWS end point for new test db instance spun up from snapshot in RDS and that is setup with the new certificate.
d('$testcertrequirements[certpath]',trim($testcertrequirements['certpath']));
d('$server',$server);
$dblink = mysqli_init();
$dblink->options(MYSQLI_OPT_SSL_VERIFY_SERVER_CERT, true);
$dblink->ssl_set(NULL, NULL, trim($testcertrequirements['certpath']), NULL, NULL);
$dblink->real_connect($server, $user, $pass, $name);
}
/*
elseif(strtolower($currentenv)=='prod') //Default on aws is to use new cert since db updated to new cert.
{
$certpath=trim($_SERVER['DOCUMENT_ROOT'].'/certs/rds-ca-2019-root.pem');
$dblink = mysqli_init();
$dblink->options(MYSQLI_OPT_SSL_VERIFY_SERVER_CERT, true);
$dblink->ssl_set(NULL, NULL, $certpath, NULL, NULL);
$dblink->real_connect($server, $user, $pass, $name);
}
*/
else
{
if($testcertrequirements)
{
d('<span style="color:red">DID NOT USE TEST CERT REQUIREMENT FOR CONNECTION</span>');
d('using default connection and not dynamic one.');
d('$currentenv',$currentenv);
d('connecting using server:'.$server.' and not test certificate requirement');
d('$testcertrequirements',$testcertrequirements);
if(!file_exists(trim($testcertrequirements['certpath'])))
d('Missing cert file:',trim($testcertrequirements['certpath']));
}
$dblink = mysqli_connect($server, $user, $pass);
}(End Mysql specific section)
Postgresql database specific section:
For postgresql databases we modified the lines:
$con_str = “host=$hostname port=$port dbname=$dbname user=$user password=”.$password;
timetrack(db_connect, $con_str);
$dbconn = pg_connect($con_str);
To instead use:
$addcertrequirement=””;
if(is_aws())
$addcertrequirement=” sslmode=’verify-full’ sslrootcert=’/etc/ssl/certs/rds-ca-2019-root.pem'”;
$con_str = “host=$hostname port=$port dbname=$dbname user=$user password=”.$password.$addcertrequirement;
$dbconn = pg_connect($con_str);
We first left this new section of code commented out and modified our db_connect function so we could include a global variable testcertrequirements that was an array containing certpath and host. If this global variable was set we would use the data to override the default connection data so we could test the connection with a developer user without causing issues for our other users on the system. This is a snippet of the code for the connection function: (d function calls just display info for developers)
global $testcertrequirements;
$addcertrequirement="";
/*
if(is_aws())
$addcertrequirement=" sslmode='verify-full' sslrootcert='/etc/ssl/certs/rds-ca-2019-root.pem'";
*/
if($testcertrequirements && is_array($testcertrequirements) && trim($testcertrequirements['host']) && trim($testcertrequirements['certpath'])) //Method for testing new certificate requirements without effecting current users on AWS.
{
d('$testcertrequirements',$testcertrequirements);
$addcertrequirement=" sslmode='verify-full' sslrootcert='".$testcertrequirements['certpath']."'";
if(!is_aws())
$addcertrequirement=" sslmode='prefer' sslrootcert='".$testcertrequirements['certpath']."'";
else
$hostname=trim($testcertrequirements['host']); //AWS end point for new test db instance spun up from snapshot in RDS and tht is setup with the new certificate.
d('$addcertrequirement',$addcertrequirement);
d('$hostname',$hostname);
}
//decide which server to connect to based on the environment
$con_str = "host=$hostname port=$port dbname=$dbname user=$user password=".$password.$addcertrequirement;
timetrack(db_connect, $con_str);
$dbconn = pg_connect($con_str); //@pg_connect($con_str); use the @ to fix any errors(End Postgresql specific section)
After setting up the database connection functions created a file to be able to setup and test the connection as a specific user on production without effecting all the other users on the system. Code snippet for our test file:
global $testcertrequirements;
$testcertrequirements=array();
if($_POST['testconnection'])
{
if(!trim($_POST['host']))
set_message('A hostname is requried to test the connection','error');
if(!trim($_POST['certpath']))
set_message('A path including the filename to the new certificate is required to test the connection with the new certificate','error');
if(!has_message('error'))
{
$testcertrequirements['host']=$_POST['host'];
$testcertrequirements['certpath']=$_POST['certpath'];
d('$testcertrequirements',$testcertrequirements);
db_connect();
$sql="[ADD AN SQL SELECT STATEMENT HERE FOR A CORE DATABASE TABLE THAT CONTAINS INFORMATION IN YOUR DATABASE]
";
$logingcheckqry=db_query($sql);
d('$logingcheck sql',$sql);
d('$logingcheckqry',$logingcheckqry);
if($logingcheckqry)
set_message('Connection appears to be successful','success');
}
$testcertrequirements=array();
db_connect(); //Changing back connection to default for sql in footer.
}
display_messages('error');
$defaulttesthost=config_var('dbserver'); //Testing db instance endpoint already setup for new certificate.
if($defaulttesthost && !trim($_POST['host']))
$_POST['host']=$defaulttesthost;
$defaulttestcertpath='/etc/ssl/certs/rds-ca-2019-root.pem';
if($defaulttestcertpath && !trim($_POST['certpath']))
$_POST['certpath']=$defaulttestcertpath;
d('post',$_POST);
?>
<h3>
Test New DB Certificate for SSL
</h3>
<form id="testnewcertconnection" method="POST" enctype="multipart/form-data">
<table style='width:25%'>
<tr>
<td>
Host
</td>
<td>
<input type='text' name='host' id='host' value='<?=$_POST['host']?>' />
</td>
</tr>
<tr class='bgmint'>
<td>
Certificate Path
</td>
<td>
<input style='width:95%' type='text' name='certpath' id='certpath' value='<?=$_POST['certpath']?>' />
</td>
</tr>
<tr>
<td colspan=2>
<input type="submit" value="Test Connection" name="testconnection">
</td>
</tr>
</table>
</form>
<?
include_once('footer.php');Next migrate this code for the database connection and run the test file on the production site with the existing production db to verify you can select data from the database. This will showed that the test file and the connection is setup correctly.
Step 2. Overview: Create a new instance of the existing database from a snapshot to test the upgrade process so you can verify it works on a clone of the existing database with the same data.
Start by logging into the AWS Console then clicking on Services>Database> RDS>Snapshots.
Choose the newest snapshot or create a new snapshot of the database and choose it. Copy the KMS key ID from the snapshot details page and also make a note of the DB Storage. Click Actions> Restore Snapshot.
Select the DB Instance Class that most closely resembles the DB Storage you noted earlier for the existing snapshot of the database. Type in a DB Instance Identifier (Identifier for the new database) like Test-DB-Cert-Upgrade.
Under Encryption Click on Master Key and select enter a key ARN. (If there is no key already shown.) Use the key from the details for the existing database. So if the key from the snapshot was efLR5721-a243-4067-bb80-fbecd491dec0 and the region was us-west-2 the key ARN would be:
arn:aws:kms:us-west-2:[put your console login key here]:key/efLR5721-a243-4067-bb80-fbecd491dec0
All the other options you should be able to leave the same. Next Click restore DB Instance. Once the new instance from the snapshot is created then click on Databases from the main menu. Click on the main instance you just made and make a note of the end point for this test database.
If you have custom Network Security groups you will need to update them now on the database or you may not be able to connect to the new test database instance. Start a timer so you can get an estimate on how long the upgrade process will take. Click Modify and in the Network & Security section Click on the drop down for the security group and choose your custom group. In this same section set the Certificate authority drop down to the new certificate (In our case it was rds-ca-2019). Click Continue and select the radio button for the option to apply the changes now (Apply Immediately) and then run the modification.
Step 3. Overview: Test the connection to the db using the test script and then schedule down time with the client and upgrade the actual server.
Run the test file on the production server selecting the end point for the new test database as the host and the current path. Verify the connection works for the test db and stop the timer started in step 2 to give you an idea of how long the upgrade process and test will take. Once the test was successful then set up a time to do the update with client and delete the test instance of the database. (For us we scheduled around 30 mins and it usually took around 10 for the whole process. We already had a method for showing a downtime message on the site while it would be down.)
During the time scheduled with the client put up the site down message and migrate it to production. In the AWS console click Modify and in the Network & Security section Click on the Certificate authority drop down and set it to the new certificate (In our case it was rds-ca-2019). Click Continue and select the radio button for the option to apply the changes now (Apply Immediately) and then run the modification. (This usually takes less than a minute.)
Use the test file to verify the connection using the end point for the production database as the host and the certificate path. Once testing is successful go into the db connect function and remove the commented section so the default connection to the server will use the new certificate. Migrate this change to production and verify the connection is still working and pulling data from the existing database using the certificate. Remove the site down message and migrate it to production, verify the site down message no longer displays, and you should be finished.