PostgreSQL RLS for Dynamic Data Masking in Multi-Tenant SaaS

The Fallacy of Application-Layer Data Masking

In any non-trivial multi-tenant SaaS platform, the need for role-based access to data is a given. But a more nuanced requirement quickly emerges: different roles often need to see the same record but with varying levels of data sensitivity. An account administrator might need to see a user's full email address, [email protected], while a customer support agent should only see a masked version, j*.d@example.com, to resolve a ticket without exposing PII. A BI tool, on the other hand, might only need to know the email's domain.

The common, and dangerously naive, approach is to implement this logic in the application layer. A service method might look something like this (in pseudocode):

function getUserData(userId, requestingUser) {
  const userRecord = db.findUser(userId);
  
  if (requestingUser.role === 'support') {
    userRecord.email = maskEmail(userRecord.email);
    userRecord.phone = maskPhone(userRecord.phone);
  }
  
  return userRecord;
}

This pattern is a ticking time bomb. It's fragile, decentralized, and prone to error. Every new API endpoint, every microservice, every background job that touches this data must meticulously reimplement or call this masking logic. A single developer mistake can lead to a catastrophic data leak. The database, as the ultimate source of truth, should be the ultimate enforcer of these policies.

This article details a production-grade pattern for enforcing dynamic, role-based data masking directly within PostgreSQL. We will leverage a powerful combination of Row-Level Security (RLS), custom PL/pgSQL functions, and SECURITY BARRIER views to build a system that is secure, centralized, and performant.

Foundational Schema: A Multi-Tenant SaaS Model

Let's start with a representative schema. We have tenants (organizations), users who belong to those tenants, and roles assigned to those users. The sensitive_pii table holds the data we need to protect.

