Use the Conformity Knowledge Base AI to help improve your Cloud Posture

Log Exports

Trend Cloud One™ – Conformity is a continuous assurance tool that provides peace of mind for your cloud infrastructure, delivering over 1000 automated best practice checks.

Risk Level: Low (generally tolerable level of risk)
Rule ID: RDS-033

Ensure that your Amazon RDS database instances have the Log Exports feature enabled in order to publish database log events directly to CloudWatch Logs. By publishing database logs to Amazon CloudWatch, you can build richer and more seamless interactions with your database instance logs. Log Exports is supported by Amazon RDS MySQL, Aurora (with MySQL compatibility), and MariaDB database engines. Trend Cloud One™ – Conformity strongly recommends that you select all the log types available for publishing to CloudWatch Logs when enabling the feature. The Log Exports feature supports the following log types:

  • Error log – collects diagnostic messages generated by the database engine, together with startup and shutdown times.
  • General query log – contains a record of all SQL statements received from clients, plus the client connect and disconnect times.
  • Slow query log – contains a record of SQL statements that took longer than expected to execute and examined more than a defined number of rows (both thresholds are configurable).
  • Audit log – records database activity on the instance for audit purposes.

This rule can help you with the following compliance standards:

  • APRA
  • MAS

For further details on compliance standards supported by Conformity, see here.

This rule can help you work with the AWS Well-Architected Framework.

This rule resolution is part of the Conformity Security & Compliance tool for AWS.

Security
Reliability
Performance
efficiency
Operational
excellence

Once the Log Exports feature is enabled, Amazon RDS sends general, slow query, audit, and error logs from your MySQL, Aurora, and MariaDB databases to CloudWatch Logs. Broadcasting these logs to Amazon CloudWatch allows you to maintain continuous visibility into database activity, query performance, and error logging. For example, you can set up Amazon CloudWatch alarms to notify on frequent restarts which are recorded in the error log, or alarms for events recorded in the audit logs that can alert on unwanted changes made to your databases. You can also create CloudWatch alarms to monitor the slow query log and enable timely detection of long-running SQL queries. Additionally, you can use CloudWatch Logs to perform impromptu searches across multiple logs published by RDS Log Exports – this capability is particularly useful for troubleshooting, audits, and log analysis.


Audit

To determine if your MySQL, Aurora, and MariaDB database instances are using the Log Exports feature to publish database logs to CloudWatch Logs, perform the following actions:

Using AWS Console

01 Sign in to the AWS Management Console.

02 Navigate to Amazon RDS console at https://console.aws.amazon.com/rds/.

03 In the navigation panel, under Amazon RDS, choose Databases.

04 Select the Amazon RDS database instance that you want to examine and choose Modify. The selected instance must have the database engine, listed in the Engine column, set to MySQL Community, Aurora MySQL or MariaDB.

05 In the Additional configuration section, under Log exports, check the log type checkboxes (i.e. Audit log, Error log, General log, Slow query log) to determine the feature status and configuration. If none of these checkboxes are currently selected, the Log Exports feature is not enabled for the selected Amazon RDS database instance, therefore the instance's general, slow query, audit, and error logs are not published to Amazon CloudWatch Logs.

06 Repeat steps no. 4 and 5 for each Amazon RDS database instance available within the current AWS region.

07 Change the AWS cloud region from the navigation bar and repeat the Audit process for other regions.

Using AWS CLI

01 Run describe-db-instances command (OSX/Linux/UNIX) with custom query filters to list the names of the MySQL, Aurora MySQL, and MariaDB database instances available in the selected AWS region:

aws rds describe-db-instances
  --region us-east-1
  --output table
  --query 'DBInstances[?Engine==`mysql` || Engine==`aurora-mysql` || Engine==`mariadb`].DBInstanceIdentifier | []'

02 The command output should return a table with the requested database instance names:

--------------------------------
|     DescribeDBInstances      |
+------------------------------+
|  cc-project5-mysql-database  |
|  cc-webapp-mariadb-database  |
+------------------------------+

03 Run describe-db-instances command (OSX/Linux/UNIX) using the name of the MySQL/Aurora MySQL/MariaDB database instance that you want to examine as the identifier parameter and custom query filters to describe the log types that the selected database instance is configured to export to Amazon CloudWatch Logs:

