Amazon Redshift connections

DataMasque masks Amazon Redshift tables by unloading them to Amazon S3, masking the unloaded files, and copying the masked data back into the target tables.

Masking process
Required privileges
Connection parameters
- Amazon Redshift specific parameters
- Amazon Redshift Advanced Settings
Encrypted connections
Limitations
- Amazon Redshift Limitations
- Amazon Redshift Unsupported Features
Supported data types
Unsupported key columns

Masking process

Amazon Redshift databases are masked by exporting the table to an S3 bucket, then masking the data and inserting it into a new table with the same name, and the old table is dropped. This is done with the use of the UNLOAD and COPY commands. Therefore, to mask Redshift, an S3 bucket is required. The data in the bucket is removed at the end of a successful masking run.

Warning! If a run fails or is cancelled, the data is attempted to be deleted from the S3 bucket but this is not always possible (for example, if the server running DataMasque is halted). Therefore, after unsuccessful (failed or cancelled) runs, the staging bucket should be checked manually for leftover exported data.

Important! Since this staging bucket will have sensitive data stored in it temporarily, access to the bucket should be confined to trusted users/IAM roles only.

Required privileges

Database privileges

A DataMasque connection for Redshift requires the ability to modify table structure, but Redshift doesn't provide direct ALTER TABLE permissions. Instead, table modification is controlled through ownership. For the connection user to have the necessary access, they either:

own the target table(s), or
belong to a role that owns the target tables, or
belong to a dedicated role created for table ownership.

The method you choose will depend on your existing database setup, security requirements, and whether you need to manage permissions across multiple tables. The following sections explain how to implement each of these permission methods, along with specific SQL commands where needed.

Connection user owns target tables

In this case, no extra GRANTs need to be applied.

Connection user belongs to a role that owns the target tables

If the table is already owned by a different role, grant the connection user access to this role:

GRANT <target table owner role> TO <connection user>;

Connection user belongs to a dedicated ownership role

Create a dedicated owner role for the target table(s). This approach is recommended when you need to manage permissions at scale or across multiple tables and users.

Create a role data_owner (or another username of your choice) without login access. This will be used solely for permission management.
Grant the created role to your connection user.
Set this role as the owner of your target tables.

CREATE ROLE data_owner;
GRANT data_owner TO <connection user>;
ALTER TABLE <target table> OWNER TO data_owner;

Additional privileges

TEMPORARY privileges to the target database.

GRANT TEMPORARY ON DATABASE <target database> to <connection user>;

SELECT privilege on ALL TABLES IN SCHEMA PG_CATALOG.

GRANT SELECT ON ALL TABLES IN SCHEMA PG_CATALOG TO <connection user>;

SELECT privilege on these INFORMATION_SCHEMA tables:

GRANT SELECT ON INFORMATION_SCHEMA.TABLES TO <connection user>;
GRANT SELECT ON INFORMATION_SCHEMA.COLUMNS TO <connection user>;
GRANT SELECT ON INFORMATION_SCHEMA.TABLE_CONSTRAINTS TO <connection user>;
GRANT SELECT ON INFORMATION_SCHEMA.KEY_COLUMN_USAGE TO <connection user>;
GRANT SELECT ON INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE TO <connection user>;

USAGE and CREATE privileges to create tables in the target schema.

GRANT USAGE, CREATE ON SCHEMA <target schema> TO <connection user>;

Ensure the connection user has visibility (in their schema search path) of all tables that will be masked.

ALTER USER <connection user> IN DATABASE <target database> SET SEARCH_PATH TO <target schema>;

Amazon Redshift Additional configuration

For masking on Amazon Redshift, DataMasque requires:

An S3 bucket that is not public, with Object Lock disabled.
An IAM role with an access policy attached to allow the Amazon Redshift database to access the S3 bucket.
A dedicated AWS user with programmatic access configured and access policy attached that allows DataMasque to access the S3 bucket.

To mask a Redshift database in a different AWS account from where DataMasque is, please refer to the Cross Account Functionality documentation.

Configuring the Amazon S3 bucket

Access control

DataMasque uses Amazon S3 buckets as a staging area for processing Amazon Redshift database data. Therefore, it is important to restrict access on the S3 bucket you configure to use on your Amazon Redshift connection.

Blocks public access, follow this link to enable Block Public Access feature on your S3 bucket.
Apply access control to your S3 bucket. Follow this link for the steps to secure your S3 bucket.

Disable object lock

Object Lock must be disabled on your S3 bucket to allow DataMasque to clean up staging data when masking is completed. If Object Lock is not disabled, staging data will be left in your S3 bucket and a warning message will be included in the run log.

Optional configurations

The following configurations are not required, but recommended:

Configure S3 buckets with SSE-S3, server side encryption.
Disable Bucket Versioning.

Configuring Amazon Redshift database

Configuring access to S3 Bucket

Your Amazon Redshift database must have read-write access to the S3 bucket configured on your connection.

Follow the steps below to configure the Amazon Redshift database that will be accessing your S3 bucket:

Open the Amazon IAM console, create an IAM policy. The following example IAM policy shows required permissions, where <bucket-name> should be replaced with the name of your S3 staging bucket. Sid attributes have been added to explain the purpose of each statement.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "BucketContentPermissions",
            "Effect": "Allow",
            "Action": [
                "s3:PutObject",
                "s3:GetObject",
                "s3:AbortMultipartUpload",
                "s3:DeleteObject",
                "s3:ListMultipartUploadParts"
            ],
            "Resource": "arn:aws:s3:::<bucket-name>/*"
        },
        {
            "Sid": "BucketMetaDataPermissions",
            "Effect": "Allow",
            "Action": [
                "s3:GetBucketPublicAccessBlock",
                "s3:GetBucketPolicyStatus",
                "s3:GetBucketObjectLockConfiguration",
                "s3:GetEncryptionConfiguration",
                "s3:ListBucketMultipartUploads",
                "s3:ListBucket",
                "s3:GetBucketAcl"
            ],
            "Resource": "arn:aws:s3:::<bucket-name>"
        }
    ]
}

