Enforce tenancy in the database

Tenant isolation

AskDB’s tenant check is a heuristic. It catches a model that forgot the tenant filter, but it can’t prove a query is scoped. This guide makes Postgres the tenant boundary instead: row-level security keeps every query to the caller’s tenants, whatever SQL arrives.

Before you start

A tenant policy and a scope. Your schema artifact has a tenant policy, and every ask() call passes a tenantScope. See Scope queries to a tenant, and What enforces isolation for how this guide fits with AskDB’s check.
A read-only role for generated SQL. Here it’s askdb_reader, set up as in Database role. It must not own the tenant tables, be a superuser, or have BYPASSRLS: Postgres skips row-level security for all three.
PostgreSQL 15 or later if the role reads views, for security_invoker. The examples were tested on PostgreSQL 17.

The examples use tables like those in Scope queries to a tenant: organizations (the tenant root) and documents, with an organization_id column. Here the IDs are integers, not that guide’s strings such as org_abc123, and a comments table reaches its organization through document_id.

Keep enforcement: strict in your tenant policy. AskDB’s check still stops most unscoped queries before they reach the database, so a model mistake shows up as an error you can log, and the database makes the mistakes that get past it harmless.

Add a policy to each tenant table

Enable row-level security on every root and scoped table in your tenant policy. Then add a policy that reads the request’s tenant IDs from a setting, here app.organization_ids:

ALTER TABLE organizations ENABLE ROW LEVEL SECURITY;
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
ALTER TABLE comments ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_read ON organizations FOR SELECT TO askdb_reader
  USING (id = ANY (string_to_array(NULLIF(current_setting('app.organization_ids', true), ''), ',')::integer[]));

CREATE POLICY tenant_read ON documents FOR SELECT TO askdb_reader
  USING (organization_id = ANY (string_to_array(NULLIF(current_setting('app.organization_ids', true), ''), ',')::integer[]));

-- comments has no organization_id: a comment belongs to its document's organization.
-- The subquery reads documents under documents' own policy.
CREATE POLICY tenant_read ON comments FOR SELECT TO askdb_reader
  USING (EXISTS (SELECT 1 FROM documents d WHERE d.id = comments.document_id));

The USING expression keeps the policy closed:

Several IDs. The setting holds a comma-separated list, so a user who sees 40 organizations gets one setting with 40 IDs. Cast the list to your ID column’s type: integer[], bigint[] or uuid[]. For text IDs, leave out the cast, and refuse an ID that contains a comma before you set it.
Unset matches nothing. current_setting(…, true) returns NULL when the setting was never set in the session, instead of raising an error. A pooled connection that ran an earlier request holds '' instead, and NULLIF turns that into NULL. = ANY (NULL) is never true, so a query with no tenant set gets no rows, not every row.
A bad value fails loudly. A setting such as 1,x, which doesn’t parse as the column’s type, raises an error instead of matching.

With a subtree scope, the IDs come per tenant root. Use one setting per root, such as app.agency_ids and app.client_ids, read by the policies on that root’s tables. Expand the scope once per request with expandTenantScope() from @askdb/core, the same expansion ask() runs, and use the result in both places:

import { expandTenantScope, loadSchema } from "@askdb/core";

const schema = loadSchema("./my-app.schema"); // once, at startup

const tenantScope = await expandTenantScope(schema.tenantPolicy!, subtreeScope, resolveTenantDescendants);
const result = await askdb.ask(question, { tenantScope });
// tenantScope.access is now a multi_root scope: one { tenantRoot, ids } entry per root,
// with the seed IDs included. Set each root's setting from its entry's ids.

A root the resolver returned no IDs for keeps an empty entry, so its setting is empty and its tables return no rows.

Enabling row-level security affects every role that doesn’t own the table or bypass it. A role with no policy of its own sees no rows. If your application reads these tables as another such role, give that role a policy before you enable row-level security in production.

Stop the query from changing its tenant

A policy keyed on a setting holds only while the SQL it guards can’t change that setting. By default every role can change it in two ways from a single statement:

set_config(). One generated SELECT can call it to set its own tenant IDs before it reads.
A DO block. PL/pgSQL code in a DO statement can run SET LOCAL, read every tenant’s rows, and return them in an error or notice message.

This is documented Postgres behavior, not an AskDB bug. AskDB’s checks already reject both: set_config is on the function denylist, a Unicode-escaped spelling such as U&"set\005fconfig" is refused as SQL_UNICODE_ESCAPED_IDENTIFIER, and DO doesn’t start with SELECT or WITH. But those checks are heuristics. Close the gap in the database: revoke both from PUBLIC, then grant them back to the roles that need them.

