How to Mask Data in Redshift

How do I mask sensitive data in Amazon Redshift?

Data masking in Redshift hides sensitive column values from unauthorized users by applying a masking policy.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What is data masking in Redshift?

Redshift data masking lets you obfuscate sensitive values—such as email or credit-card numbers—while still allowing queries to run. Users with the right role see clear text; everyone else sees the masked result.

When should you create a masking policy?

Create a policy whenever personal or financial information appears in queryable tables and only a subset of users should read the raw data. Policies are reusable and can be attached to many columns.

How do you create a masking policy?

Use CREATE MASKING POLICY. You define a return expression that replaces the original value when the current user lacks the required role.

Code example

CREATE MASKING POLICY email_mask WITH (val varchar) AS CASE WHEN has_role('pii_access') THEN val ELSE regexp_replace(val,'^([^@]{3}).*@', '\1***@example.com') END;

How do you apply a masking policy to a column?

Alter the column and attach the policy with SET MASKING POLICY. Redshift enforces the policy immediately.

Code example

ALTER TABLE customers ALTER COLUMN email SET MASKING POLICY email_mask;

How do you see which columns are masked?

Query SVV_COLUMN_PRIVILEGES or inspect DDL via SHOW TABLE. Masked columns display the policy name.

How do you remove or replace a policy?

Use ALTER COLUMN ... UNSET MASKING POLICY to detach, then DROP MASKING POLICY if no longer referenced.

What are best practices?

Create one policy per data type to simplify reuse.
Store masking policies in a dedicated schema for easy discovery.
Combine with row-level security for full protection.
Audit role assignments regularly.

Common mistakes and fixes

Missing role check: Always call has_role(); otherwise everyone sees masked data, even admins. Fix: Include a role gate in every policy.

Implicit casts: Forgetting to CAST inside policy can raise data-type errors. Fix: Cast masked value to the same column type.

Quick reference

CREATE MASKING POLICY name WITH (val datatype) AS expression; ALTER TABLE table ALTER COLUMN col SET | UNSET MASKING POLICY name; DROP MASKING POLICY name;

Why How to Mask Data in Redshift is important

How to Mask Data in Redshift Example Usage


-- Analyst without the pii_access role runs this:
SELECT id, name, email FROM Customers LIMIT 3;
-- Result
-- id | name | email
-- 1  | Jane | Jan***@example.com
-- 2  | John | Joh***@example.com

-- A data engineer with the role sees true emails
SET ROLE pii_access;
SELECT id, name, email FROM Customers LIMIT 3;

How to Mask Data in Redshift Syntax


-- Create a masking policy
CREATE MASKING POLICY policy_name
  WITH (value data_type)
AS
  CASE
    WHEN has_role('role_name') THEN value
    ELSE masked_expression
  END;

-- Apply to a column
ALTER TABLE table_name
  ALTER COLUMN column_name SET MASKING POLICY policy_name;

-- Ecommerce example: hide customer email from non-PII roles
CREATE MASKING POLICY email_mask WITH (val varchar) AS
  CASE WHEN has_role('pii_access') THEN val
       ELSE regexp_replace(val,'^([^@]{3}).*@', '\1***@example.com') END;
ALTER TABLE Customers ALTER COLUMN email SET MASKING POLICY email_mask;

Common Mistakes

Skipping role checks: A policy without has_role() shows masked data to everyone, including privileged users. Always gate clear-text access with role validation.
Applying policy to the wrong type: Attaching a varchar mask to a numeric column raises errors. Ensure the masking policy’s input parameter matches the column data type.