Multi-Tenant Postgres Search: Across vs Within Tenants

The architecture has four moving parts that have to agree: a default RLS posture that denies cross-tenant reads, an explicit cross-tenant read path that bypasses the default in a single audited place, an index strategy that matches the access pattern (per-tenant FTS vs cross-tenant FTS vs materialized view vs external search), and an indexer that keeps any external search backend tenant-aware at write time. Each piece does one job, and the seams between them are where the audit lives.

Forced RLS as the default posture

Start with the default. Tenant tables have RLS enabled, and the table owner is forced subject to RLS so even an owner-as-app connection cannot accidentally bypass. Postgres documents that table owners normally bypass row security and that you opt in to enforcement with ALTER TABLE ... FORCE ROW LEVEL SECURITY — for a multi-tenant app you want the forced posture, because the app role is often the owner of its own tables.

ALTER TABLE listings ENABLE ROW LEVEL SECURITY;
ALTER TABLE listings FORCE ROW LEVEL SECURITY;

-- PERMISSIVE by default: visible if you own the tenant.
CREATE POLICY listings_tenant_read
  ON listings
  FOR SELECT
  USING (tenant_id = current_setting('app.current_tenant')::uuid);

-- RESTRICTIVE: hide if soft-deleted, regardless of any other policy.
CREATE POLICY listings_not_deleted
  ON listings
  AS RESTRICTIVE
  FOR SELECT
  USING (deleted_at IS NULL);

-- RESTRICTIVE: hide if the tenant is suspended.
CREATE POLICY listings_tenant_active
  ON listings
  AS RESTRICTIVE
  FOR SELECT
  USING (
    EXISTS (
      SELECT 1 FROM tenants t
      WHERE t.id = listings.tenant_id
        AND t.status = 'active'
    )
  );

The two RESTRICTIVE policies are the load-bearing piece most teams miss. Permissive policies combine with OR, so adding another permissive policy can only grant more visibility. Restrictive policies combine with AND on top of the permissive set, so they can take visibility away in ways no future permissive policy can relax. Soft-delete and tenant-suspended are exactly the rules you want restrictive — once a row is deleted or a tenant is suspended, no clever "but the catalog needs to see it" policy should be able to undo that.

SECURITY DEFINER as the single cross-tenant entry

Now the cross-tenant read path. The pattern that scales is a SECURITY DEFINER function that the app calls when it explicitly needs to cross the tenant boundary. The function owns the visibility rules for the cross-tenant case — typically "the row is published AND not deleted AND the tenant is active AND not opted-out-of-catalog." The function has its search_path locked down because Postgres documents that a SECURITY DEFINER function runs with the owner's privileges and a writable schema in the path is a privilege-escalation hazard.

CREATE OR REPLACE FUNCTION public.catalog_search(q text, lim int DEFAULT 50)
RETURNS TABLE (
  listing_id uuid,
  tenant_id  uuid,
  title      text,
  rank       real
)
LANGUAGE sql
SECURITY DEFINER
STABLE
SET search_path = public, pg_temp
AS $$
  SELECT
    l.id,
    l.tenant_id,
    l.title,
    ts_rank(l.search_vector, websearch_to_tsquery('english', q)) AS rank
  FROM listings l
  JOIN tenants t ON t.id = l.tenant_id
  WHERE l.is_published = true
    AND l.deleted_at IS NULL
    AND t.status = 'active'
    AND t.catalog_opt_out = false
    AND l.search_vector @@ websearch_to_tsquery('english', q)
  ORDER BY rank DESC
  LIMIT lim;
$$;

REVOKE ALL ON FUNCTION public.catalog_search(text, int) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public.catalog_search(text, int) TO app_user;

That function is the entire cross-tenant read surface. A security review can grep for SECURITY DEFINER and find every path that crosses the boundary, instead of trying to reason about which application code paths might have bypassed RLS with a service-role connection. The Postgres docs note this directly: policy expressions run with the caller's privileges, but security-definer functions can access data the caller cannot — which is exactly the property you want here, scoped to one named function instead of an entire service-role connection.

GIN index strategy matched to access pattern

The index strategy follows the access pattern. Within-tenant search uses a single GIN index on the tsvector column for the whole table, with the tenant filter enforced by RLS. Cross-tenant catalog search uses the same GIN index, called from the SECURITY DEFINER function, with a partial-index variant filtered on is_published = true AND deleted_at IS NULL if the catalog is materially smaller than the table. The tsvector itself lives in a generated column so Postgres maintains it automatically on write — Supabase's FTS guide recommends this pattern specifically because it removes the "did we forget to update the search vector" class of bug.

ALTER TABLE listings ADD COLUMN search_vector tsvector;

-- Maintain the tsvector via a trigger so writers do not have to remember.
-- (Supabase's FTS guide also documents a generated-column form; pick the
-- one that fits your migration tooling.)
CREATE OR REPLACE FUNCTION listings_search_vector_refresh()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  NEW.search_vector :=
    setweight(to_tsvector('english', coalesce(NEW.title, '')),       'A') ||
    setweight(to_tsvector('english', coalesce(NEW.description, '')), 'B');
  RETURN NEW;
