Automated PII redaction for Amazon RDS for PostgreSQL audit logs

In this article, we delve into the intricacies of deploying a serverless pipeline designed to create an irreversibly redacted and queryable archive of Amazon RDS for PostgreSQL audit logs. This innovative solution effectively sanitizes sensitive information, such as Social Security numbers (SSNs), credit card details, email addresses, and other personally identifiable information (PII), before securely storing the clean logs in Amazon S3. The logs can then be queried through Amazon Athena. The entire deployment process is streamlined through a single AWS CloudFormation template.

The challenge arises from the use of pgAudit, which, when configured with pgaudit.log_parameter = on, logs every SQL statement in cleartext to Amazon CloudWatch Logs. This includes sensitive data that organizations must protect to comply with regulations such as GDPR, HIPAA, and PCI-DSS. While these audit logs are essential for compliance, the PII they contain must be safeguarded from unauthorized access.

To address this, CloudWatch Logs Data Protection offers a native, no-cost feature for masking PII within log groups. However, it is important to note that this masking is reversible; any IAM principal with the logs:Unmask permission can access the original data. For compliance frameworks that necessitate a permanent separation of PII from audit evidence, reversible masking proves inadequate. Our pipeline provides a solution by creating an irreversibly redacted copy stored in Amazon S3, ensuring that no permission, API call, or configuration change can restore the original values. This redacted archive is not only queryable via Amazon Athena but also shareable across accounts and can be retained for years through S3 lifecycle rules. We recommend utilizing both methods: enabling CloudWatch Logs Data Protection for live streams while employing this pipeline for permanent audit artifacts.

Solution overview

We offer a CloudFormation template that allows for customization and deployment of this solution.

Prerequisites

Before proceeding with the deployment, ensure the following prerequisites are met:

• AWS account – An active AWS account with permissions to create AWS CloudFormation stacks, IAM roles, VPCs, Amazon RDS instances, AWS Lambda functions, and Amazon S3 buckets (AdministratorAccess or equivalent).
• AWS CLI v2 – Installed and configured with valid credentials (aws configure). This is necessary for CLI and script deployment options. Refer to the install guide.
• AWS Region – A region that supports all services utilized in this template. Recommended regions include us-east-1, us-west-2, or eu-west-1.
• Service Quotas – Confirm that your account has the capacity for the resources this template will create.

Template parameters and deployment

The deployment requires only four parameters, all of which have sensible defaults, allowing for deployment without modification.

Parameter	Default	Description
ProjectName	rds-pii-redaction	Prefix for all resource names.
DBInstanceClass	db.t4g.micro	RDS instance size.
DBAllocatedStorage	20	Storage in GB (gp3). Range: 20–1000.
EnvironmentType	Single-AZ	Single-AZ – Single-AZ deployment. Multi-AZ – Multi-AZ deployment with deletion protection.
RawLogsBucketName	(auto-generated)	Custom S3 bucket name for raw logs. Leave empty for auto-generated.
RedactedLogsBucketName	(auto-generated)	Custom S3 bucket name for redacted logs. Leave empty for auto-generated.

Use existing VPC

Parameter	Default	Description
ExistingVPCId	(empty)	Your VPC ID. Leave empty to create a new one.
ExistingSubnet1 / ExistingSubnet2	(empty)	Private subnet IDs in two different AZs.
ExistingRDSSecurityGroupId	(empty)	Security group for RDS. Inbound 5432 from Lambda security group.
ExistingLambdaSecurityGroupId	(empty)	Security group for Lambda. Outbound 443 and 5432.

How to deploy – three options

The source code and CloudFormation template are available in this GitHub repository. You can choose from one of the following three deployment methods.

Option A: AWS console

1. Open the AWS CloudFormation console.
2. Select Create stack, then choose With new resources.
3. Upload template.yaml, and click Next.
4. For Stack name, enter rds-pii-redaction, then click Next twice.
5. Accept the IAM acknowledgment, and click Submit.
6. Wait approximately 15–20 minutes for the stack to deploy.

Option B: AWS CLI

aws cloudformation deploy 
    --template-file template.yaml 
    --stack-name rds-pii-redaction 
    --region us-east-1 
    --capabilities CAPABILITY_NAMED_IAM

Option C: Deploy script

chmod +x deploy.sh
./deploy.sh

Test and validate — PostgreSQL to Athena

Once the stack is deployed, retrieve your database endpoint and credentials to connect using your preferred PostgreSQL client. Follow these steps to generate PII test data, trigger the redaction pipeline, and verify the results in Athena.

Step 1: Get database credentials

# Get the RDS endpoint
RDS_ENDPOINT=$(aws cloudformation describe-stacks 
    --stack-name rds-pii-redaction 
    --query "Stacks[0].Outputs[?OutputKey=='RDSEndpoint'].OutputValue" 
    --output text)

# Get password from Secrets Manager
DB_PASSWORD=$(aws secretsmanager get-secret-value 
    --secret-id /rds/credentials 
    --query "SecretString" --output text | 
    python3 -c "import sys,json; print(json.load(sys.stdin)['password'])")

