How to Achieve GDPR Compliance in Redshift

Galaxy Glossary

How do I make Amazon Redshift GDPR compliant?

Use encryption, data masking, row-level security, and deletion routines in Amazon Redshift to meet GDPR requirements.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Description

What does GDPR compliance mean for Redshift users?

GDPR requires that personal data stored in Amazon Redshift be encrypted, access-controlled, auditable, and erasable on request. Redshift offers cluster-level encryption, column- and row-level security, data masking, and audit logging to meet these duties.

How to encrypt data at rest?

Create or modify the cluster with AWS KMS encryption turned on. All tables, system metadata, and snapshots become encrypted automatically. Verify with SELECT encrypted FROM stl_encrypted;.

How to restrict column access with data masking?

Use CREATE MASKING POLICY to hide sensitive values from unauthorized roles while keeping them selectable for privileged roles.

Example: Masking customer email

CREATE MASKING POLICY mask_email (text) RETURNS text USING ('xxxxx@redacted.com'); ALTER TABLE Customers ALTER COLUMN email ADD MASKING POLICY mask_email FOR PUBLIC; -- all non-privileged users

How to apply row-level security for EU data subjects?

Row-level security (RLS) ensures users only see rows they are allowed to process, fulfilling GDPR’s purpose-limitation rule.

CREATE SECURITY POLICY eu_customer_only USING (country = 'EU'); ALTER TABLE Customers ENABLE ROW LEVEL SECURITY; ALTER TABLE Customers ATTACH SECURITY POLICY eu_customer_only;

How to satisfy the Right to be Forgotten?

Build a delete procedure that removes customer data from all related tables and logs the request for audit.

BEGIN; DELETE FROM OrderItems USING Orders WHERE Orders.id = OrderItems.order_id AND Orders.customer_id = :cust_id; DELETE FROM Orders WHERE customer_id = :cust_id; DELETE FROM Customers WHERE id = :cust_id; COMMIT;

Best practices checklist

Turn on KMS encryption before loading data.
Use IAM roles mapped to Redshift groups for least privilege.
Apply masking and RLS to every column that holds PII.
Schedule UNLOAD to S3 with ENCRYPTED for secure exports.
Enable audit logging in the Redshift console and retain logs.

Why How to Achieve GDPR Compliance in Redshift is important

How to Achieve GDPR Compliance in Redshift Example Usage


-- Hide emails from non-compliance roles and limit results to EU customers
CREATE MASKING POLICY mask_email (text)
RETURNS text USING ('redacted');

ALTER TABLE Customers ALTER COLUMN email
ADD MASKING POLICY mask_email FOR PUBLIC;

CREATE SECURITY POLICY eu_only USING (country = 'EU');
ALTER TABLE Customers ENABLE ROW LEVEL SECURITY;
ALTER TABLE Customers ATTACH SECURITY POLICY eu_only;

SELECT id, name, email FROM Customers;

How to Achieve GDPR Compliance in Redshift Syntax


-- Column masking
CREATE MASKING POLICY policy_name (data_type)
RETURNS data_type
USING (expression);

ALTER TABLE table_name
ALTER COLUMN column_name
ADD MASKING POLICY policy_name
[FOR { role_name | PUBLIC }];

-- Row-level security
CREATE SECURITY POLICY policy_name
USING (predicate);

ALTER TABLE table_name ENABLE ROW LEVEL SECURITY;
ALTER TABLE table_name ATTACH SECURITY POLICY policy_name;

-- Right to be Forgotten
DELETE FROM Customers WHERE id = :customer_id;
DELETE FROM Orders    WHERE customer_id = :customer_id;
DELETE FROM OrderItems USING Orders
WHERE Orders.id = OrderItems.order_id
  AND Orders.customer_id = :customer_id;

Common Mistakes

Applying a masking policy but omitting the <code>FOR PUBLIC</code> clause. Without it, unassigned users can still see raw data. Always specify <code>FOR PUBLIC</code> or explicit roles.
Deleting a customer only in the <code>Customers</code> table and leaving related rows in <code>Orders</code> and <code>OrderItems</code>. Use cascaded or multi-table deletes inside a single transaction.

Frequently Asked Questions (FAQs)

Does Redshift encryption impact performance?

Minimal. AWS encrypts data blocks in hardware, so query latency changes are usually under 5%.

Can I audit who viewed masked data?

Yes. Enable system logging and query stl_ tables to see which user attempted to access masked columns.

Is column masking reversible?

Authorized roles can still query the raw column. Non-privileged users only receive the masked value defined in the policy.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Rubie