Postgres RLS: Dynamic Data Masking for Multi-Tenant SaaS
The Fallacy of Application-Layer Authorization in Multi-Tenant Architectures
Managing data access in a shared-schema, multi-tenant SaaS architecture is a recurring, high-stakes engineering challenge. While schema-per-tenant models offer robust isolation, the operational overhead, connection pooling complexity, and migration difficulties often lead engineering teams to a shared-schema model. In this model, a tenant_id or organization_id column is the ubiquitous solution for data partitioning.
The default pattern is to enforce tenancy at the application layer. Every repository method, every service call, every query builder instance must be diligently programmed to include a WHERE organization_id = ? clause. This approach is fundamentally flawed and brittle. A single missing WHERE clause in a deeply nested part of the codebase, introduced by a junior developer at 2 AM, can lead to a catastrophic cross-tenant data leak. Code reviews can miss it. Static analysis might not catch it. It's a time bomb waiting to detonate.
PostgreSQL's Row-Level Security (RLS) offers a robust, database-native solution to this problem, creating a security backstop that the application layer cannot accidentally circumvent. However, most RLS tutorials stop at the basic USING (organization_id = current_setting('app.current_organization_id')). This is insufficient for real-world applications which require nuanced permissions:
admin within an organization should see all data, but a member should only see records they created.billing_manager might need to see all invoices, regardless of who created them, while a standard member cannot.This article dives deep into implementing these advanced patterns, focusing on a powerful combination of RLS policies, secure views for dynamic data masking, and performance tuning for production workloads.
The Scenario: A Multi-Tenant Invoicing Platform
Let's establish a concrete data model. We have organizations, users who belong to them with specific roles, and invoices that contain sensitive customer information.
-- Enable the pgcrypto extension for UUIDs
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
-- Organizations (Tenants)
CREATE TABLE organizations (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name TEXT NOT NULL
);
-- Users
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
email TEXT NOT NULL UNIQUE,
hashed_password TEXT NOT NULL
);
-- Roles for users within an organization
CREATE TYPE organization_role AS ENUM ('admin', 'member', 'billing_manager');
CREATE TABLE user_roles (
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
role organization_role NOT NULL,
PRIMARY KEY (user_id, organization_id, role)
);
-- Sensitive Data: Invoices
CREATE TABLE invoices (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
created_by_user_id UUID NOT NULL REFERENCES users(id),
customer_name TEXT NOT NULL,
customer_email TEXT NOT NULL,
amount_cents INTEGER NOT NULL,
status TEXT NOT NULL DEFAULT 'draft',
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- Create indexes for efficient lookups
CREATE INDEX ON users (organization_id);
CREATE INDEX ON user_roles (user_id);
CREATE INDEX ON invoices (organization_id);
CREATE INDEX ON invoices (created_by_user_id);Establishing Application Context in the Database
RLS policies need to know who is making the request. The most secure and flexible way to pass this context from your application to PostgreSQL is via runtime parameters set at the beginning of each transaction. These settings are connection-local and automatically cleared when the connection is released back to the pool.
Let's assume your application uses JWTs for authentication. A typical JWT payload might look like this:
{
"sub": "a1b2c3d4-user-id-uuid-e5f6",
"org_id": "x1y2z3w4-org-id-uuid-v5u6",
"roles": ["admin", "member"],
"iat": 1516239022
}Your application's database abstraction layer or a middleware should be responsible for setting these values for every transaction. Here is an example using Node.js and the pg library:
// Middleware example for an Express.js-like framework
async function setDatabaseContext(req, res, next) {
if (!req.user) { // Assuming a previous middleware decoded the JWT into req.user
return next();
}
const client = await pool.connect();
req.dbClient = client; // Attach the client to the request object
try {
await client.query('BEGIN');
// Use SET LOCAL to ensure the settings are transaction-scoped
await client.query(`SET LOCAL app.current_user_id = '${req.user.sub}';`);
await client.query(`SET LOCAL app.current_organization_id = '${req.user.org_id}';`);
// Convert array of roles to a PostgreSQL-compatible string literal
const rolesString = `{${req.user.roles.join(',')}}`;
await client.query(`SET LOCAL app.current_user_roles = '${rolesString}';`);
next();
} catch (err) {
await client.query('ROLLBACK');
client.release();
next(err);
}
}
// In a route handler, you'd use req.dbClient
app.get('/invoices', setDatabaseContext, async (req, res, next) => {
try {
const result = await req.dbClient.query('SELECT * FROM invoices;');
await req.dbClient.query('COMMIT');
res.json(result.rows);
} catch (err) {
await req.dbClient.query('ROLLBACK');
next(err);
} finally {
if (req.dbClient) {
req.dbClient.release();
}
}
});This pattern is critical. Using SET LOCAL ensures these parameters only exist for the duration of the transaction, preventing context leakage between different requests sharing the same database connection from a pool.
Implementing Multi-layered RLS Policies
With the context established, we can now define our RLS policies. First, we must enable RLS on the target table.
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
-- IMPORTANT: This will immediately block all access, even for superusers,
-- unless they have the BYPASSRLS attribute or RLS is disabled for them.
-- It forces you to be explicit about who can see what.Policy 1: The Basic Tenant Isolation
This is the foundational policy. It ensures no user can ever see data outside their own organization. All subsequent, more granular policies will be built on top of this.
CREATE POLICY tenant_isolation_policy ON invoices
AS PERMISSIVE FOR ALL
TO public
USING (organization_id = current_setting('app.current_organization_id')::uuid);AS PERMISSIVE: This means if multiple policies are defined, a row is accessible if any permissive policy evaluates to true. The alternative, RESTRICTIVE, requires all restrictive policies to be true.FOR ALL: Applies to SELECT, INSERT, UPDATE, and DELETE.TO public: Applies to all roles.USING (...): This is the core of the policy. If the expression returns true for a given row, it's visible. This clause is used for existing rows (SELECT, UPDATE, DELETE).Policy 2: Role-Based Row Visibility
Now for the nuance. An admin or billing_manager can see all invoices in their organization, but a member can only see invoices they created themselves.
We need a way to check the user's roles. We can use the app.current_user_roles setting.
CREATE POLICY user_role_policy ON invoices
AS PERMISSIVE FOR SELECT
TO public
USING (
-- Admins and billing managers can see everything (within their tenant, due to the first policy)
current_setting('app.current_user_roles')::text[] @> ARRAY['admin']::text[] OR
current_setting('app.current_user_roles')::text[] @> ARRAY['billing_manager']::text[] OR
-- Members can only see their own invoices
(
current_setting('app.current_user_roles')::text[] @> ARRAY['member']::text[] AND
created_by_user_id = current_setting('app.current_user_id')::uuid
)
);When multiple PERMISSIVE policies exist for the same command (SELECT in this case), they are combined with an OR. So a user can see a row if:
(tenant_isolation_policy) AND (user_role_policy)
This is a common source of confusion. The policies for a given table are combined with OR, but the USING clause of a single policy acts like a WHERE clause (an AND condition).
Policy 3: Controlling Inserts and Updates with `WITH CHECK`
The USING clause applies to rows that already exist. For INSERT and UPDATE, we need the WITH CHECK clause to validate new data.
CREATE POLICY insert_update_policy ON invoices
AS PERMISSIVE FOR INSERT, UPDATE
TO public
WITH CHECK (
-- Ensure any new or updated row is for the current user's organization
organization_id = current_setting('app.current_organization_id')::uuid AND
-- Ensure the created_by_user_id is the person making the request
created_by_user_id = current_setting('app.current_user_id')::uuid
);This prevents a user from maliciously trying to insert an invoice for another organization or on behalf of another user.
The Advanced Technique: Dynamic Data Masking
Here is the most complex requirement: an internal support agent needs to view an invoice for debugging, but we cannot expose PII like customer_name and customer_email. RLS on its own controls row visibility, not column contents. The solution is to combine RLS with a view that performs conditional masking.
First, let's add a new role for our internal support staff. This role would not be in the organization_role enum, but rather a property of the database connection itself, perhaps set by a separate, internal-only application.
-- In our application logic, we would set this for internal users
-- SET LOCAL app.internal_role = 'support_agent';Now, we create a view that queries the invoices table and conditionally masks data.
CREATE OR REPLACE VIEW view_invoices AS
SELECT
id,
organization_id,
created_by_user_id,
CASE
WHEN current_setting('app.internal_role', true) = 'support_agent'
THEN '*** MASKED ***'
ELSE customer_name
END AS customer_name,
CASE
WHEN current_setting('app.internal_role', true) = 'support_agent'
THEN '*****@masked.com'
ELSE customer_email
END AS customer_email,
amount_cents,
status,
created_at
FROM invoices;current_setting('app.internal_role', true): The second argument true tells PostgreSQL not to error if the setting is missing, returning NULL instead. This is crucial so the view works for regular users too.The final step is to direct all application queries to view_invoices instead of the base invoices table. The RLS policies on the base invoices table are still enforced automatically when the view is queried. This is a powerful feature: the view respects the security policies of its underlying tables.
Now, when a regular user queries the view, app.internal_role is not set, the CASE statements evaluate to the ELSE condition, and they see the real data (provided the RLS policies on invoices grant them access to the row).
When a support agent queries the view, app.internal_role is support_agent, and they see the masked data. We still need an RLS policy to grant them access to the row itself.
-- Policy for internal support access
CREATE POLICY internal_support_policy ON invoices
AS PERMISSIVE FOR SELECT
TO public -- Or a more specific internal role
USING (current_setting('app.internal_role', true) = 'support_agent');Now, a support agent's query to view_invoices will succeed because internal_support_policy grants them access to the row, and the view's logic will mask the sensitive columns.
Performance Analysis and Optimization
RLS is not free. Every query against a protected table has its WHERE clause modified to include the policy expressions. Complex policies with subqueries or functions can significantly impact performance.
Let's analyze the execution plan for a query by a member user.
-- Simulate a member user's context
BEGIN;
SET LOCAL app.current_user_id = '...';
SET LOCAL app.current_organization_id = '...';
SET LOCAL app.current_user_roles = '{member}';
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM view_invoices WHERE status = 'paid';
ROLLBACK;The output will look something like this:
Seq Scan on invoices (cost=0.00..65.40 rows=1 width=123) (actual time=0.035..0.036 rows=10 loops=1)
Filter: ((status = 'paid'::text) AND (organization_id = '...'::uuid) AND ((('{admin}'::text[] @> '{member}'::text[]) OR ('{billing_manager}'::text[] @> '{member}'::text[]) OR (('{member}'::text[] @> '{member}'::text[]) AND (created_by_user_id = '...'::uuid)))))
Buffers: shared hit=4
Planning Time: 0.234 ms
Execution Time: 0.056 msNotice the giant Filter condition. The planner has injected our RLS policies directly into the query. In this case, it's a Seq Scan because our test data is small. On a large table, this could be disastrous if the policy prevents the planner from using an index.
Optimization Strategies
organization_id, a CREATE INDEX ON invoices (organization_id) is non-negotiable. If member users frequently query by status, a composite index would be even better: CREATE INDEX ON invoices (organization_id, created_by_user_id, status). The planner can use this index to satisfy both the RLS policy and the query's WHERE clause.VOLATILE function (the default) is re-evaluated for every row. If your function's result only depends on its arguments and is consistent within a transaction, declare it as STABLE. If it's truly globally constant, use IMMUTABLE. -- Example of a STABLE function to check roles
CREATE OR REPLACE FUNCTION has_role(role_to_check text)
RETURNS boolean
LANGUAGE sql
STABLE -- Critically important! The result is stable within a single query.
AS $$
SELECT current_setting('app.current_user_roles')::text[] @> ARRAY[role_to_check]::text[];
$$;
-- Rewritten policy
USING (
has_role('admin') OR has_role('billing_manager') OR
(has_role('member') AND created_by_user_id = current_setting('app.current_user_id')::uuid)
) Marking the function STABLE allows the query planner to call it once per query instead of once per row, a massive performance gain.
Edge Cases and Production Hardening
ALTER TABLE invoices DISABLE ROW LEVEL SECURITY. A better way is to grant the specific role the BYPASSRLS attribute: ALTER ROLE background_worker BYPASSRLS;. This is explicit and auditable. The role running migrations should always have this attribute.SET ROLE to impersonate a specific user type and SET LOCAL to establish their context, then run queries and assert that the returned data is correct (or correctly masked). -- Example using pg-tap for testing
BEGIN;
SELECT plan(1);
-- Impersonate a 'member' user
SET LOCAL app.current_user_id = '...';
SET LOCAL app.current_organization_id = '...';
SET LOCAL app.current_user_roles = '{member}';
-- This member should only see 1 of the 2 invoices in their org
SELECT results_eq(
'SELECT COUNT(*) FROM view_invoices',
$$VALUES (1::bigint)$$,
'Member user should only see their own invoice'
);
SELECT * FROM finish();
ROLLBACK;Conclusion
Implementing Row-Level Security in PostgreSQL is far more than adding a simple tenant ID check. By leveraging multiple permissive policies, the WITH CHECK clause, and combining RLS with secure views for dynamic data masking, you can move your entire authorization model from a brittle, distributed application-layer concern into a centralized, robust, and auditable database-layer enforcement mechanism. While it introduces complexity into your database schema and requires careful performance tuning, the security and reliability gains for a multi-tenant SaaS application are immense. This pattern provides a powerful guarantee that even with application-level bugs, your most critical security boundary—tenant data isolation—will hold firm.