Open menu

Enable Redshift User Activity Logging

Cloud Conformity allows you to automate the auditing process of this resolution page. Register for a 14 day evaluation and check your compliance level for free!

Start a Free Trial Product features
Knowledge Base Best practice rules for Amazon Web Services AWS Redshift Best Practices Enable Redshift User Activity Logging
Last updated: 03 April 2018
Security
Risk level: Low (generally tolerable level of risk)
Rule ID: RS-023

Ensure that user activity logging is enabled for your AWS Redshift clusters in order to log each query before it is performed on the clusters database. To enable this feature, set the "enable_user_activity_logging" database parameter to true within your Amazon Redshift non-default parameter groups. In order to make "enable_user_activity_logging" parameter to work, you must first enable database audit logging for your clusters. The AWS Redshift database audit creates three types of logs: connection and user logs (activated by default), and user activity logs (activated by the "enable_user_activity_logging" parameter).

This rule can help you with the following compliance standards:

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

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

AWS Redshift user activity logging is primarily useful for troubleshooting purposes. Once enabled, the feature tracks information about the types of queries that both the users and the system perform within the cluster database.

Note: For this rule, Cloud Conformity assumes that your Amazon Redshift clusters are not associated with the default parameter group created automatically by AWS, as the default parameter group cannot be modified to update the enable_user_activity_logging parameter value.

Audit

To determine if the user activity logging is enabled for your Amazon Redshift clusters by checking the non-default parameter groups for "enable_user_activity_logging" parameter status, perform the following:

Using AWS Console

01 Sign in to the AWS Management Console.

02 Navigate to Redshift dashboard at https://console.aws.amazon.com/redshift/.

03 In the left navigation panel, under Redshift Dashboard, click Clusters.

04 Choose the Redshift cluster that you want to examine then click on its identifier (name) link, listed in the Cluster column.

05 On the selected cluster Configuration tab, inside the Cluster Properties section, click on the Cluster Parameter Group value (link), to access the configuration page of the parameter group associated with the selected cluster.

06 On the Parameters tab, verify the enable_user_activity_logging parameter value, listed within the Value column:

Value set to false

If the current value is set to false, the user activity logging is not enabled for the selected Amazon Redshift cluster.

07 Repeat steps no. 4 - 6 to verify "enable_user_activity_logging" database parameter status for AWS Redshift parameter groups created in the current region.

08 Change the AWS region from the navigation bar and repeat the entire audit process for other regions.

Using AWS CLI

01 Run describe-clusters command (OSX/Linux/UNIX) using custom query filters to list the identifiers (names) of all Amazon Redshift clusters currently available in the selected region: 

aws redshift describe-clusters
	--region us-east-1
	--output table
	--query 'Clusters[*].ClusterIdentifier'

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

----------------------
|  DescribeClusters  |
+--------------------+
|  cc-main-cluster   |
|  cc-sec2-cluster   |
+--------------------+

03 Run again describe-clusters command (OSX/Linux/UNIX) using the name of the cluster that you want to examine as identifier and custom query filters to list the parameter group name associated with the cluster: 

aws redshift describe-clusters
	--region us-east-1
	--cluster-identifier cc-main-cluster
	--query 'Clusters[*].ClusterParameterGroups[*].ParameterGroupName[]'

04 The command output should return the name of the associated parameter group requested: 

[
    "cc-redshift-param-group"
]

05 Run describe-cluster-parameters command (OSX/Linux/UNIX) using the name of the AWS Redshift non-default parameter group returned at the previous step as identifier and custom query filters to expose the "enable_user_activity_logging" database parameter status: 

aws redshift describe-cluster-parameters
	--region us-east-1
	--parameter-group-name cc-redshift-param-group
	--query 'Parameters[?(ParameterName==`enable_user_activity_logging`)].ParameterValue | []'

06 The command output should return the current value set for the "enable_user_activity_logging" parameter: 

[
    "false"
]

If the describe-cluster-parameters command output returns "false", the "enable_user_activity_logging" database parameter is not activated, therefore the user activity logging is not enabled for the selected Amazon Redshift cluster.

07 Repeat steps no. 3 – 6 to verify "enable_user_activity_logging" database parameter status for AWS Redshift parameter groups available within the current region.

08 Change the AWS region by updating the --region command parameter value and repeat steps no. 1 - 7 to perform the audit process for other regions.

Remediation / Resolution