Step 2: Create table

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    full_name VARCHAR(100),
    email VARCHAR(100),
    phone VARCHAR(20),
    ssn VARCHAR(11),
    credit_card VARCHAR(19),
    address TEXT
);

Step 3: Insert records with PII

Insert test records using synthetic data to demonstrate the redaction pipeline. It is crucial to use fictitious data for testing purposes.

INSERT INTO customers (full_name, email, phone, ssn, credit_card, address) VALUES ('Test Record', 'test@example.com', '555-TEST-DATA', 'XXX-XX-XXXX', 'XXXX-XXXX-XXXX-XXXX', 'Test Location');

Step 4: Run queries that also contain PII

SELECT * FROM customers WHERE ssn = '[REDACTED_SSN]';
SELECT * FROM customers WHERE email = 'maria.garcia@company.org';
SELECT * FROM customers WHERE credit_card LIKE '4532%';

Step 5: Run the redaction pipeline

SF_ARN=$(aws cloudformation describe-stacks 
    --stack-name rds-pii-redaction 
    --query "Stacks[0].Outputs[?OutputKey=='StepFunctionArn'].OutputValue" 
    --output text)

aws stepfunctions start-execution 
    --state-machine-arn $SF_ARN 
    --input '{"lookback_hours": 24}'

Step 6: Monitor execution

aws stepfunctions list-executions 
    --state-machine-arn $SF_ARN --max-results 1 
    --query "executions[0].[status,startDate]" --output text

Wait for the status to show as SUCCEEDED (typically within 2–5 minutes).

Step 7: Verify S3 output

ACCT=$(aws sts get-caller-identity --query Account --output text)
aws s3 ls s3://rds-pii-redaction-raw-logs-$ACCT/rds-audit-logs/ --recursive | tail -5
aws s3 ls s3://rds-pii-redaction-redacted-logs-$ACCT/redacted-audit-logs/ --recursive | tail -5
aws s3 ls s3://rds-pii-redaction-redacted-logs-$ACCT/validation-reports/ --recursive | tail -5

Query redacted data in Athena — confirm PII is gone

After the pipeline execution, query the redacted archive in Athena to ensure that no PII remains.

One-time Athena setup

1. Open the Athena console.
2. Navigate to Settings, select Manage, and set the query result location to s3://rds-pii-redaction-redacted-logs-/athena-results/.
3. Select the rds-pii-redaction_audit_db database.

You should observe two tables:

• redacted_audit_events — containing the redacted SQL audit logs.
• validation_reports — displaying pass/fail reports.

The query result location is a standard requirement for Athena and is not specific to this template.

Athena queries

Execute the following queries to inspect the redacted data.

Query 1: Verify data

SELECT COUNT(*) as total_files FROM redacted_audit_events;

Query 2: View redacted INSERTs

chmod +x deploy.sh
./deploy.sh

0

Expected output:

chmod +x deploy.sh
./deploy.sh

1

Query 3: Confirm SSNs redacted

chmod +x deploy.sh
./deploy.sh

2

Query 4: Confirm credit cards redacted

chmod +x deploy.sh
./deploy.sh

3

Query 5: All redacted sensitive data

chmod +x deploy.sh
./deploy.sh

4

Query 6: PII leak check — verification
Must return zero rows:

chmod +x deploy.sh
./deploy.sh

5

Zero rows confirm that no pattern-detectable PII (SSNs, credit cards, email, phone numbers) survived in the redacted archive. For types detected by natural language processing (NLP), such as names and addresses, review the validation reports in Query 7. Consult your compliance team to determine whether the redacted copy meets your audit evidence requirements.

Query 7: Validation reports

chmod +x deploy.sh
./deploy.sh

6

Security note

It is important to note that unredacted PII remains in the source CloudWatch Logs log group. To enhance security, enable CloudWatch Logs Data Protection on those log groups as an additional layer of protection. Limit the logs:Unmask permission to a minimal set of principals. For further hardening measures (TLS, KMS, Lambda concurrency, IAM separation), refer to the Security Considerations section in the README.

Cleanup

To eliminate all resources and prevent incurring charges, empty the S3 buckets and delete the stack:

chmod +x deploy.sh
./deploy.sh

7

Wait for the deletion to complete:

chmod +x deploy.sh
./deploy.sh

8

All resources will be removed, ensuring no orphaned resources remain.

Cost considerations

The cost of the pipeline is influenced by the volume of Amazon Comprehend usage. At low activity levels (approximately 100 log events per hour), the expected monthly cost is around , primarily driven by infrastructure costs (NAT Gateway, VPC Endpoints). At higher activity levels (around 10,000 events per hour), Comprehend adds approximately per month, resulting in a total cost of about 1 monthly. While CloudWatch Logs Data Protection masks PII at no extra charge, it does not create an archive. The pipeline’s cost reflects the expense of achieving irreversibility. To optimize costs, consider reducing the frequency of the pipeline, removing VPC Interface Endpoints during development, or deploying within an existing VPC to share NAT Gateway costs.