Automated PII redaction for Amazon RDS for PostgreSQL audit logs

In this post, we show you how to deploy a serverless pipeline that creates an irreversibly redacted, queryable archive of Amazon Relational Database Service (Amazon RDS) for PostgreSQL audit logs. The pipeline permanently removes Social Security numbers (SSNs), credit cards, email, names, and more than 30 types of personally identifiable information (PII) before storing clean logs in Amazon Simple Storage Service (Amazon S3) for query through Amazon Athena. The entire solution deploys from a single AWS CloudFormation template.

The challenge: pgAudit with pgaudit.log_parameter = on writes every SQL statement into Amazon CloudWatch Logs as cleartext. This includes sensitive data such as names, Social Security numbers, credit card numbers, and email addresses. Organizations subject to GDPR, HIPAA, or PCI-DSS need these audit logs for compliance, but the PII within them must not be accessible to every team that consumes the logs.

CloudWatch Logs Data Protection is the native, zero-cost feature for masking PII in log groups. Enable it as your first line of defense. However, its masking is reversible: any IAM principal with the logs:Unmask permission can view the original data. For compliance frameworks that require permanent separation of PII from audit evidence, reversible masking is insufficient. This pipeline creates an irreversibly redacted copy in Amazon S3. No permission, API call, or configuration change can recover the original values. The redacted archive is queryable through Amazon Athena, shareable cross-account, and retainable for years through S3 lifecycle rules. Our recommendation: enable both. Use CloudWatch Logs Data Protection for your live stream, and this pipeline for the permanent audit artifact.

Solution overview

We provide a CloudFormation template you can use to customize and deploy this solution.

Prerequisites

Before deploying, make sure you have the following in place:

• AWS account – An active AWS account with permissions to create AWS CloudFormation stacks, AWS Identity and Access Management (IAM) roles, virtual private clouds (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). Required for the CLI and script deployment options. Install guide.
• AWS Region – A Region that supports all services used in this template. Recommended: us-east-1, us-west-2, or eu-west-1.
• Service Quotas – Verify your account has capacity for the resources this template creates.

Template parameters and deployment

Only four parameters, all with sensible defaults. Deploy without changing anything.

Use existing VPC

How to deploy – three options

You can find the source code and CloudFormation template on this GitHub repository. Choose one of the following three deployment methods.

Option A: AWS console

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

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

After the stack is deployed, get your database endpoint and credentials and connect using your preferred PostgreSQL client. Then run the following 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 <stack-name>/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. Important: Always use fictitious data for testing.

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 status: SUCCEEDED (typically 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 runs, query the redacted archive in Athena to confirm that no PII remains.

One-time Athena setup

1. Open the Athena console.
2. Choose Settings, choose Manage, and then set the query result location to s3://rds-pii-redaction-redacted-logs-<YOUR_ACCOUNT_ID>/athena-results/.
3. Select the rds-pii-redaction_audit_db database.

You should see two tables:

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

The query result location is a standard Athena requirement. It isn’t specific to this template.

Athena queries

Run 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

SELECT evt.message
FROM redacted_audit_events
CROSS JOIN UNNEST(events) AS t(evt)
WHERE evt.message LIKE '%INSERT%customers%'
LIMIT 10;

Expected output:

AUDIT: SESSION,3,1,WRITE,DML,TABLE,public.customers,
"INSERT INTO customers (full_name, email, phone, ssn, credit_card, address) VALUES
('[REDACTED_NAME]', '[REDACTED_EMAIL]', '[REDACTED_PHONE]',
'[REDACTED_SSN]', '[REDACTED_CREDIT_CARD]', '[REDACTED_ADDRESS]')"

Query 3: Confirm SSNs redacted

SELECT evt.message
FROM redacted_audit_events
CROSS JOIN UNNEST(events) AS t(evt)
WHERE evt.message LIKE '%REDACTED_SSN%'
LIMIT 20;

Query 4: Confirm credit cards redacted

SELECT evt.message
FROM redacted_audit_events
CROSS JOIN UNNEST(events) AS t(evt)
WHERE evt.message LIKE '%REDACTED_CREDIT_CARD%'
LIMIT 20;

Query 5: All redacted sensitive data

SELECT evt.message
FROM redacted_audit_events
CROSS JOIN UNNEST(events) AS t(evt)
WHERE evt.message LIKE '%REDACTED_SSN%'
OR evt.message LIKE '%REDACTED_CREDIT_CARD%'
OR evt.message LIKE '%REDACTED_EMAIL%'
OR evt.message LIKE '%REDACTED_PHONE%'
OR evt.message LIKE '%REDACTED_NAME%'
LIMIT 30;

Query 6: PII leak check — verification

Must return zero rows:

SELECT evt.message
FROM redacted_audit_events
CROSS JOIN UNNEST(events) AS t(evt)
WHERE regexp_like(evt.message, '\d{3}-\d{2}-\d{4}')
OR regexp_like(evt.message, '\d{4}-\d{4}-\d{4}-\d{4}')
OR regexp_like(evt.message, '[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}')
OR regexp_like(evt.message, '\(\d{3}\)\s?\d{3}-\d{4}')
OR regexp_like(evt.message, '\d{3}-\d{3}-\d{4}')
LIMIT 10;

Zero rows confirms 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 about whether a redacted copy satisfies your audit evidence requirements.

Query 7: Validation reports

SELECT source_key, total_events, violations, status
FROM validation_reports
LIMIT 10;

Security note

Unredacted PII remains in the source CloudWatch Logs log group. Enable CloudWatch Logs Data Protection on those log groups as an additional layer. Restrict the logs:Unmask permission to a minimal set of principals. For additional hardening (TLS, KMS, Lambda concurrency, IAM separation), see the Security Considerations section in the README.

Cleanup

To remove all resources and stop incurring charges, empty the S3 buckets and delete the stack:

ACCOUNT_ID=$(aws sts get-caller-identity --query Account --output text)
# Empty S3 buckets (required before stack deletion)
aws s3 rm s3://rds-pii-redaction-raw-logs-$ACCOUNT_ID --recursive
aws s3 rm s3://rds-pii-redaction-redacted-logs-$ACCOUNT_ID --recursive

Delete the stack:

aws cloudformation delete-stack --stack-name rds-pii-redaction

Wait for the deletion to complete:

aws cloudformation wait stack-delete-complete --stack-name rds-pii-redaction

All 45 resources are removed. No orphaned resources remain.

Cost considerations

Pipeline cost depends on Amazon Comprehend volume. At low activity (100 log events/hour), expect approximately $73/month, dominated by infrastructure (NAT Gateway, VPC Endpoints). At high activity (10,000 events/hour), Comprehend adds approximately $70/month for a total of approximately $161/month. CloudWatch Logs Data Protection masks PII at no additional charge but produces no archive. The pipeline cost is the cost of irreversibility. To optimize: reduce pipeline frequency, remove VPC Interface Endpoints in development, or deploy into an existing VPC to share NAT Gateway costs.

Conclusion

In this post, we showed how to deploy an automated PII redaction pipeline for Amazon RDS for PostgreSQL audit logs using AWS Step Functions, Amazon Comprehend, and a single AWS CloudFormation template. Unlike reversible masking, the redacted archive cannot be unmasked by any IAM permission. This makes it suitable for external audit handoff, cross-account sharing, and long-term retention. For immediate protection of your live log stream, enable CloudWatch Logs Data Protection. For a permanent, queryable, auditor-safe archive, deploy this pipeline alongside it.

About the authors