To enable user activity logging for your Amazon Redshift clusters, you need to enable database audit logging, then set "enable_user_activity_logging" parameter value to "true" within the non-default parameter groups associated with your Redshift clusters. To set the required parameter value, perform the following:

Using AWS Console

01 Sign to the AWS Management Console.

02 Navigate to Redshift dashboard at https://console.aws.amazon.com/redshift/.

03 In the left navigation panel, under Redshift Dashboard, click Parameter Groups.

04 Select the non-default Redshift parameter group that you want to modify then click on the Edit Parameters button from the dashboard top menu.

05 On the parameter group configuration page, select Parameters tab.

06 Identify the enable_user_activity_logging parameter and change its current value from false to true:

Value set to true

in order to enable the feature.

07 Click Save Changes to apply the changes and enable user activity logging for any Redshift cluster(s) associated with the selected parameter group.

08 To take effect immediately, the cluster(s) associated with the modified parameter group must be rebooted. To reboot an AWS Redshift cluster, perform the following actions:

  1. In the navigation panel, under Redshift Dashboard, click Clusters.
  2. Choose the cluster that you want to reboot then click on its identifier link available in the Cluster column.
  3. On the configuration page, click the Cluster dropdown button from the dashboard top menu and select Reboot. IMPORTANT: The reboot process can take several minutes. During this time your Redshift cluster becomes unavailable.
  4. Inside the Reboot Cluster dialog box, click Continue to confirm the action and reboot the selected Amazon Redshift cluster. The resource status should change now to rebooting.

09 Repeat steps no. 4 – 8 to enable user activity logging by setting the "enable_user_activity_logging" parameter value to "true" for other non-default parameter groups available in the current region.

10 Change the AWS region from the navigation bar and repeat the remediation/resolution process for other regions.

Using AWS CLI

01 Run modify-cluster-parameter-group command (OSX/Linux/UNIX) using the name of the AWS Redshift parameter group that you want to modify (see Audit section part II to identify the right resource) to set "enable_user_activity_logging" database parameter value to "true": 

aws redshift modify-cluster-parameter-group
	--region us-east-1
	--parameter-group-name cc-redshift-param-group
	--parameters ParameterName=enable_user_activity_logging,ParameterValue=true

02 If successful, the command output should return the modified parameter group name and its status: 

{
    "ParameterGroupStatus": "Your parameter group has been updated.
     Updates to dynamic parameters will be applied immediately.
     Updates to static parameters will be applied when you reboot
     the associated Clusters.",
    "ParameterGroupName": "cc-redshift-param-group"
}

03 Run reboot-cluster command (OSX/Linux/UNIX) using the name of the AWS Redshift cluster associated with the modified parameter group (see Audit section part II to identify the right resource) to reboot the cluster so that the configuration change can take effect immediately: 

aws redshift reboot-cluster
	--region us-east-1
	--cluster-identifier cc-main-cluster

04 The command output should return the metadata of the Redshift cluster selected for reboot: 

{
    "Cluster": {
        "PubliclyAccessible": true,
        "VpcSecurityGroups": [
            {
                "Status": "active",
                "VpcSecurityGroupId": "sg-1234abcd"
            }
        ],
        "NumberOfNodes": 2,
        "PendingModifiedValues": {},
        "VpcId": "vpc-12345678",
        "ClusterVersion": "1.0",
        "AutomatedSnapshotRetentionPeriod": 1,
        "PreferredMaintenanceWindow": "fri:08:30-fri:09:00",

        ...

        "Endpoint": {
            "Port": 5439,
            "Address": "cc-main-cluster.abcd1234.us-east-1.redshift.amazonaws.com"
        },
        "IamRoles": [],
        "AllowVersionUpgrade": true,
        "ClusterCreateTime": "2017-09-06T10:56:16.513Z",
        "EnhancedVpcRouting": false,
        "ClusterSecurityGroups": [],
        "ClusterIdentifier": "cc-main-cluster",
        "AvailabilityZone": "us-east-1a",
        "NodeType": "dc2.large",
        "Encrypted": false,
        "ClusterStatus": "rebooting"
    }
}

05 Repeat steps no. 1 – 4 to enable user activity logging by setting the "enable_user_activity_logging" parameter value to "true" for other non-default parameter groups available within the current region.

06 Change the AWS region by updating the --region command parameter value and repeat steps no. 1 – 5 for other regions.

References

Publication date Feb 6, 2018

Related Redshift rules

Thanks!

A verification email has been sent to

Thanks!

A verification email has been sent to

Thanks!

A verification email has been sent to