REVOKE EXECUTE ON FUNCTION pg_catalog.set_config(text, text, boolean) FROM PUBLIC;
REVOKE USAGE ON LANGUAGE plpgsql FROM PUBLIC;

-- Grant them back to each role that needs them. Never to askdb_reader.
GRANT EXECUTE ON FUNCTION pg_catalog.set_config(text, text, boolean) TO app_admin;
GRANT USAGE ON LANGUAGE plpgsql TO app_admin;

Without USAGE on PL/pgSQL, a role can’t run a DO block or create a PL/pgSQL function, but it can still call the functions that exist. If you installed another procedural language that runs DO blocks, such as PL/Perl, revoke its USAGE from PUBLIC too.

Your application sets the tenant with a SET LOCAL statement instead, which needs neither privilege. Generated SQL can’t run that statement as long as you run the SQL as a single statement: see Run each query in its own transaction.

Run the REVOKE statements as a superuser, or as the owner of set_config and of the language. Run by any other role, Postgres prints a warning such as WARNING: no privileges could be revoked for "set_config" and changes nothing. On a managed service you may not have such a role. Checks 5 and 6 in Check that it works show whether the revokes took effect.

Close the bypasses

Some roles and objects skip row-level security. Each of these returns every tenant’s rows, with no error:

The table owner. Postgres doesn’t apply a table’s policies to its owner. Run AskDB’s SQL as a role that doesn’t own the tables. If some code must read them as the owner, ALTER TABLE documents FORCE ROW LEVEL SECURITY; applies the policies to the owner too. Under FORCE, an owner with no policy of its own sees no rows, so check what else connects as the owner before you turn it on.
Superusers and BYPASSRLS roles. They skip row-level security even under FORCE. Never run AskDB’s SQL as one.
Views. A view reads its tables with its owner’s rights, so a view the table owner created skips the policies. Make each view the role can read run as the caller: ALTER VIEW document_counts SET (security_invoker = true);.
Materialized views. They hold a copy their owner read, with every tenant’s rows, and take no security_invoker. Don’t grant them to askdb_reader.
Foreign tables. Postgres doesn’t support row-level security on a foreign table, so the remote server decides what it returns. Don’t grant them to askdb_reader.
SECURITY DEFINER functions. They run with their owner’s rights, and new functions are executable by PUBLIC. Revoke EXECUTE on them from PUBLIC, and grant it to the roles that call them. A revoke from askdb_reader alone changes nothing, because the role keeps the PUBLIC grant. To keep functions created later from being executable by PUBLIC, run ALTER DEFAULT PRIVILEGES REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC; as the role that creates them. Check 7 lists the ones askdb_reader can still call.
Tables without row-level security. A table you grant to askdb_reader without enabling row-level security returns every row. Enable it, or don’t grant the table.

Run each query in its own transaction

For each request, open a read-only transaction, set the tenant IDs with SET LOCAL, run the generated SQL, and roll back:

BEGIN READ ONLY;
SET LOCAL app.organization_ids = '1,3';
-- run the SQL AskDB generated here
ROLLBACK;

Use SET LOCAL, never SET. SET LOCAL lasts until the transaction ends, so the next request on a pooled connection starts with no tenant and sees no rows. A plain SET lasts for the whole session: the connection goes back to the pool still set to this request’s tenants, and the next request that doesn’t set its own reads their rows.

In node-postgres, with askdb created by createAskDb() as in Embed in a Node app:

import { Pool, type QueryConfig } from "pg";

// Connects as askdb_reader.
const pool = new Pool({ connectionString: process.env.READER_DATABASE_URL, statement_timeout: 5000 });

// organizationIds: the organizations your application authorized for this user.
export async function askForOrganizations(question: string, organizationIds: string[]) {
  const result = await askdb.ask(question, {
    tenantScope: {
      access: { kind: "ids", tenantRoot: "table:public.organizations", ids: organizationIds },
    },
  });

  const client = await pool.connect();
  try {
    await client.query("BEGIN READ ONLY");
    // SET takes no bind parameters, so quote the value.
    await client.query(`SET LOCAL app.organization_ids = ${client.escapeLiteral(organizationIds.join(","))}`);
    // The extended protocol accepts exactly one statement, even with no parameters.
    const query = { text: result.sql, values: result.tenantParams, queryMode: "extended" } as QueryConfig;
    const { rows } = await client.query(query);
    return rows;
  } finally {
    // ROLLBACK ends the transaction and clears the setting. If it fails, close the connection.
    const ended = await client.query("ROLLBACK").then(() => true, () => false);
    client.release(!ended);
  }
}

