How to Give Each Customer Their Own Data Without Breaking Your Database

Author's Note

This post is written by the Dashrendr team. While the strategies covered here are broadly applicable to any multi-tenant SaaS architecture, some sections reference Dashrendr-specific features and link to our product. We've done our best to keep the technical content objective and useful regardless of which tools you use.

The Multi-Tenancy Data Problem

Multi-tenancy is the backbone of almost every modern SaaS product. Instead of spinning up a separate database for each customer, you share infrastructure — and that shared infrastructure is exactly where things go wrong.

The core risk is simple: if your application doesn't rigorously enforce tenant boundaries at the data layer, a bug in a single query can expose Customer A's records to Customer B. This isn't a theoretical concern. It has happened to well-funded, well-staffed engineering teams, and the consequences range from embarrassing to catastrophic.

Embedded dashboards amplify this risk considerably. When you embed analytics directly into your product, you're often passing raw SQL or parameterized queries to a data layer that runs with elevated database credentials. If tenant context isn't baked into every query — not just the application layer — you're one missing WHERE tenant_id = ? clause away from a data breach.

There are three primary strategies for solving this problem, each with different trade-offs in complexity, performance, and operational overhead. Let's walk through all three.

Option 1: Schema-per-Tenant

The schema-per-tenant pattern gives each customer their own isolated namespace within the same database. In PostgreSQL, a schema is a logical container for tables, views, and functions. By creating one schema per tenant, you get hard namespace separation without the cost of running separate database instances.

How It Works

When a new customer signs up, your provisioning logic creates a dedicated schema and runs your migrations inside it. At query time, you set the search_path to the tenant's schema before executing any SQL.

-- Provision a new tenant schema
CREATE SCHEMA tenant_acme;

