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.

WordPress Permalinks & mod_rewrite in lighttpd

After switching to lighttpd away from Apache I was pretty pleased with the whole process, everything seemed to be working fine, with the exception of my permalink structure. Bad news, however this can be fixed;

In your lighttpd.conf or 10-rewrite.conf enable mod_rewrite and paste the following code (you will obviously need to edit the $HTTP[“host”] portion):

$HTTP["host"] =~ "www.mark-gilbert.co.uk" {
url.rewrite-final = (
# Exclude common directories
"^/(wp-admin|wp-includes|wp-content)/(.*)" => "$0",
# Exclude root php files
"^/(.*.php)" => "$0",
# Handle permalinks and feeds
"^/(.*)$" => "/index.php/$1"
)
}

These three rules should cover every plugin and bit of functionality within the system. If you have other folders which you will need to access without any redirection (images in a separate folder etc), you have to add these names to the first rule, separated by the | characters.

Now go to Settings-> Permalinks, change Common Settings to Custom Structure and enter what you wish it to look like.

Basically, once mod_rewrite is enabled on the lighttpd server, which is done by adding the following line to the lighttpd.conf file;

server.modules += ("mod_rewrite")

This should work for you too.