Best-Practice Hardening: Separating Sensitive Data into Secure Tables

One of the most effective ways to protect sensitive information in any application is to physically separate it into its own database table with stricter security rules.

This approach ensures that even if your public-facing data is widely accessible, private details remain locked away (accessible only to authorised users).

1. Why Split Data Into Separate Tables?

The goal is to minimise accidental exposure. If sensitive columns live in the same table as public ones, a single overly-permissive query or view could leak private data.

By moving those fields into a dedicated private table with Row-Level Security (RLS) enabled (default-deny), you create a strong separation between what's public and what's private.

2. How It Works

1. Public table

   • Stores only safe-to-display fields.
   • Can have broader RLS policies (or even allow anonymous reads if appropriate).

2. Private table

   • Stores sensitive fields (e.g., personal contact details, financial information, internal notes).
   • RLS is enabled with no default policies (meaning nothing is accessible until you explicitly allow it).

3. Foreign key link

   • The private table references the public table's primary key.
   • Cascade deletes ensure private records are removed when the public record is deleted.

3. Hardening Best Practices

a) Default-deny and scope access

Enable RLS and write the narrowest possible policies, usually scoped by tenant, user, or role:

ALTER TABLE sensitive_data ENABLE ROW LEVEL SECURITY;
CREATE POLICY sensitive_read ON sensitive_data
FOR SELECT
USING (
  organisation_id = auth.jwt()->>'org_id'
);

b) Avoid accidental leaks via views

• Make public views safe by excluding private columns.
• Keep internal views in restricted schemas.

c) Use RPC or server functions for combined access

If a workflow needs both public and private fields:

• Expose an RPC or server function.
• Validate access server-side.
• Return only the specific fields required.

d) Separate highly sensitive data further

For example, move passwords, tokens, or secret keys into an even more restricted table with its own policies.

e) Control media access

• Public media: store in public buckets or serve with permissive policies.
• Private media: store in private buckets with signed URLs and short expiry.

f) Audit access

• Log queries to private tables.
• Monitor for unexpected access patterns.

g) Test like an attacker

• Try queries as an anonymous user and as an unauthorised user.
• Confirm private rows are never returned.

4. Benefits

• Reduces risk of accidental exposure through joins, views, or scaffolding.
• Keeps policies simple (you only need strict rules on the private tables).
• Improves clarity for developers (it's obvious what's safe and what's sensitive).

By isolating sensitive information into a private, RLS-protected table, you build a fail-safe layer into your data model. Even if a public endpoint is misconfigured, your most valuable and sensitive data stays secure.