Encryption and Audit Logging on Amazon Redshift: KMS, SSL Enforcement and Audit Trails

Access control answers "who may query what." Auditors also ask two other questions: is the data encrypted, and can you prove who did what, and when? This guide covers the encryption and audit-logging setup on Amazon Redshift that we put in place on regulated warehouses. It complements the RBAC and masking guide. It does not replace it.

Encryption at rest with KMS

Redshift encrypts the cluster's data blocks, metadata and snapshots with a hierarchy of keys, rooted in AWS KMS. Serverless namespaces are always encrypted. For provisioned RA3 clusters, set encryption at creation time. You can also turn it on later, but Redshift then migrates the data, so plan a maintenance window.

aws redshift create-cluster \
  --cluster-identifier dw-prod \
  --node-type ra3.4xlarge --number-of-nodes 2 \
  --master-username admin --manage-master-password \
  --encrypted --kms-key-id arn:aws:kms:us-east-1:111111111111:key/EXAMPLE

Decisions worth making deliberately:

• Use a customer managed key (CMK) rather than the AWS managed key if you need to control the key policy, rotate on your own schedule, or revoke access. The AWS managed key cannot be customized.
• Snapshots inherit the key. For cross-region snapshot copy, you need a second KMS key in the destination region and a snapshot copy grant. Missing this is the most common reason a DR setup fails its first test.
• Disabling or deleting the key makes the warehouse unreadable. Restrict kms:ScheduleKeyDeletion and kms:DisableKey to a small break-glass role.
• --manage-master-password keeps the admin password in Secrets Manager with rotation, so it never lives in a pipeline variable.

Encryption in transit

Require SSL for every connection. On provisioned clusters, set the parameter group value require_ssl to true. Serverless workgroups expose the same setting in their configuration. Then confirm that clients actually use it:

SELECT username, sslversion, ssl_cipher, recordtime
FROM stl_connection_log
WHERE event = 'authenticated'
ORDER BY recordtime DESC
LIMIT 20;

Rows with a null sslversion indicate a client that is not using TLS. Fix those clients before you flip enforcement on. Otherwise they will fail.

Audit logging: what to turn on

Redshift has two separate log sources, and teams often enable only one.

1. Audit logs to S3 or CloudWatch Logs. These cover the connection log (authentication attempts, disconnections), the user log (changes to database users) and the user activity log (every SQL statement). The user activity log is only produced if the parameter enable_user_activity_logging is true. That parameter requires a parameter-group change, and clusters need a reboot for it to take effect.
2. CloudTrail. This records the management plane: CreateCluster, ModifyCluster, AuthorizeSnapshotAccess, GetClusterCredentials and similar API calls. It does not record SQL.

Choose a destination:

aws redshift enable-logging \
  --cluster-identifier dw-prod \
  --log-destination-type cloudwatch \
  --log-exports connectionlog userlog useractivitylog

CloudWatch Logs is easier to query and alarm on. S3 is cheaper for long retention. If you pick S3, the bucket policy must allow the Redshift logging service principal to write. Use a dedicated bucket with Object Lock or restricted delete permissions, so that a compromised admin cannot erase the trail.

Querying the system tables for in-database audit

Some questions are quicker to answer from inside the warehouse. The SYS monitoring views are the current interface:

-- Who ran DDL in the last 7 days?
SELECT user_id, query_type, start_time, LEFT(query_text, 120) AS sql_head
FROM sys_query_history
WHERE query_type = 'DDL'
  AND start_time > DATEADD(day, -7, GETDATE())
ORDER BY start_time DESC;

Keep in mind that system tables retain only a few days of history. They are not an audit record. Use them for investigation and use the exported logs as evidence.

Alert on what matters

Do not alert on everything. These four events are the ones we alarm on:

• Repeated authentication failures from one user or host (connection log).
• Any CREATE USER, ALTER USER ... CREATEUSER or superuser grant (user log).
• Changes to the parameter group, especially require_ssl or logging settings (CloudTrail).
• DisableKey or ScheduleKeyDeletion on the warehouse's KMS key (CloudTrail).

With CloudWatch Logs, a metric filter on each pattern plus an SNS topic is enough to start.

Checklist

• Customer managed KMS key, with a restricted key policy
• Snapshot copy grant in each DR region
• require_ssl enforced after confirming no plaintext clients
• Connection, user and user activity logs exported
• Log bucket or log group protected against deletion, with a retention period that matches your compliance requirement
• CloudTrail enabled for the account
• Alarms on the four events above

Verify each setting in a non-production environment first. Parameter and logging changes can require a reboot, and the details differ between provisioned and Serverless. If you want an experienced team to review your Redshift security posture or prepare it for an audit, get in touch through the contact page.