Overview

Amazon Redshift supports standard AWS security measures, such as:

  • Authentication and access to the DB is compatible with JDBC/ODBC, and can be set up to use Amazon account managed IAM users with third-party identity providers such as Okta or ADFS
  • Encryption at rest and e2e encryption using AWS KMS
  • Contained within a VPC with specified subnets and security groups so that access is limited and strictly controlled

More important however, is it’s support for Audit Logging to S3 buckets. These logs and alerts can notify a user when suspicious activity occurs, even after the best security measures have been taken. The auditing process is what this guide will focus on enabling.

All API calls and usage data can be logged and either stored in the database for querying, or sent to an S3 bucket for external access and analysis. This allows for easy compliance adherence to various policies and regulations.

Setting up a Redshift Instance

A redshift instance requires a type (or size), the number of nodes the cluster will consist of, and then a name for the instance, a username, and  a password. You can spin one up quickly using the boilerplate command below:

aws redshift create-cluster –node-type <size> –number-of-nodes <node-number> –master-username <username> –master-user-password <password> –cluster-identifier <cluster-name>

For more customizable options and information on accepted parameters, see the create-cluster documentation.

Creating a Parameter Group and an S3 Bucket for Logs

In order to enable logging, we need to create a parameter group for the redshift instance we want to monitor. A parameter group allows us to toggle and set different flags on the DB instance, enabling or configuring internal features. You can create a new parameter group using the command below:

aws redshift create-cluster-parameter-group –parameter-group-name <parameter-group-name> –parameter-group-family redshift-1.0 –description <description>

Now we need to attach the parameter group to the instance we want to configure. To do so, use this cli command:

aws redshift modify-cluster –cluster-identifier <cluster-name> –cluster-parameter-group <parameter-group-name>

Finally we’ll have to set up an S3 bucket that will be used by redshift to store logs. You can create an S3 bucket using the command below:

aws s3api create-bucket –bucket <bucket-name> –region <region>

You’ll have to add a policy to the S3 bucket that will allow the redshift instance to write to it as well, using the following file and command:

bucket-policy.json

{
“Version”: “2012-10-17”,
“Statement”: [
{
“Sid”: “Put bucket policy needed for audit logging”,
“Effect”: “Allow”,
“Principal”: {
“AWS”: “arn:aws:iam::<account-id>:user/logs”
},
“Action”: “s3:PutObject”,
“Resource”: “arn:aws:s3:::<bucket-name>/*”
},
{
“Sid”: “Get bucket policy needed for audit logging “,
“Effect”: “Allow”,
“Principal”: {
“AWS”: “arn:aws:iam::<account-id>:user/logs”
},
“Action”: “s3:GetBucketAcl”,
“Resource”: “arn:aws:s3:::<bucket-name>”
}
]
}      

aws s3api put-bucket-policy –bucket <bucket-name> –policy file://bucket-policy.json

Enabling Audit Logging

For full audit logging, the enable_user_activity_logging parameter must be enabled on the Redshift DB instance in order to get details on actual queries that are run against the data:

aws redshift modify-cluster-parameter-group –parameter-group-name <parameter-group-name> –parameters ParameterName=enable_user_activity_logging,ParameterValue=true

Enabling logging to an S3 bucket is done with a single cli command:

aws redshift enable-logging –cluster-identifier <cluster-name> –bucket-name <bucket-name> –s3-key-prefix <bucket-subdirectory-name>

The session logs are formatted as follows, delimited by a pipe:

authenticated |Mon, 9 Jul 2018 23:23:13:277|[local] | |600|dev |rdsdb |Ident |0| | |0| | | |
initiating session |Mon, 9 Jul 2018 23:23:13:277|[local] | |600|dev |rdsdb |Ident |0| | |0| | | |
disconnecting session |Mon, 9 Jul 2018 23:23:21:538|[local] | |600|dev |rdsdb |Ident |8261357| | |0| | | |
authenticated |Mon, 9 Jul 2018 23:28:46:386|::ffff:127.0.0.1 |54986 |1262|dev |rdsdb |password |0| | |0| | | |

User activity logs are formatted slightly differently:

‘2018-07-09T18:57:23Z UTC [ db=dev user=rdsdb pid=7773 userid=1 xid=1117 ]’ LOG: SET QUERY_GROUP TO ‘health’

‘2018-07-09T18:57:23Z UTC [ db=dev user=rdsdb pid=7773 userid=1 xid=1119 ]’ LOG: SELECT 1

‘2018-07-09T18:57:23Z UTC [ db=dev user=rdsdb pid=7772 userid=1 xid=1120 ]’ LOG: SET query_group to ‘xen_is_up.sql’

Sending S3 Bucket Logs to Cloudwatch

You can also monitor the bucket containing your redshift logs by utilizing Cloudtrail and Cloudwatch. This is a multistep process, and we’ll begin by creating a Cloudwatch stream that will be used to monitor the events:

aws logs create-log-group –log-group-name <log-group-name>

Create a role and policy for the cloudwatch logs to be written with:

cloudtrail-role.json

{

“Version”: “2012-10-17”,

“Statement”: [

   {

     “Sid”: “Allow cloudtrail to assume a role”,

     “Effect”: “Allow”,

     “Principal”: {

       “Service”: “cloudtrail.amazonaws.com”

     },

     “Action”: “sts:AssumeRole”0

   }

]

}

cloudtrail-policy.json

{

“Version”: “2012-10-17”,

“Statement”: [

   {

     “Sid”: “Allow create log stream”,

     “Effect”: “Allow”,

     “Action”: [

       “logs:CreateLogStream”

     ],

     “Resource”: [

       “<log-group-arn>”

     ]

   },

   {

     “Sid”: “Allow adding log events to bucket”,

     “Effect”: “Allow”,

     “Action”: [

       “logs:PutLogEvents”

     ],

     “Resource”: [

       “<log-group-arn>”

     ]

   }

]

}

aws iam create-role –role-name <role-name> –assume-role-policy-document file://cloudtrail-role.json

aws iam create-policy –policy-name <policy-name> –policy-document file://cloudtrail-policy.json

aws iam attach-role-policy –policy-arn arn:aws:iam::aws:policy/<policy-name> –role-name <role-name>

Then, using the ARN given in the output of the above command, create a trail and link it to the S3 bucket or object you want to monitor:

aws cloudtrail create-subscription –name <trail-name> –s3-new-bucket <log-bucket>

aws cloudtrail update-trail –name <trail-name> –s3-bucket-name <log-bucket> –s3-key-prefix <bucket-to-trail> –cloud-watch-logs-log-group-arn <log-group-arn> –cloud-watch-logs-role-arn <arn-from-output-of-policy>

Usage

The logs can be sent to an S3 bucket using AWS Cloudtrail (and soon will directly support Cloudwatch streams as well, as of this article, Cloudwatch logs will only be for activity on the bucket containing the logs). This allows organizations to utilize detailed monitoring software like those offered by jSONAR to analyze and track DB usage and statistics.

This type of monitoring allows organizations to keep track of activity and can protect against possible backdoors or gaps in the standard security practices already supported by the DB, acting as a last line of defense against vulnerabilities and attackers.

Audit logs can be set up to be digested and analyzed so that they will trigger alerts, or deliver detailed reports to be reviewed by security analysts. The ability to look for outliers and possible future issues, allows an organization to take an informed and proactive approach towards securing their data.