aws rds describe-db-instances
  --region us-east-1
  --db-instance-identifier cc-project5-mysql-database
  --query 'DBInstances[*].EnabledCloudwatchLogsExports'

04 The command output should return the requested log types:

[]

If the describe-db-instances command output returns an empty array (i.e. []), as shown in the output example above, the required logs are not published to Amazon CloudWatch Logs, therefore the Log Exports feature is not enabled for the selected Amazon RDS database instance.

05 Repeat steps no. 3 and 4 for each Amazon RDS database instance available in the selected AWS region.

06 Change the AWS cloud region by updating the --region command parameter value and repeat the Audit process for other regions.

Remediation / Resolution

To enable the Log Exports feature for your MySQL, Aurora MySQL, and MariaDB database instances, perform the following actions:

Using AWS CloudFormation

01 CloudFormation template (JSON):

{
	"AWSTemplateFormatVersion": "2010-09-09",
	"Description": "Enable and Configure CloudWatch Log Exports for RDS Database Instances",
	"Parameters": {
		"DBInstanceName": {
			"Default": "mysql-database-instance",
			"Description": "RDS database instance name",
			"Type": "String",
			"MinLength": "1",
			"MaxLength": "63",
			"AllowedPattern": "^[0-9a-zA-Z-/]*$",
			"ConstraintDescription": "Must begin with a letter and must not end with a hyphen or contain two consecutive hyphens."
		},
		"DBInstanceClass": {
				"Default": "db.t2.micro",
				"Description": "DB instance class/type",
				"Type": "String",
				"ConstraintDescription": "Must provide a valid DB instance type."
		},
		"DBAllocatedStorage": {
			"Default": "20",
			"Description": "The size of the database (GiB)",
			"Type": "Number",
			"MinValue": "20",
			"MaxValue": "65536",
			"ConstraintDescription": "Must be between 20 and 65536 GiB."
		},
		"DBName": {
			"Default": "mysqldb",
			"Description": "Database name",
			"Type": "String",
			"MinLength": "1",
			"MaxLength": "64",
			"AllowedPattern": "[a-zA-Z][a-zA-Z0-9]*",
			"ConstraintDescription": "Must begin with a letter and contain only alphanumeric characters."
		},
		"DBUsername": {
			"Description": "Master username for database access",
			"Type": "String",
			"MinLength": "1",
			"MaxLength": "16",
			"AllowedPattern": "[a-zA-Z][a-zA-Z0-9]*",
			"ConstraintDescription": "Must begin with a letter and contain only alphanumeric characters."
		},
		"DBPassword": {
			"NoEcho": "true",
			"Description": "Password for database access",
			"Type": "String",
			"MinLength": "8",
			"MaxLength": "41",
			"AllowedPattern": "[a-zA-Z0-9]*",
			"ConstraintDescription": "Must contain only alphanumeric characters."
		}
	},
	"Resources": {
		"RDSInstance": {
			"Type": "AWS::RDS::DBInstance",
			"Properties": {
				"DBInstanceIdentifier": {
					"Ref": "DBInstanceName"
				},
				"DBName": {
					"Ref": "DBName"
				},
				"MasterUsername": {
					"Ref": "DBUsername"
				},
				"MasterUserPassword": {
					"Ref": "DBPassword"
				},
				"DBInstanceClass": {
					"Ref": "DBInstanceClass"
				},
				"AllocatedStorage": {
					"Ref": "DBAllocatedStorage"
				},
				"Engine": "MySQL",
				"EngineVersion": "5.7.36",
				"EnableCloudwatchLogsExports" : ["audit", "error", "general", "slowquery"]
			}
		}
	}
}

02 CloudFormation template (YAML):