-- Run your standard migrations inside it
CREATE TABLE tenant_acme.users (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email       TEXT NOT NULL UNIQUE,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE tenant_acme.events (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id     UUID REFERENCES tenant_acme.users(id),
  name        TEXT NOT NULL,
  occurred_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- At query time, scope to the tenant's schema
SET search_path TO tenant_acme;
SELECT * FROM users WHERE email = 'alice@acme.com';

Pros

• Hard isolation: Cross-tenant queries are structurally impossible without explicitly referencing another schema.
• Clean migrations: You can migrate tenants independently or in rolling batches.
• Simpler queries: No tenant_id column needed — the schema is the boundary.

Cons

• Schema sprawl: At thousands of tenants, managing thousands of schemas becomes operationally painful.
• Migration complexity: Running DDL across all schemas requires tooling (e.g., a migration runner that iterates schemas).
• Connection pooling friction: Setting search_path per connection can interfere with poolers like PgBouncer in transaction mode.

When to Use It

Schema-per-tenant works best when you have a relatively small number of high-value enterprise customers (think dozens to low hundreds), where the operational overhead is justified by the isolation guarantees and per-tenant customization needs.

Option 2: Row-Level Security

Row-Level Security (RLS) is a PostgreSQL feature that enforces access policies directly inside the database engine. Instead of relying on your application to always include the right WHERE clause, RLS makes the database itself reject unauthorized row access — even if the query forgets to filter.

Setting Up RLS in PostgreSQL

The pattern involves three steps: add a tenant_id column to every shared table, enable RLS on those tables, and create policies that compare tenant_id against a session-level variable set at connection time.

-- 1. Add tenant_id to your tables
ALTER TABLE events ADD COLUMN tenant_id UUID NOT NULL;
CREATE INDEX ON events (tenant_id);

-- 2. Enable RLS
ALTER TABLE events ENABLE ROW LEVEL SECURITY;
ALTER TABLE events FORCE ROW LEVEL SECURITY;

-- 3. Create a policy using a session variable
CREATE POLICY tenant_isolation ON events
  USING (tenant_id = current_setting('app.current_tenant_id')::UUID);

-- 4. At query time, set the session variable before any SQL
SET LOCAL app.current_tenant_id = '11bf5b37-e0b8-42e0-8dcf-dc8c4aefc000';
SELECT * FROM events; -- automatically filtered to the tenant

RLS in MySQL

MySQL doesn't have native RLS, but you can approximate it using views with a DEFINER clause and stored procedures that enforce tenant context. The most pragmatic MySQL approach is disciplined application-layer filtering combined with a dedicated database role per tenant that only has access to that tenant's rows — enforced via views.

-- MySQL: create a filtered view per tenant
CREATE VIEW acme_events AS
  SELECT * FROM events WHERE tenant_id = 'acme';

-- Grant the tenant's app user access only to the view
GRANT SELECT ON acme_events TO 'acme_app_user'@'%';

Tenant ID Column Strategies

When using RLS or application-layer filtering, how you store and index tenant_id matters:

• UUID vs. integer: UUIDs are safer for external exposure (no enumeration risk) but slightly larger. Integers are faster for joins on very large tables.
• Composite indexes: Index (tenant_id, created_at) rather than tenant_id alone to support the time-range queries common in analytics workloads.
• Backfilling: If you're adding tenant_id to an existing table, do it in batches with a default value and validate before enabling RLS.

When to Use It

RLS is the right choice for products with many tenants (hundreds to millions) sharing the same tables. It scales well, requires no schema proliferation, and provides a strong safety net against application-layer bugs. The trade-off is that it requires careful session management and thorough testing of your policy logic.

Option 3: Token Scoping

Token scoping is the pattern most relevant to embedded analytics and API-driven data access. Instead of relying solely on database-level controls, you encode tenant context directly into a signed token — typically a JWT — and validate it server-side before any query runs.

Embedding Tenant Context in a JWT

When your backend issues an embed token for a dashboard or API call, it includes the tenant_id as a claim in the JWT payload. The token is signed with a secret only your server knows, so it can't be tampered with client-side.

// Node.js — issuing a scoped embed token
import jwt from 'jsonwebtoken';

function issueEmbedToken(tenantId: string, userId: string): string {
  return jwt.sign(
    {
      tenant_id: tenantId,
      user_id:   userId,
      scope:     'dashboard:read',
      iat:       Math.floor(Date.now() / 1000),
    },
    process.env.EMBED_SECRET!,
    { expiresIn: '1h' }
  );
}

// Validating the token and extracting tenant context
function validateEmbedToken(token: string): { tenant_id: string; user_id: string } {
  const payload = jwt.verify(token, process.env.EMBED_SECRET!) as any;
  if (!payload.tenant_id) throw new Error('Missing tenant_id claim');
  return { tenant_id: payload.tenant_id, user_id: payload.user_id };
}

Scoping Queries Automatically

Once the token is validated, the extracted tenant_id is injected into every downstream query — either as a parameter or as a session variable for RLS. The key principle is that the application layer never trusts a tenant ID supplied by the client directly; it always derives it from the verified token.

// Express middleware — attach tenant context from token
app.use('/api/embed', (req, res, next) => {
  const token = req.headers.authorization?.replace('Bearer ', '');
  if (!token) return res.status(401).json({ error: 'Missing token' });

  try {
    const { tenant_id } = validateEmbedToken(token);
    req.tenantId = tenant_id; // attach to request context
    next();
  } catch {
    return res.status(403).json({ error: 'Invalid or expired token' });
  }
});

// Query handler — tenant_id always comes from req.tenantId
app.get('/api/embed/events', async (req, res) => {
  const rows = await db.query(
    'SELECT * FROM events WHERE tenant_id = $1 ORDER BY occurred_at DESC LIMIT 100',
    [req.tenantId] // never from req.query or req.body
  );
  res.json(rows);
});

When to Use It

Token scoping is essential for any embedded analytics scenario where the data layer is accessed from a browser or third-party context. It works well in combination with RLS — the token provides the tenant context, and RLS enforces it at the database level as a second line of defense.

How Dashrendr Handles Tenant Isolation

Dashrendr is built with multi-tenant SaaS products as the primary use case, so tenant isolation is a first-class feature rather than an afterthought.

When you embed a Dashrendr dashboard into your product, you generate a signed embed token server-side that includes your tenant's identifier. Dashrendr validates this token on every request and automatically scopes all data queries to that tenant — you don't write filtering logic in your dashboard queries, because the isolation is enforced at the platform level.

This means your embedded dashboards are secure by default. Even if a dashboard query is written without an explicit tenant filter, Dashrendr's query engine injects the tenant context before execution. The result is that your developers can focus on building useful analytics rather than auditing every query for missing WHERE clauses.

Dashrendr also supports multiple isolation strategies depending on your architecture: token-scoped access for browser-embedded dashboards, API key scoping for server-to-server integrations, and connection-level tenant context for direct database connections. You can mix and match based on your deployment model.

Common Pitfalls

• Trusting client-supplied tenant IDs: Never accept a tenant_id directly from a query parameter, request body, or cookie without validating it against a signed token or authenticated session. Client-supplied values can be trivially forged.
• Forgetting to index tenant_id: Adding a tenant_id column without a proper index turns every tenant-scoped query into a full table scan. On large tables, this will destroy query performance. Always index tenant_id, ideally as part of a composite index with your most common filter columns.
• Bypassing RLS with superuser connections: PostgreSQL RLS policies are bypassed by superusers and table owners by default. Your application should connect with a role that is neither a superuser nor the table owner — use a dedicated application role with only the permissions it needs.
• Leaking tenant data in error messages: Stack traces, error responses, and logs can inadvertently expose tenant IDs, record counts, or data snippets from other tenants. Sanitize all error output before it reaches the client, and use structured logging with tenant context stripped from external-facing responses.
• Not testing cross-tenant access: It's easy to test that a tenant can see their own data. It's less common to explicitly test that they cannot see another tenant's data. Add negative-path integration tests that attempt cross-tenant access and assert they fail.
• Short-lived token neglect: Embed tokens with long expiry windows are a liability. If a token is leaked or a user's session is compromised, a long-lived token gives an attacker extended access. Keep embed token TTLs short (15 minutes to 1 hour) and implement token refresh flows.

Conclusion

Tenant data isolation isn't a feature you add at the end — it's a constraint you design around from the beginning. Whether you choose schema-per-tenant for hard namespace separation, row-level security for scalable shared-table isolation, or token scoping for embedded and API-driven access, the goal is the same: make it structurally impossible for one customer's data to reach another.

The best implementations layer these strategies. Use RLS as your database-level safety net, token scoping as your application-level enforcement, and schema separation where the isolation requirements justify the operational cost.

If you're building embedded analytics into your SaaS product and want tenant isolation handled at the platform level — so your team can ship dashboards without auditing every query — take a look at Dashrendr's pricing page and see if it fits your stack.