END;
$$;

CREATE TRIGGER listings_search_vector_bi
BEFORE INSERT OR UPDATE OF title, description
ON listings
FOR EACH ROW EXECUTE FUNCTION listings_search_vector_refresh();

-- Within-tenant search index (used by the RLS-protected path).
CREATE INDEX listings_search_vector_gin
  ON listings USING GIN (search_vector);

-- Cross-tenant catalog index — narrower, faster for the catalog query.
CREATE INDEX listings_catalog_search_gin
  ON listings USING GIN (search_vector)
  WHERE is_published = true AND deleted_at IS NULL;

Two index choices worth flagging. First, the setweight calls let ts_rank boost title matches over description matches without changing the query, which is the cheapest ranking improvement available in Postgres FTS. Second, the partial cross-tenant index pays off when the catalog is, say, 10% of total rows — at that ratio, the partial index is roughly 10x smaller and disk-cache-friendlier than the full index, and the planner picks it for the catalog path automatically.

Materialized views for snapshot reads

For features that need a pre-aggregated cross-tenant view — leaderboards, public profile counts, "top N" widgets — the materialized view pattern is the right tool. The view is a snapshot, refreshed on a cadence that matches your freshness budget, and refreshed CONCURRENTLY so reads stay available during the refresh.

CREATE MATERIALIZED VIEW public_creator_profile AS
SELECT
  c.id                   AS creator_id,
  c.tenant_id,
  c.display_name,
  count(p.id)            AS published_post_count,
  max(p.published_at)    AS last_published_at,
  to_tsvector('english',
    coalesce(c.display_name, '') || ' ' || coalesce(c.bio, '')
  ) AS profile_search_vector
FROM creators c
LEFT JOIN posts p
  ON p.creator_id = c.id
 AND p.is_published = true
 AND p.deleted_at IS NULL
WHERE c.is_public_profile = true
  AND c.deleted_at IS NULL
GROUP BY c.id;

-- Required by REFRESH ... CONCURRENTLY.
CREATE UNIQUE INDEX public_creator_profile_pk
  ON public_creator_profile (creator_id);

CREATE INDEX public_creator_profile_search
  ON public_creator_profile USING GIN (profile_search_vector);

-- Refresh on a schedule that matches the freshness budget.
REFRESH MATERIALIZED VIEW CONCURRENTLY public_creator_profile;

The unique index is non-negotiable for CONCURRENTLY — Postgres documents that the option is only allowed when at least one UNIQUE index on the view uses only column names and covers every row. Skipping that index is the most common production failure mode for materialized views: the first refresh works (without CONCURRENTLY), the second one (added later for performance) errors out, and now you are shipping an emergency migration during a Friday afternoon refresh.

External search backends inherit visibility from a view

When you do need an external search backend — typo tolerance, deep faceting, ranking on user signals — Meilisearch and Typesense both work, and both have the same multi-tenant trap. The trap is treating the external index as the source of truth for visibility. It is not. The source of truth is Postgres, and the external index is a derived projection of Postgres that must respect the same visibility rules at write time.

// indexer.ts — runs in a background worker, with a connection that
// reads through the SECURITY DEFINER function (or the catalog view),
// not directly from the raw table with a service-role bypass.
async function reindexListing(listingId: string): Promise<void> {
  const row = await db.query<CatalogRow>(
    `SELECT listing_id, tenant_id, title, description
       FROM catalog_view
      WHERE listing_id = $1`,
    [listingId],
  );

  if (row.rows.length === 0) {
    // Row is no longer in the catalog (deleted, tenant suspended,
    // unpublished). Remove it from the external index too.
    await meili.index("listings").deleteDocument(listingId);
    return;
  }

  await meili.index("listings").addDocuments([row.rows[0]]);
}

// search.ts — every query is filtered by the caller's allowed-tenant
// set, constructed server-side from the authenticated session.
async function searchListings(
  session: AuthedSession,
  query: string,
): Promise<SearchHit[]> {
  const allowedTenants = await resolveAllowedTenantsForSearch(session);
  // The filter is constructed from the session, not from the request body.
  const filter = `tenant_id IN [${allowedTenants.map((t) => `"${t}"`).join(",")}]`;

  const result = await meili.index("listings").search(query, {
    filter,
    limit: 50,
  });

  return result.hits;
}

resolveAllowedTenantsForSearch returns one of three things: the single tenant the user is logged into (within-tenant search), the global "catalog" pseudo-tenant tag for public marketplace search, or a union for users with multi-tenant access. The function lives next to the auth layer and is the single source of truth for "who can search what" — the search handler does not read tenant filters from the client. That is the rule that prevents the IDOR-shaped bug where a user changes a tenant ID in the request and gets back another tenant's data.