AWSTemplateFormatVersion: '2010-09-09'
	Description: Enable and Configure CloudWatch Log Exports for RDS Database Instances
	Parameters:
		DBInstanceName:
		Default: mysql-database-instance
		Description: RDS database instance name
		Type: String
		MinLength: '1'
		MaxLength: '63'
		AllowedPattern: ^[0-9a-zA-Z-/]*$
		ConstraintDescription: Must begin with a letter and must not end with a hyphen
			or contain two consecutive hyphens.
		DBInstanceClass:
		Default: db.t2.micro
		Description: DB instance class/type
		Type: String
		ConstraintDescription: Must provide a valid DB instance type.
		DBAllocatedStorage:
		Default: '20'
		Description: The size of the database (GiB)
		Type: Number
		MinValue: '20'
		MaxValue: '65536'
		ConstraintDescription: Must be between 20 and 65536 GiB.
		DBName:
		Default: mysqldb
		Description: Database name
		Type: String
		MinLength: '1'
		MaxLength: '64'
		AllowedPattern: '[a-zA-Z][a-zA-Z0-9]*'
		ConstraintDescription: Must begin with a letter and contain only alphanumeric
			characters.
		DBUsername:
		Description: Master username for database access
		Type: String
		MinLength: '1'
		MaxLength: '16'
		AllowedPattern: '[a-zA-Z][a-zA-Z0-9]*'
		ConstraintDescription: Must begin with a letter and contain only alphanumeric
			characters.
		DBPassword:
		NoEcho: 'true'
		Description: Password for database access
		Type: String
		MinLength: '8'
		MaxLength: '41'
		AllowedPattern: '[a-zA-Z0-9]*'
		ConstraintDescription: Must contain only alphanumeric characters.
	Resources:
		RDSInstance:
		Type: AWS::RDS::DBInstance
		Properties:
			DBInstanceIdentifier: !Ref 'DBInstanceName'
			DBName: !Ref 'DBName'
			MasterUsername: !Ref 'DBUsername'
			MasterUserPassword: !Ref 'DBPassword'
			DBInstanceClass: !Ref 'DBInstanceClass'
			AllocatedStorage: !Ref 'DBAllocatedStorage'
			Engine: MySQL
			EngineVersion: 5.7.36
			EnableCloudwatchLogsExports:
			- audit
			- error
			- general
			- slowquery

Using Terraform

01 Terraform configuration file (.tf):

terraform {
	required_providers {
		aws = {
			source  = "hashicorp/aws"
			version = "~> 3.27"
		}
	}

	required_version = ">= 0.14.9"
}

provider "aws" {
	profile = "default"
	region  = "us-east-1"
}

resource "aws_db_instance" "rds-database-instance" {
	allocated_storage     = 30
	engine                = "mysql"
	engine_version        = "5.7"
	instance_class        = "db.t2.micro"
	name                  = "mysqldb"
	username              = "ccmysqluser01"
	password              = "ccmysqluserpwd"
	parameter_group_name  = "default.mysql5.7"

	# Enable and Configure CloudWatch Log Exports for RDS Database Instances 
	enabled_cloudwatch_logs_exports = ["audit", "error", "general", "slowquery"]


	apply_immediately = true
}

Using AWS Console

01 Sign in to the AWS Management Console.

02 Navigate to Amazon RDS console at https://console.aws.amazon.com/rds/.

03 In the navigation panel, under Amazon RDS, choose Databases.

04 Select the Amazon RDS database instance that you want to reconfigure and choose Modify.

05 On the Modify DB instance: <instance-name> configuration page, perform the following operations:

  1. In the Additional configuration section, under Log exports, select all the log types available, i.e. Audit log, Error log, General log, and Slow query log, to enable the Log Exports feature for the selected Amazon RDS database instance.
  2. Choose Continue and review the configuration changes that you want to apply, available in the Summary of modifications section.
  3. In the Scheduling of modifications section, perform one of the following actions based on your workload requirements:
    • Select Apply during the next scheduled maintenance window to apply the changes automatically during the next scheduled maintenance window.
    • Select Apply immediately to apply the changes right away. With this option any pending modifications will be asynchronously applied as soon as possible, regardless of the maintenance window configured for the selected database instance. Note that any changes available in the pending modifications queue are also applied. If any of the pending modifications require downtime, choosing this option can cause unexpected downtime for your database application.
  4. Choose Modify DB instance to apply the configuration changes.

06 Repeat steps no. 4 and 5 for each Amazon RDS database instance available in the selected AWS region.

07 Change the AWS cloud region from the navigation bar and repeat the Remediation process for other regions.

Using AWS CLI

01 Run modify-db-instance command (OSX/Linux/UNIX) to enable the Log Exports feature for the selected MySQL/MariaDB database instance by adding the --cloudwatch-logs-export-configuration parameter to the command request. The --cloudwatch-logs-export-configuration parameter specifies the log types to be exported to Amazon CloudWatch Logs. The following command request example makes use of --apply-immediately parameter to apply the configuration changes asynchronously and as soon as possible. Any changes available in the pending modifications queue are also applied with this request. If any of the pending modifications require downtime, choosing this option can cause unexpected downtime for your MySQL database application. If you skip adding the --apply-immediately parameter to the command request, Amazon RDS will apply your changes during the next maintenance window:

aws rds modify-db-instance
  --region us-east-1
  --db-instance-identifier cc-project5-mysql-database
  --cloudwatch-logs-export-configuration '{"EnableLogTypes":["audit","error","general","slowquery"]}'
  --apply-immediately

02 The command output should return the configuration metadata for the modified database instance:

{
	"DBInstance": {
		"PubliclyAccessible": true,
		"MasterUsername": "ccadmin",
		"MonitoringInterval": 0,
		"LicenseModel": "general-public-license",
		"VpcSecurityGroups": [
			{
				"Status": "active",
				"VpcSecurityGroupId": "sg-0abcd1234abcd1234"
			},
			{
				"Status": "active",
				"VpcSecurityGroupId": "sg-abcd1234"
			}
		],
		"InstanceCreateTime": "2021-05-12T08:00:00.677Z",
		"CopyTagsToSnapshot": true,
		"OptionGroupMemberships": [
			{
				"Status": "in-sync",
				"OptionGroupName": "default:mysql-5-7"
			}
		],
			"PendingModifiedValues": {
			"PendingCloudwatchLogsExports": {
				"LogTypesToEnable": [
					"audit",
					"error",
					"general",
					"slowquery"
				]
			}
		},
		"Engine": "mysql",
		"MultiAZ": false,
		"DBSecurityGroups": [],
		"DBParameterGroups": [
			{
				"DBParameterGroupName": "default.mysql5.7",
				"ParameterApplyStatus": "in-sync"
			}
		],
		"PerformanceInsightsEnabled": true,
		"AutoMinorVersionUpgrade": true,
		"PreferredBackupWindow": "06:02-06:32",
		"DBSubnetGroup": {
			"Subnets": [
				{
					"SubnetStatus": "Active",
					"SubnetIdentifier": "subnet-abcd1234",
					"SubnetOutpost": {},
					"SubnetAvailabilityZone": {
						"Name": "us-east-1d"
					}
				},
				{
					"SubnetStatus": "Active",
					"SubnetIdentifier": "subnet-1234abcd",
					"SubnetOutpost": {},
					"SubnetAvailabilityZone": {
						"Name": "us-east-1e"
					}
				},
				{
					"SubnetStatus": "Active",
					"SubnetIdentifier": "subnet-abcdabcd",
					"SubnetOutpost": {},
					"SubnetAvailabilityZone": {
						"Name": "us-east-1b"
					}
				},
				{
					"SubnetStatus": "Active",
					"SubnetIdentifier": "subnet-12341234",
					"SubnetOutpost": {},
					"SubnetAvailabilityZone": {
						"Name": "us-east-1a"
					}
				},
				{
					"SubnetStatus": "Active",
					"SubnetIdentifier": "subnet-abcd1234",
					"SubnetOutpost": {},
					"SubnetAvailabilityZone": {
						"Name": "us-east-1f"
					}
				},
				{
					"SubnetStatus": "Active",
					"SubnetIdentifier": "subnet-1234abcd",
					"SubnetOutpost": {},
					"SubnetAvailabilityZone": {
						"Name": "us-east-1c"
					}
				}
			],
			"DBSubnetGroupName": "default-vpc-abcdabcd",
			"VpcId": "vpc-abcdabcd",
			"DBSubnetGroupDescription": "Created from the AWS Management Console",
			"SubnetGroupStatus": "Complete"
		},
		"ReadReplicaDBInstanceIdentifiers": [],
		"AllocatedStorage": 70,
		"DBInstanceArn": "arn:aws:rds:us-east-1:123456789012:db:cc-project5-mysql-database",
		"BackupRetentionPeriod": 7,
		"PreferredMaintenanceWindow": "thu:03:27-thu:03:57",
		"Endpoint": {
			"HostedZoneId": "ABCDABCDABCD",
			"Port": 3306,
			"Address": "cc-project5-mysql-database.abcdabcdabcd.us-east-1.rds.amazonaws.com"
		},
		"DBInstanceStatus": "available",
		"IAMDatabaseAuthenticationEnabled": true,
		"EngineVersion": "5.7.30",
		"DeletionProtection": true,
		"AvailabilityZone": "us-east-1a",
		"DomainMemberships": [],
		"StorageType": "gp2",
		"DbiResourceId": "db-ABCDABCDABCDABCDABCDABCDAB",
		"CACertificateIdentifier": "rds-ca-2019",
		"StorageEncrypted": true,
		"AssociatedRoles": [],
		"DBInstanceClass": "db.t3.medium",
		"DbInstancePort": 0,
		"DBInstanceIdentifier": "cc-project5-mysql-database"
	}
}

