Amazon Redshift is a massively parallel processing (MPP), fully managed petabyte-scale data warehouse that makes it simple and cost-effective to analyze all your data using existing business intelligence tools.

When businesses are modernizing their data warehousing solutions to Amazon Redshift, implementing additional data protection mechanisms for sensitive data, such as personally identifiable information (PII) or protected health information (PHI), is a common requirement, especially for those in highly regulated industries with strict data security and privacy mandates. Amazon Redshift provides role-based access control, row-level security, column-level security, and dynamic data masking, along with other database security features to enable organizations to enforce fine-grained data security.

Security-sensitive applications often require column-level (or field-level) encryption to enforce fine-grained protection of sensitive data on top of the default server-side encryption (namely data encryption at rest). In other words, sensitive data should be always encrypted on disk and remain encrypted in memory, until users with proper permissions request to decrypt the data. Column-level encryption provides an additional layer of security to protect your sensitive data throughout system processing so that only certain users or applications can access it. This encryption ensures that only authorized principals that need the data, and have the required credentials to decrypt it, are able to do so.

In this post, we demonstrate how you can implement your own column-level encryption mechanism in Amazon Redshift using AWS Glue to encrypt sensitive data before loading data into Amazon Redshift, and using AWS Lambda as a user-defined function (UDF) in Amazon Redshift to decrypt the data using standard SQL statements. Lambda UDFs can be written in any of the programming languages supported by Lambda, such as Java, Go, PowerShell, Node.js, C#, Python, Ruby, or a custom runtime. You can use Lambda UDFs in any SQL statement such as SELECT, UPDATE, INSERT, or DELETE, and in any clause of the SQL statements where scalar functions are allowed.

Solution overview

The following diagram describes the solution architecture.

To illustrate how to set up this architecture, we walk you through the following steps:

1. We upload a sample data file containing synthetic PII data to an Amazon Simple Storage Service (Amazon S3) bucket.
2. A sample 256-bit data encryption key is generated and securely stored using AWS Secrets Manager.
3. An AWS Glue job reads the data file from the S3 bucket, retrieves the data encryption key from Secrets Manager, performs data encryption for the PII columns, and loads the processed dataset into an Amazon Redshift table.
4. We create a Lambda function to reference the same data encryption key from Secrets Manager, and implement data decryption logic for the received payload data.
5. The Lambda function is registered as a Lambda UDF with a proper AWS Identity and Access Management (IAM) role that the Amazon Redshift cluster is authorized to assume.
6. We can validate the data decryption functionality by issuing sample queries using Amazon Redshift Query Editor v2.0. You may optionally choose to test it with your own SQL client or business intelligence tools.

Prerequisites

To deploy the solution, make sure to complete the following prerequisites:

• Have an AWS account. For this post, you configure the required AWS resources using AWS CloudFormation in the us-east-2 Region.
• Have an IAM user with permissions to manage AWS resources including Amazon S3, AWS Glue, Amazon Redshift, Secrets Manager, Lambda, and AWS Cloud9.

Deploy the solution using AWS CloudFormation

Provision the required AWS resources using a CloudFormation template by completing the following steps:

1. Sign in to your AWS account.
2. Choose Launch Stack:
   
3. Navigate to an AWS Region (for example, us-east-2).
4. For Stack name, enter a name for the stack or leave as default (aws-blog-redshift-column-level-encryption).
5. For RedshiftMasterUsername, enter a user name for the admin user account of the Amazon Redshift cluster or leave as default (master).
6. For RedshiftMasterUserPassword, enter a strong password for the admin user account of the Amazon Redshift cluster.
7. Select I acknowledge that AWS CloudFormation might create IAM resources.
8. Choose Create stack.
   

The CloudFormation stack creation process takes around 5–10 minutes to complete.

9. When the stack creation is complete, on the stack Outputs tab, record the values of the following:
   1. AWSCloud9IDE
   2. AmazonS3BucketForDataUpload
   3. IAMRoleForRedshiftLambdaUDF
   4. LambdaFunctionName

Upload the sample data file to Amazon S3

To test the column-level encryption capability, you can download the sample synthetic data generated by Mockaroo. The sample dataset contains synthetic PII and sensitive fields such as phone number, email address, and credit card number. In this post, we demonstrate how to encrypt the credit card number field, but you can apply the same method to other PII fields according to your own requirements.

An AWS Cloud9 instance is provisioned for you during the CloudFormation stack setup. You may access the instance from the AWS Cloud9 console, or by visiting the URL obtained from the CloudFormation stack output with the key AWSCloud9IDE.

