Logging database errors and queries to Amazon Web Services CloudWatch Logs

July 8, 2025

Exemplifies a PHP way to use Amazon Web Services CloudWatch to log database errors and queries run. (Yes, PHP in 2025!).

Ever grow tired of storing logs on a server then having to manage access to the logs for individual users working in a collaborative development team? This post explores an alternative method to manage database logging by making use of the Amazon Web Services (AWS) CloudWatch Logs service. After reading this post you should have an idea of how to code PHP to send database logs to AWS CloudWatch. This approach offers several advantages over storing logs on a server filesystem, including:

Infrastructure is managed by AWS.
Permissions to logs are orchestrated through AWS IAM which can greatly improve means of access when working within a development team.
Can be integrated as part of an organization wide logging structure with AWS CloudWatch Logs.
Can use CloudWatch metric filters to create data visualizations of the log data and generate alarms from those metrics using CloudWatch Alarms.

This post depends on setting up a database configuration file which is covered in a previous post: Connecting Python to a Postgresql database.

Requirements

PHP 8 (preferably running on Ubuntu)
PHP composer
PHP composer packages: aws/aws-sdk-php, legalthings/cloudwatch-logger
AWS: Programmatic Access
AWS: Key Management Service (KMS) Customer managed symmetric key
AWS: CloudWatch Logs - Log Groups and Streams
Postgresql database cluster (needs to accept TCP/IP connections)
Database created on Postgresql cluster

Set up Log Group and Log Streams in AWS

First we need to create a log group and two log streams within the group which will store our database sql queries and errors. A log group named 'sdk' with two log streams named 'php/database/sql/' and 'php/database/errors/' have been created. After creating the log group and streams, ensure that the programmatic AWS user has the necessary AWS IAM credentials to write to the log streams which is the PutLogEvents API call. The simplest way to try out the example in this post is to attach the managed policy CloudWatchLogsFullAccess to the IAM user or group. The log stream structure looks like the below from the AWS console:

Create CloudWatch Logs / KMS / CloudWatchLogger instances in PHP

Use PHP composer to install the AWS PHP SDK and logging packages - note the cloudwatch-logger package from legalthings is used here. Then create the CloudWatch Logs / KMS / CloudWatchLogger instances in the PHP program. Since the instances are essential to the rest of the program, a try / catch block with an exit statement is used in their instantiation. If unable to connect to the AWS CloudWatch Logs or KMS services, check the IaM permissions for the programmatic user.

    /**
     * phppdocloudwatchlogs.php
     *
     * Creates a pdo connection to a postgresql database and runs a sample
     * query. Demonstrates how to use AWS Key Management Service keys to 
     * decrypt database connection parameters from a configuration file on
     * filesystem. The configuration file must have been created first
     * with the aws_postgresql_config.py program. Shows how to loq errors
     * encountered as well as queries run to CloudWatch Logs.
     */

    require ('../vendor/autoload.php');

    // Create CloudWatch Logs / KMS / CloudWatchLogger instances.
    try {

        $aws_cwlogs_client = new Aws\CloudWatchLogs\CloudWatchLogsClient([
            'profile' => 'default',
            'region' => 'us-east-2',
            'version' => 'latest'
        ]);

        $aws_kms_client = new Aws\Kms\KmsClient([
            'profile' => 'default',
            'region' => 'us-east-2',
            'version' => 'latest'
        ]);

        $error_logger = new LegalThings\CloudWatchLogger([
            'aws' => [
                'profile' => 'default',
                'region' => 'us-east-2',
                'version' => 'latest'
            ],
            'group_name' => 'sdk',
            'stream_name' => 'php/database/errors/'
        ]);
        
        $sql_logger = new LegalThings\CloudWatchLogger([
            'aws' => [
                'profile' => 'default',
                'region' => 'us-east-2',
                'version' => 'latest'
            ],
            'group_name' => 'sdk',
            'stream_name' => 'php/database/sql/'
        ]);         
    }
    // Report on any exceptions - exit program since all instances are needed.
    catch (Exxception $e) {
        echo $e->getTraceAsString();
        exit('Couldn\'t instantiate a needed AWS or Logger class. Exiting.');
    }

Read database connection properties from encrypted configuration file

