中文
devexamples
← Recipes

阅读中文版本

Mask Sensitive Columns with a View

Expose masked columns through a view for support and analytics roles, grant access to the view instead of the table, and keep the raw table restricted.

~35 minsecuritypostgresqlprivacy

Task

Give support and analytics read-only access to masked data without touching the base table.

When to use this

Support and analytics need to see records, but granting table access exposes full PII that they do not need.

Prerequisites

  • PostgreSQL views and roles
  • A defined masking rule per sensitive column

Steps

1. Create a view that selects non-sensitive columns and masks or partially masks the rest.

Expected output: Sensitive values are not visible in full.

2. Grant SELECT on the view to the support and analytics roles only.

Expected output: Those roles never need table privileges.

3. Revoke direct table access from those roles.

Expected output: The raw table stays restricted.

4. Document the masking and verify it with a query as the restricted role.

psql -c "GRANT SELECT ON customers_masked TO support_role; REVOKE SELECT ON customers FROM support_role;"

Expected output: Masking is provable, not assumed.

Troubleshooting

Problem

Masking bypassed via the base table

Solution

Revoke table privileges; a view is only a boundary if the underlying table is not directly readable.

Problem

Masking too weak

Solution

Mask enough to prevent re-identification, not just the last digits of a single field.

Limitations

  • A masking view protects reads through the view; it does not change what the application itself stores.
  • Some masking needs row-level context, which requires a function rather than a static view.

Context

Give support and analytics read-only access to masked data without touching the base table. Support and analytics need to see records, but granting table access exposes full PII that they do not need.

Steps

  1. Create a view that selects non-sensitive columns and masks or partially masks the rest. Expected: Sensitive values are not visible in full.
  2. Grant SELECT on the view to the support and analytics roles only. Expected: Those roles never need table privileges.
  3. Revoke direct table access from those roles. Expected: The raw table stays restricted.
  4. Document the masking and verify it with a query as the restricted role. Expected: Masking is provable, not assumed.
CREATE VIEW customers_masked AS
SELECT id, tenant_id,
  left(email, 1) || '***@' || split_part(email, '@', 2) AS email,
  regexp_replace(phone, '[0-9]', '*', 'g') AS phone
FROM customers;
REVOKE SELECT ON customers FROM support_role;
GRANT SELECT ON customers_masked TO support_role;

Verify

  • Sensitive values are not visible in full.
  • Those roles never need table privileges.
  • The raw table stays restricted.
  • Masking is provable, not assumed.

Limits

  • A masking view protects reads through the view; it does not change what the application itself stores.
  • Some masking needs row-level context, which requires a function rather than a static view.