Imagine you're the lead data engineer at a SaaS company serving 300 enterprise clients. Your application sits on a single PostgreSQL database — one schema, one set of tables, hundreds of thousands of rows belonging to different organizations. Late one Friday afternoon, you get an email from your security team: a client's developer noticed they could view another tenant's order data by manipulating a query parameter. The breach isn't catastrophic, but it's a near miss. The fix your team reaches for — adding WHERE tenant_id = :current_tenant to every query — works until it doesn't. One developer forgets it on a reporting endpoint. A new ORM query skips it. Six months later you're back in the same conversation, only now with a compliance auditor in the room.
The real solution isn't discipline. It's architecture. Modern relational databases ship with security primitives that sit below the application layer, enforcing data access rules at the engine level where they can't be accidentally omitted. Row-Level Security (RLS) lets you attach policies to tables so that queries automatically filter results based on who's asking. Dynamic Data Masking scrambles sensitive values at query time for users who shouldn't see them in plaintext. Permission-based query filtering lets you build layered access models where the same SQL query returns different results to a billing admin, a support engineer, and an end customer — without branching your application logic.
By the end of this lesson, you'll understand not just how to configure these features, but why they're designed the way they are, where they break down, and how to build a production-grade multi-tenant access model you can actually trust under audit. Specifically:
What you'll learn:
You should be comfortable with:
You don't need prior experience with RLS or DDM — we'll build from first principles.
Before we touch a single security feature, let's establish a concrete schema. Rushing into RLS without understanding your data model is one of the most common ways engineers end up with policies that are either too permissive or that silently kill performance.
We'll model a B2B SaaS platform — call it Arbor — that provides project management and invoicing tools for professional services firms. The relevant tables look like this:
CREATE TABLE tenants (
tenant_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name TEXT NOT NULL,
plan TEXT NOT NULL CHECK (plan IN ('starter', 'growth', 'enterprise')),
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TABLE users (
user_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id UUID NOT NULL REFERENCES tenants(tenant_id),
email TEXT NOT NULL,
role TEXT NOT NULL CHECK (role IN ('owner', 'admin', 'member', 'viewer')),
is_active BOOLEAN NOT NULL DEFAULT TRUE,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TABLE projects (
project_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id UUID NOT NULL REFERENCES tenants(tenant_id),
name TEXT NOT NULL,
status TEXT NOT NULL CHECK (status IN ('active', 'archived', 'draft')),
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TABLE invoices (
invoice_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id UUID NOT NULL REFERENCES tenants(tenant_id),
project_id UUID REFERENCES projects(project_id),
amount_cents INTEGER NOT NULL,
status TEXT NOT NULL CHECK (status IN ('draft', 'sent', 'paid', 'overdue')),
recipient_email TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TABLE audit_log (
log_id BIGSERIAL PRIMARY KEY,
tenant_id UUID,
user_id UUID,
action TEXT NOT NULL,
table_name TEXT NOT NULL,
row_id UUID,
occurred_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
The tenant_id column appears on every tenant-owned table. This is the foundational design choice that makes RLS tractable. If your existing schema doesn't follow this pattern, you'll need to add it before RLS can help you — there's no shortcut.
When you enable RLS on a table in PostgreSQL, the query planner treats your policy predicates as an invisible WHERE clause appended to every query that touches that table. The crucial thing to understand is that this happens after parsing and before optimization — which means the planner can use indexes on those predicate columns, but it also means every single statement (SELECT, INSERT, UPDATE, DELETE) is affected unless you explicitly configure otherwise.
Let's enable RLS and write our first policy:
-- Enable RLS on the table
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
-- By default, once RLS is enabled, NO rows are visible to anyone
-- except superusers and table owners with BYPASSRLS privilege.
-- You must explicitly create policies.
-- Create a policy for tenant isolation
CREATE POLICY tenant_isolation ON projects
USING (tenant_id = current_setting('app.current_tenant_id')::UUID);
The USING clause is the filter predicate — it controls which existing rows are visible (for SELECT) and which can be updated or deleted. There's also a WITH CHECK clause that controls which rows can be inserted or updated into. If you only provide USING, PostgreSQL uses it for both read and write operations.
Here's where most tutorials stop, and where real problems begin. The current_setting('app.current_tenant_id') call needs to be set somewhere before your queries run. In a connection-pooling environment, this is subtle:
-- Your application must set this at the start of each transaction
-- or at session initialization:
SET LOCAL app.current_tenant_id = '550e8400-e29b-41d4-a716-446655440000';
-- SET LOCAL only persists for the current transaction.
-- SET (without LOCAL) persists for the session.
-- With a connection pool (PgBouncer in transaction mode),
-- you MUST use SET LOCAL within an explicit transaction.
Critical warning: If you're using PgBouncer in transaction pooling mode,
SETwithoutLOCALis catastrophically dangerous. A session variable set in one transaction could persist into another tenant's transaction if the connection is reused. Always useSET LOCALinside an explicitBEGIN/COMMITblock when working with connection pools.
A complete, production-grade RLS setup covers all four DML operations. PostgreSQL lets you write separate policies per command:
-- Drop the generic policy and write explicit ones
DROP POLICY tenant_isolation ON projects;
-- SELECT policy: only see your own tenant's projects
CREATE POLICY projects_select ON projects
FOR SELECT
USING (tenant_id = current_setting('app.current_tenant_id')::UUID);
-- INSERT policy: can only insert rows that belong to your tenant
CREATE POLICY projects_insert ON projects
FOR INSERT
WITH CHECK (tenant_id = current_setting('app.current_tenant_id')::UUID);
-- UPDATE policy: can only see and update your own projects
-- USING filters which rows can be targeted
-- WITH CHECK validates the resulting row after update
CREATE POLICY projects_update ON projects
FOR UPDATE
USING (tenant_id = current_setting('app.current_tenant_id')::UUID)
WITH CHECK (tenant_id = current_setting('app.current_tenant_id')::UUID);
-- DELETE policy: can only delete your own tenant's projects
CREATE POLICY projects_delete ON projects
FOR DELETE
USING (tenant_id = current_setting('app.current_tenant_id')::UUID);
The distinction between USING and WITH CHECK on UPDATE matters more than it looks. Suppose someone tried to move a project from their tenant to another tenant's namespace:
-- Attacker tries to "steal" a row from another tenant by updating
-- their own row's tenant_id to point elsewhere
UPDATE projects
SET tenant_id = 'victim-tenant-uuid-here'
WHERE project_id = 'my-project-uuid';
Without WITH CHECK, the USING clause would allow this — the row being targeted belongs to the current tenant, so it passes the filter. The WITH CHECK clause evaluates the resulting state of the row and rejects the update because the resulting tenant_id doesn't match app.current_tenant_id.
Here's an edge case that surprises many engineers: when multiple policies apply to the same table and command, PostgreSQL combines them with OR semantics (for permissive policies). This means if you have two SELECT policies, a row is visible if it passes either one.
-- Suppose you add a "platform admin" policy later:
CREATE POLICY platform_admin_select ON projects
FOR SELECT
USING (current_setting('app.is_platform_admin', true) = 'true');
Now any session with app.is_platform_admin set to 'true' can see all projects, regardless of tenant_id. That might be intentional — platform support staff need to see everything — but it also means a bug that incorrectly sets this flag would expose all tenants' data. This is why RESTRICTIVE policies exist:
-- A RESTRICTIVE policy uses AND semantics.
-- The row must pass ALL restrictive policies AND at least one permissive policy.
CREATE POLICY must_be_active_tenant ON projects
AS RESTRICTIVE
FOR SELECT
USING (
EXISTS (
SELECT 1 FROM tenants
WHERE tenant_id = projects.tenant_id
AND plan != 'suspended'
)
);
With this restrictive policy in place, even a platform admin can't see rows belonging to suspended tenants unless you explicitly handle that case.
Table owners and superusers bypass RLS by default. This is a double-edged sword. For your application's migration user (which owns the tables), this means RLS is invisible — migrations run without restriction, which is usually correct. But if your application connects as a table-owning user, your entire RLS setup is moot.
-- Create a dedicated application role that CANNOT bypass RLS
CREATE ROLE arbor_app LOGIN PASSWORD 'use-a-vault-for-this';
-- Grant data access but NOT ownership
GRANT SELECT, INSERT, UPDATE, DELETE ON projects TO arbor_app;
GRANT SELECT, INSERT, UPDATE, DELETE ON invoices TO arbor_app;
GRANT SELECT, INSERT, UPDATE, DELETE ON users TO arbor_app;
-- Separately, your migration role owns the tables and bypasses RLS
-- Never use this role in application connection strings
CREATE ROLE arbor_migrations LOGIN PASSWORD 'also-use-a-vault';
ALTER TABLE projects OWNER TO arbor_migrations;
Architecture note: This role separation — one role for migrations (owns tables, bypasses RLS) and one for the application (no bypass) — is not optional if you're serious about multi-tenant isolation. Mixing them is a recurring audit finding that's genuinely embarrassing to explain.
This is where theory meets reality hard. The current_setting() call inside your policy predicate is a stable function in PostgreSQL — it returns the same value for the duration of a query, but the planner can't treat it as a constant at plan time. This matters for index usage.
Let's look at what happens with a large invoices table:
-- Without RLS, this query uses the primary key index efficiently
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM invoices WHERE invoice_id = 'some-uuid';
-- With RLS active, the plan now includes the policy predicate:
-- The planner must also evaluate: tenant_id = current_setting(...)::UUID
-- This is fine IF there's a composite index on (tenant_id, invoice_id)
-- But if you only have an index on invoice_id alone, you may see:
-- Index Scan on invoices (cost=0.56..8.58 rows=1 width=120)
-- Filter: (tenant_id = (current_setting('app.current_tenant_id'))::uuid)
-- The filter happens AFTER the index lookup, which is usually fine for PK lookups
-- but catastrophic for table scans on large tables
-- Create an index that supports your RLS predicate:
CREATE INDEX idx_invoices_tenant ON invoices (tenant_id);
-- For queries that filter on both tenant and another column:
CREATE INDEX idx_invoices_tenant_status ON invoices (tenant_id, status);
CREATE INDEX idx_invoices_tenant_created ON invoices (tenant_id, created_at DESC);
The mental model to hold: your RLS policy predicate is a filter that participates in query planning. Every table with RLS enabled should have an index on the isolation column (tenant_id), and your frequent access patterns should have composite indexes that put tenant_id first. If your query planner isn't using those indexes, check whether the column statistics are current (ANALYZE invoices) and whether the cost estimates account for your actual data distribution.
SQL Server's RLS, introduced in SQL Server 2016, uses a different architectural model. Instead of policy predicates attached to tables directly, you write an inline table-valued function (TVF) that encapsulates your access logic, then bind it to a table as a security policy.
-- First, create the schema-bound filter function
CREATE FUNCTION Security.fn_tenant_filter(@tenant_id UNIQUEIDENTIFIER)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
SELECT 1 AS access_granted
WHERE
-- Regular tenant users see their own data
CAST(SESSION_CONTEXT(N'TenantId') AS UNIQUEIDENTIFIER) = @tenant_id
OR
-- Platform admins see everything
CAST(SESSION_CONTEXT(N'IsPlatformAdmin') AS BIT) = 1;
GO
-- Bind the function to the invoices table as a security policy
CREATE SECURITY POLICY InvoiceTenantPolicy
ADD FILTER PREDICATE Security.fn_tenant_filter(tenant_id) ON dbo.Invoices,
ADD BLOCK PREDICATE Security.fn_tenant_filter(tenant_id) ON dbo.Invoices AFTER INSERT
WITH (STATE = ON);
GO
SQL Server distinguishes between FILTER predicates (for SELECT) and BLOCK predicates (for INSERT/UPDATE/DELETE). Block predicates can be configured at different points: AFTER INSERT, AFTER UPDATE, BEFORE UPDATE, and BEFORE DELETE.
Setting session context in SQL Server uses sp_set_session_context:
-- Set at the beginning of each request (your app does this)
EXEC sp_set_session_context N'TenantId', '550e8400-e29b-41d4-a716-446655440000';
EXEC sp_set_session_context N'IsPlatformAdmin', 0;
-- SQL Server's session context survives connection pool reuse differently than PostgreSQL.
-- Use the @read_only flag to prevent later code from overwriting security context:
EXEC sp_set_session_context N'TenantId', '550e8400-...', @read_only = 1;
The @read_only = 1 parameter is critical for SQL Server security. Once set, the context key cannot be overwritten for the duration of the session. This prevents injection attacks where application code might be tricked into overwriting the tenant context.
Dynamic Data Masking (DDM) operates at a different layer than RLS. Where RLS controls which rows a user can see, DDM controls what values they see in those rows. The canonical use case: a support engineer needs to be able to look up customer records and verify account details, but shouldn't be able to read full credit card numbers, social security numbers, or personal health information.
SQL Server has built-in DDM as a column-level feature:
ALTER TABLE dbo.Users
ALTER COLUMN email ADD MASKED WITH (FUNCTION = 'email()');
ALTER TABLE dbo.Invoices
ALTER COLUMN recipient_email ADD MASKED WITH (FUNCTION = 'email()');
-- The 'email()' mask shows: aXXX@XXXX.com
-- Other built-in masks:
-- default() -> XXXX for strings, 0 for numbers, 01-01-1900 for dates
-- partial(prefix_length, padding, suffix_length) -> custom reveal
-- random(low, high) -> random number in range for numeric columns
-- For SSNs: show only last 4 digits
ALTER TABLE dbo.Customers
ALTER COLUMN ssn ADD MASKED WITH (FUNCTION = 'partial(0, "XXX-XX-", 4)');
A user with UNMASK permission sees the real values. Everyone else sees the masked version. Granting unmask is as simple as:
-- Grant the ability to see unmasked data to specific roles
GRANT UNMASK ON dbo.Users TO SeniorSupportRole;
-- In SQL Server 2022+, you can grant column-level unmask
GRANT UNMASK ON dbo.Users(email) TO SeniorSupportRole;
The important architectural point: DDM in SQL Server is a UI-layer defense. It doesn't prevent a clever user from inferring the real values through aggregations, comparisons, or side-channel attacks. Someone who can write WHERE email = 'target@company.com' can confirm whether that email exists, even if the column is masked, because the filter predicate runs against real data. DDM is appropriate for reducing accidental exposure, not for defending against malicious internal users with query access.
PostgreSQL doesn't have native DDM, but you can achieve equivalent (and arguably more robust) behavior using a combination of views, column permissions, and security-definer functions.
The approach: create a view that applies masking logic via CASE expressions, grant access to the view instead of the base table, and use column-level GRANT to expose unmasked columns only to privileged roles.
-- Masking helper functions
CREATE OR REPLACE FUNCTION mask_email(email TEXT) RETURNS TEXT AS $$
BEGIN
IF email IS NULL THEN RETURN NULL; END IF;
RETURN SUBSTRING(email, 1, 2) ||
REPEAT('*', POSITION('@' IN email) - 3) ||
SUBSTRING(email FROM POSITION('@' IN email));
END;
$$ LANGUAGE plpgsql IMMUTABLE;
-- Create a masking view
CREATE VIEW invoices_masked AS
SELECT
invoice_id,
tenant_id,
project_id,
amount_cents,
status,
-- Mask the sensitive column by default
CASE
WHEN pg_has_role(current_user, 'arbor_finance_admin', 'member')
OR pg_has_role(current_user, 'arbor_platform_admin', 'member')
THEN recipient_email
ELSE mask_email(recipient_email)
END AS recipient_email,
created_at
FROM invoices;
-- Grant access to the view, NOT the base table
REVOKE ALL ON invoices FROM arbor_app;
GRANT SELECT ON invoices_masked TO arbor_app;
GRANT INSERT, UPDATE, DELETE ON invoices TO arbor_app;
This pattern is more powerful than SQL Server's DDM in one respect: the masking logic is entirely in your control and can be arbitrarily complex. It's more work to maintain because you must keep the view in sync with table schema changes — a significant operational burden. Consider automating view generation if you have many sensitive columns.
Security depth: Because the view uses
pg_has_role()to check membership, the masking decision is made per-row at query time by the database engine — not by the application. Even if your application code has a bug, the masking holds. This is the right layer for this kind of defense.
RLS handles tenant isolation (horizontal partitioning of rows). But within a single tenant, you often need vertical access control: the same user table, but members can only see their own profile, admins can see all users in their tenant, and support staff can see specific fields across tenants. This is where permission-based query filtering comes in, and it's where the design gets genuinely interesting.
The cleanest architecture puts all access-context logic in a single function that any query can call:
-- A function that returns the current user's effective permissions
-- as a structured type — clean, testable, and central
CREATE TYPE user_access_context AS (
tenant_id UUID,
user_id UUID,
role TEXT,
is_platform_admin BOOLEAN,
can_view_financials BOOLEAN,
can_manage_users BOOLEAN
);
CREATE OR REPLACE FUNCTION get_current_user_context()
RETURNS user_access_context
LANGUAGE plpgsql STABLE SECURITY DEFINER AS $$
DECLARE
ctx user_access_context;
v_user_id UUID;
v_tenant_id UUID;
BEGIN
-- Read from session-level settings your app has configured
v_user_id := current_setting('app.current_user_id', true)::UUID;
v_tenant_id := current_setting('app.current_tenant_id', true)::UUID;
-- Fetch the user's role from the database (not just trusting the session setting)
SELECT
u.user_id,
u.tenant_id,
u.role,
FALSE, -- is_platform_admin: set by a separate privileged mechanism
u.role IN ('owner', 'admin'),
u.role IN ('owner', 'admin')
INTO
ctx.user_id,
ctx.tenant_id,
ctx.role,
ctx.is_platform_admin,
ctx.can_view_financials,
ctx.can_manage_users
FROM users u
WHERE u.user_id = v_user_id
AND u.tenant_id = v_tenant_id
AND u.is_active = TRUE;
IF NOT FOUND THEN
RAISE EXCEPTION 'Access denied: invalid or inactive user context';
END IF;
RETURN ctx;
END;
$$;
This function is SECURITY DEFINER — it runs with the permissions of its creator (the migration role), which means it can access the users table even when the calling role might not have direct access. More importantly, it validates the user context against the database, not just against session variables. An attacker who manipulates app.current_user_id in a session variable still can't impersonate an inactive user or a user belonging to a different tenant.
Now use this context function in your RLS policies:
-- Users can see their own profile; admins see all users in their tenant
CREATE POLICY users_access ON users
FOR SELECT
USING (
tenant_id = (get_current_user_context()).tenant_id
AND (
-- Admins see all users in their tenant
(get_current_user_context()).role IN ('owner', 'admin')
OR
-- Members only see themselves
user_id = (get_current_user_context()).user_id
)
);
-- Financial data: only owners and admins
CREATE POLICY invoices_access ON invoices
FOR SELECT
USING (
tenant_id = (get_current_user_context()).tenant_id
AND (get_current_user_context()).can_view_financials = TRUE
);
Wait — there's a performance trap here. Calling get_current_user_context() inside a policy predicate means it gets called for every row being evaluated. Because the function is marked STABLE, PostgreSQL may cache the result within a single query, but across a JOIN of large tables this can still be painful. Profile before deploying this pattern on high-cardinality tables.
A better approach for hot paths: materialize the context into session variables from the function, then read session variables in policies:
-- Call this once per request to set all context variables
CREATE OR REPLACE PROCEDURE set_request_context(
p_user_id UUID,
p_tenant_id UUID
)
LANGUAGE plpgsql AS $$
DECLARE
ctx user_access_context;
BEGIN
-- Fetch and validate
SELECT get_current_user_context() INTO ctx;
-- Set individual session variables for RLS policies to read cheaply
PERFORM set_config('app.current_tenant_id', ctx.tenant_id::TEXT, true);
PERFORM set_config('app.current_user_id', ctx.user_id::TEXT, true);
PERFORM set_config('app.current_role', ctx.role, true);
PERFORM set_config('app.can_view_financials', ctx.can_view_financials::TEXT, true);
PERFORM set_config('app.can_manage_users', ctx.can_manage_users::TEXT, true);
END;
$$;
-- RLS policies read cheap session variables, not function calls per-row
CREATE POLICY invoices_financial_access ON invoices
FOR SELECT
USING (
tenant_id = current_setting('app.current_tenant_id')::UUID
AND current_setting('app.can_view_financials')::BOOLEAN = TRUE
);
This two-stage approach — validate and materialize context in a procedure, then read session variables in policies — gives you both security and performance. The validation happens once; the cheap current_setting() calls happen per-row.
The audit log is a table everyone needs access to for compliance, but nobody should be able to write to except through controlled pathways, and different roles need different visibility:
-- Enable RLS on audit_log
ALTER TABLE audit_log ENABLE ROW LEVEL SECURITY;
-- Platform admins see everything
CREATE POLICY audit_platform_admin ON audit_log
FOR SELECT
USING (current_setting('app.is_platform_admin', true)::BOOLEAN = TRUE);
-- Tenant admins see their own tenant's audit trail
CREATE POLICY audit_tenant_admin ON audit_log
FOR SELECT
USING (
tenant_id = current_setting('app.current_tenant_id')::UUID
AND current_setting('app.current_role') IN ('owner', 'admin')
);
-- Regular users see only their own actions
CREATE POLICY audit_self ON audit_log
FOR SELECT
USING (
tenant_id = current_setting('app.current_tenant_id')::UUID
AND user_id = current_setting('app.current_user_id')::UUID
);
-- Nobody can INSERT/UPDATE/DELETE directly; only a security-definer function can
-- Revoke all write access from the app role
REVOKE INSERT, UPDATE, DELETE ON audit_log FROM arbor_app;
-- Audit writes go through a security-definer function
CREATE OR REPLACE FUNCTION log_audit_event(
p_action TEXT,
p_table_name TEXT,
p_row_id UUID
)
RETURNS VOID
LANGUAGE plpgsql SECURITY DEFINER AS $$
BEGIN
INSERT INTO audit_log (tenant_id, user_id, action, table_name, row_id)
VALUES (
current_setting('app.current_tenant_id')::UUID,
current_setting('app.current_user_id')::UUID,
p_action,
p_table_name,
p_row_id
);
END;
$$;
GRANT EXECUTE ON FUNCTION log_audit_event TO arbor_app;
This pattern — expose a controlled write path through a SECURITY DEFINER function while revoking direct table access — is the correct way to protect audit integrity. The function runs with elevated permissions but only does exactly what it's designed to do.
The most dangerous security configurations are the ones that seem to work but have holes you haven't found. Validation isn't optional — it's part of the implementation.
PostgreSQL lets you impersonate another role for testing purposes:
-- As a superuser or role with the target role as a member:
BEGIN;
SET ROLE arbor_app;
SET LOCAL app.current_tenant_id = 'tenant-a-uuid';
SET LOCAL app.current_user_id = 'user-from-tenant-a-uuid';
SET LOCAL app.current_role = 'member';
SET LOCAL app.can_view_financials = 'false';
-- This should return only tenant A's projects
SELECT count(*) FROM projects;
-- Expected: count of only tenant A's rows
-- This should return 0 rows (wrong tenant)
SELECT count(*) FROM projects WHERE tenant_id = 'tenant-b-uuid';
-- Expected: 0
-- This should fail (member can't see financials)
SELECT count(*) FROM invoices;
-- Expected: 0
ROLLBACK;
Treat security policy tests like unit tests. Here's a pattern using pgTAP (PostgreSQL's test framework):
-- Install pgTAP, then write tests like:
SELECT plan(6);
-- Test 1: Tenant A member cannot see Tenant B's projects
SELECT set_config('app.current_tenant_id', :'tenant_a_id', true);
SELECT set_config('app.current_user_id', :'member_user_id', true);
SELECT set_config('app.current_role', 'member', true);
SELECT is(
(SELECT count(*)::INT FROM projects WHERE tenant_id = :'tenant_b_id'),
0,
'Member from Tenant A cannot see Tenant B projects'
);
-- Test 2: Tenant A admin can see all Tenant A projects
SELECT set_config('app.current_role', 'admin', true);
SELECT is(
(SELECT count(*)::INT FROM projects WHERE tenant_id = :'tenant_a_id'),
(SELECT count(*)::INT FROM projects_unfiltered WHERE tenant_id = :'tenant_a_id'),
'Admin sees all their tenant projects'
);
-- ... more tests
SELECT finish();
For SQL Server, you can use the EXECUTE AS feature for equivalent testing:
-- Test as a specific user
EXECUTE AS USER = 'support_user_login';
SELECT COUNT(*) FROM dbo.Invoices WHERE tenant_id = 'other-tenant-id';
-- Must return 0
REVERT;
Build a complete multi-tenant security model for the Arbor schema. Work through these steps in order:
Step 1: Enable and test baseline tenant isolation
Enable RLS on all tenant-owned tables (users, projects, invoices, audit_log). Write the basic tenant isolation policies. Then verify by setting the session context to tenant A and confirming you get zero rows when querying for tenant B's data.
Step 2: Add role-based within-tenant filtering
Members of a tenant should only see their own user record in the users table. Admins see all users in their tenant. Write separate policies (remember, multiple permissive policies use OR) and test both role types.
Step 3: Implement invoice masking
Create the invoices_masked view with recipient_email masked for non-finance roles. Grant arbor_app access to the view rather than the base table for SELECT operations. Test by switching roles and confirming the email masking behavior.
Step 4: Lock down the audit trail
Remove direct INSERT access on audit_log for arbor_app. Create the log_audit_event security-definer function. Trigger it from a simple wrapper around a project status update and verify the audit record is written correctly.
Step 5: Add a platform admin bypass
Add a PERMISSIVE policy to each table that allows access when app.is_platform_admin = 'true'. Then add a RESTRICTIVE policy that prevents access to any suspended tenant's data, even for platform admins. Verify that the restrictive policy overrides the permissive one by testing with a suspended tenant's data.
Step 6: Validate with a security test suite
Write at least five test cases that check: cross-tenant data leakage, role escalation attempts (a member setting their own app.current_role to 'admin' in session variables), and masked email correctness.
Even with RLS enabled, the table owner bypasses policies by default. If your application connects as the table owner:
-- Check if FORCE ROW LEVEL SECURITY is needed
SELECT relname, relrowsecurity, relforcerowsecurity
FROM pg_class
WHERE relname IN ('projects', 'invoices', 'users');
-- If relforcerowsecurity is false and your app role owns the table,
-- you have a problem. Fix it:
ALTER TABLE projects FORCE ROW LEVEL SECURITY;
-- Better fix: change table ownership to a migration-only role
-- and connect your application as a non-owner role
If your only validation is current_setting('app.current_role') without cross-checking against the database, an attacker who can execute SQL can trivially escalate:
-- Attacker runs:
SET LOCAL app.current_role = 'admin';
SET LOCAL app.can_view_financials = 'true';
-- Now they see everything
-- Fix: validate role from the database in your context function,
-- don't trust session variables for role determination.
-- Session variables are fine for CARRYING validated context,
-- not for being the sole source of truth.
If your RLS policy calls a VOLATILE function (not STABLE or IMMUTABLE), it gets called for every row, always. Even STABLE functions may be called more than you expect on large joins:
-- Bad: VOLATILE function in policy (called per row, no caching)
CREATE POLICY bad_policy ON projects
FOR SELECT
USING (tenant_id = get_current_tenant()::UUID); -- VOLATILE function
-- If get_current_tenant() is not marked STABLE, each row evaluation
-- incurs a function call. On a 10M row table, that's 10M function calls.
-- Fix: mark context functions as STABLE, or pre-materialize to session variables
After enabling RLS, run EXPLAIN ANALYZE on your most common queries and look for sequential scans on large tables. The policy predicate adds a filter, but if there's no index, Postgres scans the full table before applying it:
-- Diagnosis: look for Seq Scan with Filter mentioning tenant_id
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM invoices WHERE status = 'overdue';
-- If you see: Seq Scan on invoices (cost=0.00..485000.00...)
-- Filter: ((tenant_id = ...) AND (status = 'overdue'))
-- You need:
CREATE INDEX idx_invoices_tenant_status ON invoices(tenant_id, status);
A common oversight: writing only a SELECT policy and forgetting that INSERT without a WITH CHECK policy falls through to the default behavior (allow all, when RLS is enabled and no applicable policy exists, the action is denied for restrictive-defaulted tables, but permissive-defaulted behavior can surprise you):
-- Check which policies exist on your tables
SELECT schemaname, tablename, policyname, permissive, roles, cmd, qual, with_check
FROM pg_policies
WHERE tablename = 'invoices';
-- Make sure cmd covers 'ALL' or each of 'SELECT', 'INSERT', 'UPDATE', 'DELETE'
In SQL Server, a masked column can still be used in WHERE clauses by the user who can't see its value:
-- User without UNMASK can still do this:
SELECT * FROM Users WHERE email = 'ceo@competitor.com';
-- If this returns rows, the user now knows that email exists in the system
-- DDM doesn't protect against this. If you need to prevent inference,
-- you need RLS to restrict which rows are visible, not DDM.
-- Use both together: RLS for row visibility, DDM for value masking.
RLS is compelling precisely because it enforces security in a layer that application code can't easily bypass. But it comes with trade-offs worth naming explicitly.
Schema complexity: Your tenant_id column must appear on every table you want to isolate. For deeply normalized schemas with many junction tables, this means either denormalizing to add tenant_id to more tables, or writing complex policy predicates that JOIN to find the owning tenant.
Testing friction: Testing RLS policies requires setting session context correctly in every test case. Teams without a disciplined testing culture often skip this, leading to policies that look right but have edge cases.
Debugging difficulty: When a query returns fewer rows than expected, it's not always obvious whether RLS filtered them or the WHERE clause did. PostgreSQL's EXPLAIN output includes policy predicates, but they're embedded in the plan in non-obvious ways.
Migration complexity: Adding RLS to an existing database mid-flight (while the application is running) requires careful sequencing: enable RLS, ensure the application is setting session context before enabling enforcement, then write policies. Getting this wrong causes data to become invisible to your running application.
For truly high-scale systems (billions of rows, thousands of tenants), RLS may not be the primary isolation mechanism — separate schemas or databases per tenant may be more appropriate. But for the vast majority of multi-tenant SaaS applications in the 10M–1B row range, RLS with proper indexing performs within single-digit percentage overhead of unprotected queries.
You've now built a complete mental model for database-layer security in multi-tenant applications. The key insights to carry forward:
RLS is a policy engine, not a WHERE clause. Policies compose with OR (permissive) and AND (restrictive) semantics, they affect all DML operations, and they interact with role ownership in ways you must explicitly account for. Setting FORCE ROW LEVEL SECURITY and separating your application role from your table-owner role aren't optional.
Context propagation is the weak link. The entire RLS model depends on session variables being set correctly before queries run. In connection-pooled environments, use SET LOCAL inside explicit transactions, validate context from the database rather than trusting session variables as sole truth, and use @read_only = 1 in SQL Server's session context to prevent overwriting.
DDM and RLS are complementary, not alternatives. RLS controls row visibility; DDM controls value visibility. A support engineer who can see all users within a tenant (RLS allows it) but shouldn't see full email addresses (DDM masks them) needs both. Design them together.
Test adversarially. The right question isn't "does my policy work?" — it's "what would happen if someone set every session variable to the most permissive value possible?" Your automated security tests should include deliberate attempts to break isolation.
Where to go next:
The security model you've built here — RLS for isolation, DDM for masking, SECURITY DEFINER functions for controlled write paths, and context functions for validated permission propagation — is production-ready and auditable. The compliance auditor in the room will have questions, but you'll have answers.
Learning Path: Advanced SQL Queries