Enable Redshift User Activity Logging

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:

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.

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"
]

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.

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:

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.

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.