-- Enable the pgcrypto extension for UUID generation
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- Tenants represent customer organizations
CREATE TABLE tenants (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Users belong to a tenant and have a specific role
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    email TEXT NOT NULL UNIQUE,
    role TEXT NOT NULL CHECK (role IN ('admin', 'support', 'member')),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- The table containing sensitive PII data
CREATE TABLE sensitive_pii (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    owner_user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    full_name TEXT NOT NULL,
    email_address TEXT NOT NULL,
    phone_number TEXT,
    national_id TEXT, -- e.g., SSN, Passport Number
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Create indexes crucial for RLS performance
CREATE INDEX idx_users_tenant_id ON users(tenant_id);
CREATE INDEX idx_sensitive_pii_tenant_id ON sensitive_pii(tenant_id);
CREATE INDEX idx_sensitive_pii_owner_user_id ON sensitive_pii(owner_user_id);

This schema establishes the core multi-tenancy model. The tenant_id column is the partition key for our data, and the users.role column will drive our masking logic.

Step 1: Creating Immutable Masking Functions

The core of our masking logic will reside in custom PL/pgSQL functions. A critical performance consideration is to declare these functions as IMMUTABLE. This tells the PostgreSQL query planner that the function will always return the same result for the same input arguments and that it has no side effects. This allows the planner to pre-calculate the function's result and potentially cache it, significantly reducing overhead in large queries.

-- A function to mask an email address
CREATE OR REPLACE FUNCTION mask_email(email TEXT) 
RETURNS TEXT AS $$
BEGIN
    RETURN regexp_replace(email, '(?<=.).(?=[^@]*?.@)', '*', 'g');
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- A function to mask a phone number, showing only the last 4 digits
CREATE OR REPLACE FUNCTION mask_phone_number(phone TEXT) 
RETURNS TEXT AS $$
BEGIN
    IF phone IS NULL OR length(phone) < 4 THEN
        RETURN '****';
    END IF;
    RETURN '***-***-' || substr(phone, -4);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- A function for highly sensitive data like a national ID
CREATE OR REPLACE FUNCTION mask_national_id(nid TEXT)
RETURNS TEXT AS $$
BEGIN
    IF nid IS NULL THEN
        RETURN NULL;
    END IF;
    RETURN '***-**-' || substr(nid, -4);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- A fully redacting function
CREATE OR REPLACE FUNCTION redact_value(val TEXT)
RETURNS TEXT AS $$
BEGIN
    RETURN 'REDACTED';
END;
$$ LANGUAGE plpgsql IMMUTABLE;

Advanced Note: While IMMUTABLE is ideal, if your function depended on a configuration table or current_setting, you would have to use STABLE, which tells the planner the function's result is consistent within a single scan. For pure string manipulation, IMMUTABLE is correct and offers the best performance.

Step 2: Establishing Secure Session Context

For RLS policies to make decisions, they need context about the user making the query. The most robust and secure way to pass this information from your application to PostgreSQL is via runtime configuration parameters set for the duration of a transaction.

Your application's connection pool or transaction management logic should execute these SET LOCAL commands immediately after acquiring a connection and before running any user-facing queries.

-- In your application code, before a query:
-- BEGIN;
-- SET LOCAL app.current_user_id = '...'; -- UUID of the logged-in user
-- SET LOCAL app.current_tenant_id = '...'; -- UUID of the user's tenant
-- SELECT * FROM sensitive_pii WHERE ...;
-- COMMIT;

Using SET LOCAL is critical. It scopes the variable to the current transaction, preventing state from leaking between different requests that might share the same database connection from a pool. We can now create helper functions in the database to access these settings in a clean way.

CREATE OR REPLACE FUNCTION current_user_id() 
RETURNS UUID AS $$
BEGIN
    RETURN current_setting('app.current_user_id', true)::UUID;
EXCEPTION
    WHEN OTHERS THEN
        RETURN NULL;
END;
$$ LANGUAGE plpgsql STABLE;

CREATE OR REPLACE FUNCTION current_tenant_id() 
RETURNS UUID AS $$
BEGIN
    RETURN current_setting('app.current_tenant_id', true)::UUID;
EXCEPTION
    WHEN OTHERS THEN
        RETURN NULL;
END;
$$ LANGUAGE plpgsql STABLE;

The true parameter in current_setting makes it return NULL instead of throwing an error if the setting is not defined, which is a graceful way to handle unauthenticated sessions.

Step 3: The Superior Pattern - Security Barrier Views for Masking

One might be tempted to cram all the masking logic directly into an RLS policy's USING clause. This is an anti-pattern. RLS policies are primarily designed for filtering rows, not for transforming column values. Doing so leads to convoluted, hard-to-maintain policies and can even have security implications.

The architecturally superior solution is to decouple row visibility from column-level data masking. We achieve this with a SECURITY BARRIER view.

Why SECURITY BARRIER? When a view has this property, RLS policies on the underlying base tables are applied before any functions or operators in the view's definition are evaluated. This prevents side-channel attacks where a malicious user could craft inputs to functions within the view that leak information from rows they are not supposed to see. It's a critical safeguard.

Let's create the view:

CREATE OR REPLACE VIEW vw_sensitive_pii
WITH (security_barrier = true)
AS
SELECT
    pii.id,
    pii.tenant_id,
    pii.owner_user_id,
    pii.full_name, -- Let's assume full name is not masked for simplicity
    CASE
        WHEN u.role = 'admin' THEN pii.email_address
        WHEN u.role = 'support' THEN mask_email(pii.email_address)
        ELSE redact_value(pii.email_address)
    END AS email_address,
    CASE
        WHEN u.role = 'admin' THEN pii.phone_number
        WHEN u.role = 'support' THEN mask_phone_number(pii.phone_number)
        ELSE redact_value(pii.phone_number)
    END AS phone_number,
    CASE
        WHEN u.role = 'admin' THEN pii.national_id
        ELSE redact_value(pii.national_id)
    END AS national_id,
    pii.created_at
FROM
    sensitive_pii pii
JOIN
    -- This join fetches the role of the *current* user making the query
    users u ON u.id = current_user_id();

This view is the heart of our pattern. It elegantly encodes the business logic for data masking. Notice how it joins against the users table on current_user_id() to fetch the role of the querying user, not the owner of the data row. This is what makes the masking dynamic.

Step 4: Implementing RLS Policies on the Base Table

With the masking logic encapsulated in the view, our RLS policies on the sensitive_pii table become clean and focused solely on row visibility.

First, enable RLS on the table:

ALTER TABLE sensitive_pii ENABLE ROW LEVEL SECURITY;
-- Force RLS for the table owner as well, a good security practice
ALTER TABLE sensitive_pii FORCE ROW LEVEL SECURITY;

Now, create the policy for tenant isolation. This policy ensures that any query against sensitive_pii (even those coming through our view) can only ever see rows belonging to the user's current tenant.

CREATE POLICY tenant_isolation_policy ON sensitive_pii
AS PERMISSIVE -- PERMISSIVE means policies are combined with OR
FOR ALL -- Applies to SELECT, INSERT, UPDATE, DELETE
USING (tenant_id = current_tenant_id())
WITH CHECK (tenant_id = current_tenant_id());

* USING (tenant_id = current_tenant_id()): This is the filter for read operations (SELECT). It's equivalent to the database appending WHERE tenant_id = '...' to every query.

* WITH CHECK (tenant_id = current_tenant_id()): This is the constraint for write operations (INSERT, UPDATE). It prevents a user from inserting data for another tenant or moving data between tenants.

Step 5: Tying It All Together with Permissions

The final step is to orchestrate permissions. We want our application roles to interact with the view, not the underlying table.

-- Create roles for our application
CREATE ROLE app_admin;
CREATE ROLE app_support;
CREATE ROLE app_member;

-- Grant usage on the schema
GRANT USAGE ON SCHEMA public TO app_admin, app_support, app_member;

-- Grant SELECT access ONLY on the VIEW
GRANT SELECT ON vw_sensitive_pii TO app_admin, app_support, app_member;

-- Deny direct access to the base table for these roles
REVOKE ALL ON sensitive_pii FROM app_admin, app_support, app_member;
-- The table owner or a migration user would still have access

-- Grant write permissions if needed. Note that writes would still go to the base table.
-- This requires a more complex setup with INSTEAD OF triggers on the view or separate API endpoints.
-- For this example, we focus on the read (masking) path.
GRANT INSERT (full_name, email_address, phone_number, national_id), UPDATE ON sensitive_pii TO app_admin;

Now, the security model is complete. An application user connecting with the app_support role can only SELECT from vw_sensitive_pii. When they do, the following happens:

• The final, masked result set is returned to the user.

Performance Analysis and Optimization

This architecture is robust, but it's not free. Let's analyze the performance impact and how to mitigate it.

1. RLS Predicate Overhead:

RLS adds a predicate to every query. The tenant_isolation_policy effectively adds WHERE tenant_id = current_tenant_id(). This is why having a B-tree index on tenant_id on all RLS-protected tables is non-negotiable. Without it, PostgreSQL would be forced to do a sequential scan on every query.

Let's compare query plans. Assume app.current_tenant_id is set.

Without RLS: EXPLAIN ANALYZE SELECT * FROM sensitive_pii;

                                     QUERY PLAN
-------------------------------------------------------------------------------------
 Seq Scan on sensitive_pii  (cost=0.00..18.50 rows=850 width=145) (actual time=0.010..0.035 rows=1000 loops=1)
 Planning Time: 0.075 ms
 Execution Time: 0.055 ms

With RLS: EXPLAIN ANALYZE SELECT * FROM sensitive_pii;

                                           QUERY PLAN
-----------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on sensitive_pii  (cost=4.44..14.59 rows=10 width=145) (actual time=0.025..0.035 rows=10 loops=1)
   Recheck Cond: (tenant_id = current_tenant_id())
   ->  Bitmap Index Scan on idx_sensitive_pii_tenant_id  (cost=0.00..4.44 rows=10 width=0) (actual time=0.020..0.020 rows=10 loops=1)
         Index Cond: (tenant_id = current_tenant_id())
 Planning Time: 0.150 ms
 Execution Time: 0.050 ms

The planner correctly picks the index, and performance is excellent.

2. View and Function Overhead:

The vw_sensitive_pii introduces a join and several CASE statements. The join to users on current_user_id() is very fast as it's on a primary key and happens once per query. The IMMUTABLE masking functions have minimal overhead.

EXPLAIN ANALYZE SELECT * FROM vw_sensitive_pii LIMIT 10;

This query plan will be more complex, showing the join to users and the underlying scan on sensitive_pii. The cost will be slightly higher than a direct table query, but for most OLTP workloads, this overhead is negligible and a worthy trade-off for the security gains.

3. Context Setting (SET LOCAL):

The cost of SET LOCAL is very low, typically in the microseconds range. In extremely high-throughput systems with very short-lived transactions, this can add up, but it's rarely a bottleneck compared to the query itself.

Edge Cases and Production Hardening

Superusers and BYPASSRLS: A PostgreSQL superuser or any role with the BYPASSRLS attribute is not subject to RLS policies. Production application roles should never* have these privileges. Access for database administrators should be tightly controlled and audited. Use specific, non-superuser roles for migrations and administrative tasks.

* Complex Role Hierarchies: Our example uses a simple text column for roles. A more robust system might have roles and permissions tables. The view's logic can be adapted to handle this by changing the JOIN to a subquery or LEFT JOIN on a permissions table:

    -- Inside the view...
    LEFT JOIN user_permissions p ON p.user_id = current_user_id()
    -- In the CASE statement...
    CASE WHEN p.permission = 'pii.national_id.read' THEN pii.national_id ...

Auditing: RLS and masking control what* a user sees, but they don't inherently log the access. For compliance, you should pair this pattern with an auditing tool like pgaudit. You can configure pgaudit to log all SELECT statements on vw_sensitive_pii, providing a clear record of who accessed sensitive data, even if the data they received was masked.

* Handling NULL Current User: Our helper functions gracefully return NULL if the session variable is not set. The RLS policy tenant_id = current_tenant_id() will then evaluate to tenant_id = NULL, which is never true (unless tenant_id is actually NULL). This correctly results in zero rows being returned for an unauthenticated session, failing securely.

Conclusion: Database-Enforced Data Governance

By moving data masking and access control logic from the application layer into the database, we build a more secure, robust, and maintainable system. The pattern of combining tenant-isolating RLS policies on a base table with a SECURITY BARRIER view for dynamic, role-based masking provides a powerful framework for data governance in any multi-tenant SaaS application.

This architecture centralizes the security logic, making it easier to audit and reason about. It eliminates the risk of developer error in a distributed application environment leading to data leaks. While there is a minor performance cost, it is manageable with proper indexing and function design, and it is an entirely acceptable trade-off for the massive leap in security and compliance posture. The database is no longer just a passive data store; it is an active participant in enforcing your application's security model.