Security for Amazon Redshift

We’ll show you how to set up basic monitoring of AWS Redshift using their native security
features, including how to set up a Redshift instance, creating S3 buckets, and shipping the
audit logs to Cloudwatch.

Basic security for Amazon Redshift can be accomplished through standard AWS security
measures, such as:

• Authentication and access to the DB are 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 its 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. In this blog, we’ll focus on enabling the audit process.

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

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 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 its data.