Create an IAM role:
- Select AWS service as the trusted identity, Redshift as the service and Redshift - Customizable as the use case.
- Attach the policy created from the previous step to the role.
Open the Amazon Redshift console and associate the IAM role to your Amazon Redshift database.

Reserve storage space for data processing

DataMasque requires additional storage on the database for creating staging tables when masking data. It is crucial to have sufficient storage space on your Amazon Redshift database to accommodate the total size of the tables you will be masking in parallel.

Configuring DataMasque's access to S3 bucket

DataMasque must be able to access the S3 bucket provided. A Profile that grants access to the bucket must be attached to the EC2 instance running DataMasque.

Follow the steps below to configure your DataMasque to access your S3 bucket:

Open the Amazon IAM console and create an IAM policy. The following example IAM policy shows required permissions, where <bucket-name> should be replaced with the name of your S3 staging bucket. Sid attributes have been added to explain the purpose of each statement.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "BucketContentPermissions",
            "Effect": "Allow",
            "Action": [
                "s3:PutObject",
                "s3:GetObject",
                "s3:AbortMultipartUpload",
                "s3:DeleteObject",
                "s3:ListMultipartUploadParts"
            ],
            "Resource": "arn:aws:s3:::<bucket-name>/*"
        },
        {
            "Sid": "BucketMetaDataPermissions",
            "Effect": "Allow",
            "Action": [
                "s3:GetBucketPublicAccessBlock",
                "s3:GetBucketPolicyStatus",
                "s3:GetBucketObjectLockConfiguration",
                "s3:GetEncryptionConfiguration",
                "s3:ListBucketMultipartUploads",
                "s3:ListBucket",
                "s3:GetBucketAcl"
            ],
            "Resource": "arn:aws:s3:::<bucket-name>"
        }
    ]
}

Create an IAM role:
- Select AWS service as the trusted identity, EC2 as the service and EC2 under Select your use case.
- Attach the policy created from the previous step to the role.
Attach the Amazon IAM role created to the target DataMasque instance. You can use the following AWS command to attach an IAM role to an existing EC2 instance.

aws ec2 associate-iam-instance-profile --instance-id <ec2 instance id> --iam-instance-profile Name=<name of IAM role>

The AWS CLI associate-iam-instance-profile documentation has more information on this command.

Connection parameters

Amazon Redshift specific parameters

For Amazon Redshift databases, the following additional fields are required:


*S3 bucket name*	Name of the S3 bucket to UNLOAD to and COPY from.
*Amazon Redshift IAM Role*	ARN of an IAM Role that allows Amazon Redshift to access the S3 bucket.

Note: see the section Amazon Redshift additional configuration for details on these fields.

Note: an Amazon Redshift connection can also carry an IAM Role ARN for cross-account resource tagging, which is a different field with a different purpose — see Cross account resource tagging parameters below.

Amazon Redshift Advanced Settings

For Amazon Redshift databases, the following field is optional:


*Schema*	The name of the schema that DataMasque will operate on. If not specified, the database user's default schema will be used.

Encrypted connections

DataMasque supports encrypted connections to Amazon Redshift databases. Use the sslmode connection option to control the connection encryption. No additional configuration is required in DataMasque.

Important: DataMasque doesn't support sslmode=verify-ca or sslmode=verify-full for Amazon Redshift currently. This is on our roadmap and will be included in future releases.

Limitations

Amazon Redshift Limitations

from_unique_imitate is supported by Redshift connections (including on IDENTITY columns), but has the following limitations:

Values do not automatically cascade to child foreign key columns. Separate mask_table tasks should be created in the same ruleset to mask child FK columns. By using from_unique_imitate the integrity of the relationship will be retained because the child columns will have values replaced identically to the parent column's.
Columns with IDENTITY attributes will lose their ability to automatically assign a unique number to new inserts. Inserted values will be NULL.

Amazon Redshift Unsupported Features

The mask_unique_key task is not supported for Amazon Redshift connections.

A mask_table task is not supported on tables that are referenced by foreign keys, as the table can't be dropped and recreated due to breaking this reference.

Use of the where clause in mask_table tasks is not supported.

Important! When the new (masked) table is created, it will not contain the PK constraints, UK constraints or FK constraints of the source table (although Redshift does not enforce these constraints). NOT NULL constraints are also not retained. IDENTITY columns are recreated as INT columns and lose any sequences they had.

Supported data types

The following Amazon Redshift data types are supported.

Data type	Description
char(n)	Fixed-length character data of `n` size.
nvarchar(n)	Variable-length character data of `n` size.
numeric, decimal	Numerical value with decimal precision.
int8, bigint	64 bit integer value.
int, int4, integer	32 bit integer value.
int2, smallint	16 bit integer value.
float4, real	32 bit numerical value with 6 decimal digits of precision.
float8, double precision	64 bit numerical value with 15 decimal digits of precision.
bool, boolean	`true` or `false`.
date	Date value with no time.
timestamp	Timestamp including both a date and time.
timestamp with time zone	Timestamp including time zone.

Unsupported key columns

Amazon Redshift has the same unsupported key column types as PostgreSQL. See PostgreSQL connections -> Unsupported key columns.