03 Run modify-db-cluster command (OSX/Linux/UNIX) to enable the Log Exports feature for the MySQL database instances within the selected Amazon Aurora cluster by adding the --cloudwatch-logs-export-configuration parameter to the command request. The following command request example makes use of --apply-immediately parameter to apply the configuration changes asynchronously and as soon as possible. Any changes available in the pending modifications queue are also applied with this request. If any of the pending modifications require downtime, choosing this option can cause unexpected downtime for your Aurora database application. If you skip adding the --apply-immediately parameter to the command request, Amazon Aurora will apply your changes during the next maintenance window:

aws rds modify-db-cluster
  --region us-east-1
  --db-cluster-identifier cc-aurora-mysql-cluster
  --cloudwatch-logs-export-configuration '{"EnableLogTypes":["audit","error","general","slowquery"]}'
  --apply-immediately

04 The command output should return the configuration metadata for the modified Aurora cluster:

{
	"DBCluster": {
		"MasterUsername": "ccadmin",
		"ReaderEndpoint": "cc-aurora-mysql-cluster.cluster-ro-abcdabcdabcd.us-east-1.rds.amazonaws.com",
		"HttpEndpointEnabled": false,
		"ReadReplicaIdentifiers": [],
		"VpcSecurityGroups": [
			{
				"Status": "active",
				"VpcSecurityGroupId": "sg-0abcd1234abcd1234"
			},
			{
				"Status": "active",
				"VpcSecurityGroupId": "sg-abcd1234"
			}
		],
		"CopyTagsToSnapshot": false,
		"HostedZoneId": "ABCDABCDABCDAB",
		"EngineMode": "provisioned",
		"Status": "available",
		"MultiAZ": false,
		"LatestRestorableTime": "2021-05-12T09:00:00.162Z",
		"DomainMemberships": [],
		"PreferredBackupWindow": "04:06-04:36",
		"DBSubnetGroup": "default-vpc-abcdabcd",
		"AllocatedStorage": 30,
		"BackupRetentionPeriod": 7,
		"PreferredMaintenanceWindow": "tue:05:48-tue:06:18",
		"Engine": "aurora-mysql",
		"Endpoint": "cc-aurora-mysql-cluster.cluster-abcdabcdabcd.us-east-1.rds.amazonaws.com",
		"AssociatedRoles": [],
		"EarliestRestorableTime": "2021-05-12T09:03:00.657Z",
		"CrossAccountClone": false,
		"IAMDatabaseAuthenticationEnabled": false,
		"ClusterCreateTime": "2021-05-12T09:00:00.853Z",
		"EngineVersion": "5.7.mysql_aurora.2.07.2",
		"DeletionProtection": true,
		"DBClusterIdentifier": "cc-aurora-mysql-cluster",
		"DbClusterResourceId": "cluster-ABCDABCDABCDABCDABCDABCDAB",
		"DBClusterMembers": [
			{
				"IsClusterWriter": true,
				"DBClusterParameterGroupStatus": "in-sync",
				"PromotionTier": 1,
				"DBInstanceIdentifier": "cc-aurora-mysql-cluster-instance-1"
			}
		],
		"DBClusterArn": "arn:aws:rds:us-east-1:123456789012:cluster:cc-aurora-mysql-cluster",
		"StorageEncrypted": false,
		"DatabaseName": "",
		"DBClusterParameterGroup": "default.aurora-mysql5.7",
		"AvailabilityZones": [
			"us-east-1c",
			"us-east-1d",
			"us-east-1a"
		],
		"Port": 3306
	}
}

05 Repeat steps no. 1 – 4 for each MySQL, Aurora MySQL, and MariaDB database instance available in the selected AWS region.

06 Change the AWS cloud region by updating the --region command parameter value and repeat the Remediation process for other regions.

References

Publication date Oct 29, 2018