Now we will need to identify the database and user details used to connect to a PostgreSQL cluster and database. From the previous post, Connecting Python to a Postgresql database, the connection details will be read from a file storing the sensitive data in encrypted format. Then the AWS KMS key used to encrypt the data will be used to decrypt the connection details so a connection can be made to the PostgreSQL database.

    // Open and read the database config file: reset perms on database config
    // file when finished opening.
    $db_config_file = 'la_demo_db.ini';
    chmod($db_config_file, 0440);
    $handle = fopen($db_config_file, 'rb');
    chmod($db_config_file, 0000);
    $binary_data = fread($handle, filesize($db_config_file));

    // Decrypt the binary data in the database config file.
    try {
        $db_config_data = $aws_kms_client->decrypt([

            // AWS KMS decrypt P(x)'s - Include EncryptionAlgorithm as best practice.
            'CiphertextBlob' => $binary_data,
            'EncryptionAlgorithm' => 'SYMMETRIC_DEFAULT',
            'KeyId' => 'keyid'
        ]);
    }
    // Log any exceptions to the error log group / stream. Exit on failure
    // since remaining program depends on decrypted data.
    catch (Exception $e) {
        $error_logger->log(['Problem decrypting : ' => $e->getAwsErrorMessage()]);
        exit("Unable to decrypt the data in ${db_config_file}.");
    }

    // Decode using base64 decoding, then split data into components per delimiter.
    $db_config_data = base64_decode($db_config_data['Plaintext']);
    $db_params = explode( chr(7), $db_config_data );    
    $db_host = $db_params[0];
    $db_name = $db_params[1];
    $db_user = $db_params[2];
    $db_auth = $db_params[3];

Capture decryption errors in AWS CloudWatch Logs

If an error was encountered during the encryption attempt there will be a log in the AWS CloudWatch Logs log stream previously configured: php/database/errors/. For simulation's sake we have generated an error to create a log record which appears in the AWS CloudWatch logs console as shown below.

Connect to PostgreSQL database and capture connection errors in AWS CloudWatch Logs

Finally it is time to connect to the database. PHP's PDO data access layer is great for abstracting calls to database operations and easy to instantiate. To show the usefulness of CloudWatch logs a simulation of a failed connection attempt is run here.

    // Try to connect to postgres database. Log any errors to CloudWatch Logs.
					
    try {
        $pdo = new PDO(
            "pgsql:host=${db_host};
                port=65535;
					
                dbname=${db_name};
                user=${db_user};
                password=${db_auth};
                sslmode=require"
					
        );
        // Set PDO error reporting to Exception.
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }
    // Connection attempt failed - log failure to CloudWatch Logs and exit.
					
    catch (PDOException $e) {
        $error_logger->log(['Problem connecting : ' => $e->getMessage()]);
        exit("Unable to connect to host : ${db_host}.");
    }

    

In using a bogus port of 65535 while attempting to connect via PHP PDO to a PostgreSQL database the try / catch entered the catch block and generated a log to the php/database/errors/ logstream. The log from the AWS CloudWatch logs console is shown below.

Log a succesfully run query

    // Run an example query. Log successful queries to the sql log stream
					
    // and any errors to the error log stream in CloudWatch Logs.
					
    try {
        $qry = 'SELECT datname, datcollate FROM pg_database;';
        $stmt = $pdo->query($qry);
        $sql_logger->log(["User: ${db_user}: Query: "  => $qry]);
    }
    // Log failed query prepare statements to CloudWatch Logs.
					
    catch (PDOException $e) {
        $error_logger->log(['Problem with query ' => $e->getMessage()]);
        exit("Unable to prepare query : ${qry}.");
    }

And this time the log goes to the php/database/sql/ logstream.

What can be learned from this article

Making use of AWS CloudWatch logs can simplify database error and query logging, especially if working collaboratively within a team which uses an AWS infrastructure. Instead of manually logging to files on a workstation's filesystem during development, the approach in this post emphasizes sending log data to CloudWatch logs where the logs can be analyzed further. AWS SDK's make logging simple when combined with a robust logging client such as the CloudWatchLogger from LegalThings.

LiberAnalytics

Freeing analytics from the ordinary.