Adding root certificates to the system trust store in Amazon Linux 2023 in AWS

As AWS are constantly notifying customers about the need to update RDS certificates, I thought now would be both a good time to do it, and also ensure that WordPress is connecting to RDS via TLS. Sounds simple enough, updating the certificates is simply a case of modifying the RDS instance and picking the new certificate. Getting the new root and intermediate CA’s into the AL2023 system certificate store was another matter.

Once you’ve had a read of this page, and downloaded the relevant bundles, in the case of WordPress you need to add them to the system store. I had a good look around the internet and couldn’t find much information related to this at all, but knowing AL2023 is based on Fedora, I checked and they do have documentation on this, so kudos to them for that. The basic process for this is as follows:

# Grab the correct bundle for your region
wget https://truststore.pki.rds.amazonaws.com/eu-west-1/eu-west-1-bundle.pem
# Copy the bundle to the trust anchors store
sudo cp ~/eu-west-1-bundle.pem /etc/pki/ca-trust/source/anchors/
# This ensures the relevant certificate bundles in app specific formats are updated
sudo update-ca-trust
# You should then be able to run this and see the newly added certificate bundles
trust list | grep eu-west-1

After this you can test connectivity to RDS via the MariaDB or MySQL CLI utility to confirm the new certificate is being picked up and works, and this command should then connect if that’s the case

#  Connect to the database
mariadb -h DBHost --ssl-verify-server-cert -P 3306 -u DBUsername -p

Next we can test PHP connectivity

# Drop into a php prompt
php -a

// Define the database variables
$host = 'DBHost';
$username = 'DBUsername';
$password = 'DBPassword';
$db_name = 'DBName';

// Initializes MySQLi
$conn = mysqli_init();

mysqli_ssl_set($conn,NULL,NULL, "/etc/pki/ca-trust/source/anchors/eu-west-1-bundle.pem", NULL, NULL);

// Establish the connection
mysqli_real_connect($conn, $host, $username, $password, $db_name, 3306, NULL, MYSQLI_CLIENT_SSL);

// If connection failed, show the error
if (mysqli_connect_errno())
{
    die('Failed to connect to MySQL: '.mysqli_connect_error());
}

// Run the Select query which should dump a list of WordPress users out if the connection was successful
printf("Reading data from table: \n");
$res = mysqli_query($conn, 'SELECT * FROM wp_users');
while ($row = mysqli_fetch_assoc($res))
 {
    var_dump($row);
 }

If that’s successful then the final step should be to make sure WordPress use TLS to connect to the database, which means adding the following to the wp-config.php file

define( 'MYSQL_CLIENT_FLAGS', MYSQLI_CLIENT_SSL );

At this point, the final step was to enforce SSL connectivity on the RDS instance, which simply needed a change of the RDS parameter group to the require_secure_transport option.

I hope this helps someone enforce SSL connectivity, or at the very least update the root certificates in AL2023 for another reason, as I found very little AL2023 specific documentation regarding that, but luckily the upstream Fedora docs work.