On the AWS Cloud9 terminal, copy the sample dataset to your S3 bucket by running the following command:

S3\_BUCKET=$(aws s3 ls| awk '{print $3}'| grep awsblog-pii-data-input-)aws s3 cp s3://aws-blogs-artifacts-public/artifacts/BDB-2274/pii-sample-dataset.csv s3://$S3\_BUCKET/

Generate a secret and secure it using Secrets Manager

We generate a 256-bit secret to be used as the data encryption key. Complete the following steps:

1. Create a new file in the AWS Cloud9 environment.
   
2. Enter the following code snippet. We use the cryptography package to create a secret, and use the AWS SDK for Python (Boto3) to securely store the secret value with Secrets Manager:

   from cryptography.fernet import Fernetimport boto3import base64key = Fernet.generate\_key()client = boto3.client('secretsmanager')response = client.create\_secret( Name='data-encryption-key', SecretBinary=base64.urlsafe\_b64decode(key))print(response['ARN'])

3. Save the file with the file name generate\_secret.py (or any desired name ending with .py).
   
4. Install the required packages by running the following pip install command in the terminal:

   pip install --user boto3pip install --user cryptography

5. Run the Python script via the following command to generate the secret:

   python generate\_secret.py

   

Create a target table in Amazon Redshift

A single-node Amazon Redshift cluster is provisioned for you during the CloudFormation stack setup. To create the target table for storing the dataset with encrypted PII columns, complete the following steps:

1. On the Amazon Redshift console, navigate to the list of provisioned clusters, and choose your cluster.
   
2. To connect to the cluster, on the Query data drop-down menu, choose Query in query editor v2.
   
3. If this is the first time you’re using the Amazon Redshift Query Editor V2, accept the default setting by choosing Configure account.
   
4. To connect to the cluster, choose the cluster name.
   
5. For Database, enter demodb.
6. For User name, enter master.
7. For Password, enter your password.

You may need to change the user name and password according to your CloudFormation settings.

8. Choose Create connection.
   
9. In the query editor, run the following DDL command to create a table named pii\_table:

   CREATE TABLE pii\_table( id BIGINT, full\_name VARCHAR(50), gender VARCHAR(10), job\_title VARCHAR(50), spoken\_language VARCHAR(50), contact\_phone\_number VARCHAR(20), email\_address VARCHAR(50), registered\_credit\_card VARCHAR(50));

We recommend using the smallest possible column size as a best practice, and you may need to modify these table definitions per your specific use case. Creating columns much larger than necessary will have an impact on the size of data tables and affect query performance.

Create the source and destination Data Catalog tables in AWS Glue

The CloudFormation stack provisioned two AWS Glue data crawlers: one for the Amazon S3 data source and one for the Amazon Redshift data source. To run the crawlers, complete the following steps:

1. On the AWS Glue console, choose Crawlers in the navigation pane.
   
2. Select the crawler named glue-s3-crawler, then choose Run crawler to trigger the crawler job.
   
3. Select the crawler named glue-redshift-crawler, then choose Run crawler.
   

When the crawlers are complete, navigate to the Tables page to verify your results. You should see two tables registered under the demodb database.

Author an AWS Glue ETL job to perform data encryption

An AWS Glue job is provisioned for you as part of the CloudFormation stack setup, but the extract, transform, and load (ETL) script has not been created. We create and upload the ETL script to the /glue-script folder under the provisioned S3 bucket in order to run the AWS Glue job.

1. Return to your AWS Cloud9 environment either via the AWS Cloud9 console, or by visiting the URL obtained from the CloudFormation stack output with the key AWSCloud9IDE.
   

We use the Miscreant package for implementing a deterministic encryption using the AES-SIV encryption algorithm, which means that for any given plain text value, the generated encrypted value will be always the same. The benefit of using this encryption approach is to allow for point lookups, equality joins, grouping, and indexing on encrypted columns. However, you should also be aware of the potential security implication when applying deterministic encryption to low-cardinality data, such as gender, boolean values, and status flags.