Set the IDs you authorized. The setting and tenantScope take the same IDs, from your own authorization. Don’t read them from result.tenantBindings: it lists only the placeholders the model wrote, so a query that left out the filter would set no tenant.
Quote the value. SET can’t take a bind parameter, so escapeLiteral quotes it. Never put an ID into the statement unquoted.
Run one statement. node-postgres sends a query with no parameters through the simple protocol, which runs every statement in the string. A query that reads only global tables has no tenant parameters, so a stacked SET LOCAL app.organization_ids = '…'; SELECT … would reset the tenant. queryMode: "extended" (node-postgres 8.12 or later) makes Postgres refuse it with cannot insert multiple commands into a prepared statement. AskDB’s single-statement check rejects a stacked statement first, but that check is a heuristic too. @types/pg doesn’t list queryMode, which is why the query is cast to QueryConfig. One statement can still be a DO block, which the PL/pgSQL revoke in Stop the query from changing its tenant blocks.

Check that it works

Run these checks against a real database before you rely on the setup, and again after a schema change. A policy that is enabled but doesn’t apply looks the same as one that works until you test it.

The role can’t skip the policies. Both columns must be false:

SELECT rolsuper, rolbypassrls FROM pg_roles WHERE rolname = 'askdb_reader';

Every relation the role can read is protected. Every table (relkind r or p) must have relrowsecurity set to true, unless your tenant policy lists it under globalTables. Every view (v) must have security_invoker=true in reloptions. No materialized view (m) or foreign table (f) may appear. The query also lists a table the role can read only some columns of:
```
SELECT c.oid::regclass AS relation, c.relkind, c.relrowsecurity, c.reloptions
FROM pg_class c
WHERE c.relkind IN ('r', 'p', 'v', 'm', 'f')
  AND c.relnamespace NOT IN ('pg_catalog'::regnamespace, 'information_schema'::regnamespace)
  AND has_any_column_privilege('askdb_reader', c.oid, 'SELECT')
ORDER BY 1;
```
A tenant sees only its own rows. Connect as askdb_reader and run a query with no tenant filter. Only organization 1 may appear. Repeat it for each tenant table:
```
BEGIN READ ONLY;
SET LOCAL app.organization_ids = '1';
SELECT organization_id, count(*) FROM documents GROUP BY organization_id;
ROLLBACK;
```
No tenant sees nothing. As askdb_reader, on a connection that hasn’t set the tenant, this must return 0:
```
SELECT count(*) FROM documents;
```

The query can’t change its tenant. As askdb_reader, the first transaction must fail with permission denied for function set_config. If it returns rows for organizations 2 and 3, the revoke didn’t take effect. The second must fail with permission denied for language plpgsql:

BEGIN READ ONLY;
SET LOCAL app.organization_ids = '1';
SELECT d.organization_id
FROM (SELECT set_config('app.organization_ids', '1,2,3', true)) AS s
CROSS JOIN LATERAL (SELECT organization_id FROM documents OFFSET 0) AS d;
ROLLBACK;

BEGIN READ ONLY;
SET LOCAL app.organization_ids = '1';
DO $$ BEGIN SET LOCAL app.organization_ids = '1,2,3'; END $$;
ROLLBACK;

Only the roles that need them can change the setting. The first query lists the roles that can call set_config: askdb_reader must not be in it, and superusers always are. The second lists the languages with DO blocks that askdb_reader can use, and must return no rows:

SELECT rolname
FROM pg_roles
WHERE has_function_privilege(oid, 'pg_catalog.set_config(text, text, boolean)', 'EXECUTE')
  AND rolname NOT LIKE 'pg\_%'
ORDER BY rolname;

SELECT lanname
FROM pg_language
WHERE laninline <> 0
  AND has_language_privilege('askdb_reader', oid, 'USAGE');

No SECURITY DEFINER function reads around the policies. This lists the functions that run with their owner’s rights and that askdb_reader can call. Revoke EXECUTE from PUBLIC on each one that can read a tenant table:

SELECT p.oid::regprocedure AS function_name, pg_get_userbyid(p.proowner) AS owner
FROM pg_proc p
WHERE p.prosecdef
  AND has_function_privilege('askdb_reader', p.oid, 'EXECUTE')
  AND p.pronamespace NOT IN ('pg_catalog'::regnamespace, 'information_schema'::regnamespace)
ORDER BY 1;

Other engines

This recipe is for Postgres. On every engine, AskDB’s tenant check stays a heuristic, so the database or your application must keep each query to its tenants. Tenant isolation says what CockroachDB, SQL Server, MySQL, MariaDB and SQLite offer.