2. Create a new file in the AWS Cloud9 environment and enter the following code snippet:

   import sysfrom awsglue.transforms import *from awsglue.utils import getResolvedOptionsfrom pyspark.context import SparkContextfrom awsglue.context import GlueContextfrom awsglue.job import Jobfrom awsglue.dynamicframe import DynamicFrameCollectionfrom awsglue.dynamicframe import DynamicFrameimport boto3import base64from miscreant.aes.siv import SIVfrom pyspark.sql.functions import udf, colfrom pyspark.sql.types import StringTypeargs = getResolvedOptions(sys.argv, ["JOB\_NAME", "SecretName", "InputTable"])sc = SparkContext()glueContext = GlueContext(sc)spark = glueContext.spark\_sessionjob = Job(glueContext)job.init(args["JOB\_NAME"], args)# retrieve the data encryption key from Secrets Managersecret\_name = args["SecretName"]sm\_client = boto3.client('secretsmanager')get\_secret\_value\_response = sm\_client.get\_secret\_value(SecretId = secret\_name)data\_encryption\_key = get\_secret\_value\_response['SecretBinary']siv = SIV(data\_encryption\_key) # Without nonce, the encryption becomes deterministic# define the data encryption functiondef pii\_encrypt(value): if value is None: value = "" ciphertext = siv.seal(value.encode()) return base64.b64encode(ciphertext).decode('utf-8')# register the data encryption function as Spark SQL UDF udf\_pii\_encrypt = udf(lambda z: pii\_encrypt(z), StringType())# define the Glue Custom Transform functiondef Encrypt\_PII (glueContext, dfc) -> DynamicFrameCollection: newdf = dfc.select(list(dfc.keys())[0]).toDF() # PII fields to be encrypted pii\_col\_list = ["registered\_credit\_card"] for pii\_col\_name in pii\_col\_list: newdf = newdf.withColumn(pii\_col\_name, udf\_pii\_encrypt(col(pii\_col\_name))) encrypteddyc = DynamicFrame.fromDF(newdf, glueContext, "encrypted\_data") return (DynamicFrameCollection({"CustomTransform0": encrypteddyc}, glueContext))# Script generated for node S3 bucketS3bucket\_node1 = glueContext.create\_dynamic\_frame.from\_catalog( database="demodb", table\_name=args["InputTable"], transformation\_ctx="S3bucket\_node1",)# Script generated for node ApplyMappingApplyMapping\_node2 = ApplyMapping.apply( frame=S3bucket\_node1, mappings=[ ("id", "long", "id", "long"), ("full\_name", "string", "full\_name", "string"), ("gender", "string", "gender", "string"), ("job\_title", "string", "job\_title", "string"), ("spoken\_language", "string", "spoken\_language", "string"), ("contact\_phone\_number", "string", "contact\_phone\_number", "string"), ("email\_address", "string", "email\_address", "string"), ("registered\_credit\_card", "long", "registered\_credit\_card", "string"), ], transformation\_ctx="ApplyMapping\_node2",)# Custom TransformCustomtransform\_node = Encrypt\_PII(glueContext, DynamicFrameCollection({"ApplyMapping\_node2": ApplyMapping\_node2}, glueContext))# Script generated for node Redshift ClusterRedshiftCluster\_node3 = glueContext.write\_dynamic\_frame.from\_catalog( frame=Customtransform\_node, database="demodb", table\_name="demodb\_public\_pii\_table", redshift\_tmp\_dir=args["TempDir"], transformation\_ctx="RedshiftCluster\_node3",)job.commit()

3. Save the script with the file name pii-data-encryption.py.
   
4. Copy the script to the desired S3 bucket location by running the following command:

   S3\_BUCKET=$(aws s3 ls| awk '{print $3}'| grep awsblog-pii-data-input-)aws s3 cp pii-data-encryption.py s3://$S3\_BUCKET/glue-script/pii-data-encryption.py

   

5. To verify the script is uploaded successfully, navigate to the Jobs page on the AWS Glue console.You should be able to find a job named pii-data-encryption-job.
   
6. Choose Run to trigger the AWS Glue job.It will first read the source data from the S3 bucket registered in the AWS Glue Data Catalog, then apply column mappings to transform data into the expected data types, followed by performing PII fields encryption, and finally loading the encrypted data into the target Redshift table. The whole process should be completed within 5 minutes for this sample dataset.You can switch to the Runs tab to monitor the job status.
   

Configure a Lambda function to perform data decryption

A Lambda function with the data decryption logic is deployed for you during the CloudFormation stack setup. You can find the function on the Lambda console.

The following is the Python code used in the Lambda function:

import boto3import osimport jsonimport base64import loggingfrom miscreant.aes.siv import SIVlogger = logging.getLogger()logger.setLevel(logging.INFO)secret\_name = os.environ['DATA\_ENCRYPT\_KEY']sm\_client = boto3.client('secretsmanager')get\_secret\_value\_response = sm\_client.get\_secret\_value(SecretId = secret\_name)data\_encryption\_key = get\_secret\_value\_response['SecretBinary']siv = SIV(data\_encryption\_key) # Without nonce, the encryption becomes deterministic# define lambda function logicdef lambda\_handler(event, context): ret = dict() res = [] for argument in event['arguments']: encrypted\_value = argument[0] try: de\_val = siv.open(base64.b64decode(encrypted\_value)) # perform decryption except: de\_val = encrypted\_value logger.warning('Decryption for value failed: ' + str(encrypted\_value)) res.append(json.dumps(de\_val.decode('utf-8'))) ret['success'] = True ret['results'] = res return json.dumps(ret) # return decrypted results

If you want to deploy the Lambda function on your own, make sure to include the Miscreant package in your deployment package.

Register a Lambda UDF in Amazon Redshift

You can create Lambda UDFs that use custom functions defined in Lambda as part of your SQL queries. Lambda UDFs are managed in Lambda, and you can control the access privileges to invoke these UDFs in Amazon Redshift.

1. Navigate back to the Amazon Redshift Query Editor V2 to register the Lambda UDF.
2. Use the CREATE EXTERNAL FUNCTION command and provide an IAM role that the Amazon Redshift cluster is authorized to assume and make calls to Lambda:

   CREATE OR REPLACE EXTERNAL FUNCTION pii\_decrypt (value varchar(max))RETURNS varchar STABLELAMBDA '<--Replace-with-your-lambda-function-name-->'IAM\_ROLE '<--Replace-with-your-redshift-lambda-iam-role-arn-->';

You can find the Lambda name and Amazon Redshift IAM role on the CloudFormation stack Outputs tab:

• LambdaFunctionName
• IAMRoleForRedshiftLambdaUDF

Validate the column-level encryption functionality in Amazon Redshift

By default, permission to run new Lambda UDFs is granted to PUBLIC. To restrict usage of the newly created UDF, revoke the permission from PUBLIC and then grant the privilege to specific users or groups. To learn more about Lambda UDF security and privileges, see Managing Lambda UDF security and privileges.

You must be a superuser or have the sys:secadmin role to run the following SQL statements:

GRANT SELECT ON "demodb"."public"."pii\_table" TO PUBLIC;CREATE USER regular\_user WITH PASSWORD '1234Test!';CREATE USER privileged\_user WITH PASSWORD '1234Test!';REVOKE EXECUTE ON FUNCTION pii\_decrypt(varchar) FROM PUBLIC;GRANT EXECUTE ON FUNCTION pii\_decrypt(varchar) TO privileged\_user;

First, we run a SELECT statement to verify that our highly sensitive data field, in this case the registered\_credit\_card column, is now encrypted in the Amazon Redshift table:

SELECT * FROM "demodb"."public"."pii\_table";

For regular database users who have not been granted the permission to use the Lambda UDF, they will see a permission denied error when they try to use the pii\_decrypt() function:

SET SESSION AUTHORIZATION regular\_user;SELECT *, pii\_decrypt(registered\_credit\_card) AS decrypted\_credit\_card FROM "demodb"."public"."pii\_table";

For privileged database users who have been granted the permission to use the Lambda UDF for decrypting the data, they can issue a SQL statement using the pii\_decrypt() function:

SET SESSION AUTHORIZATION privileged\_user;SELECT *, pii\_decrypt(registered\_credit\_card) AS decrypted\_credit\_card FROM "demodb"."public"."pii\_table";

The original registered\_credit\_card values can be successfully retrieved, as shown in the decrypted\_credit\_card column.

Cleaning up

To avoid incurring future charges, make sure to clean up all the AWS resources that you created as part of this post.

You can delete the CloudFormation stack on the AWS CloudFormation console or via the AWS Command Line Interface (AWS CLI). The default stack name is aws-blog-redshift-column-level-encryption.

Conclusion

In this post, we demonstrated how to implement a custom column-level encryption solution for Amazon Redshift, which provides an additional layer of protection for sensitive data stored on the cloud data warehouse. The CloudFormation template gives you an easy way to set up the data pipeline, which you can further customize for your specific business scenarios. You can also modify the AWS Glue ETL code to encrypt multiple data fields at the same time, and to use different data encryption keys for different columns for enhanced data security. With this solution, you can limit the occasions where human actors can access sensitive data stored in plain text on the data warehouse.

You can learn more about this solution and the source code by visiting the GitHub repository. To learn more about how to use Amazon Redshift UDFs to solve different business problems, refer to Example uses of user-defined functions (UDFs) and Amazon Redshift UDFs.

About the Author

Aaron Chong is an Enterprise Solutions Architect at Amazon Web Services Hong Kong. He specializes in the data analytics domain, and works with a wide range of customers to build big data analytics platforms, modernize data engineering practices, and advocate AI/ML democratization.