NLQueries GitHub

Blog  / 

Before You Embed NL2SQL for Customers: Tenant Identity, Row Filters, and Licensing

Published . Written for nlqueries-core 0.4.

multi-tenant row-level-security licensing

Picture a SaaS team two sprints away from launching an "Ask your data" box inside its customer dashboard. The demo is good. A customer types "what was paid revenue in October?", the assistant writes SQL, and a tidy number comes back. Then the security reviewer asks one question: when tenant B types the same thing, what stops it from getting tenant A's number? The room goes quiet, because the honest answer is "the model usually adds a WHERE tenant_id = ...."

That answer isn't good enough, and the reason isn't really about the model. Multi-tenant natural-language-to-SQL (NL2SQL) has a trust problem, a database problem, a state problem and a licensing problem, and an embedded chat widget touches all four. This post walks through each one with a single six-row example, and ends with the tests that should fail safely before any customer gets the box.

NLQueries comes up throughout because its product page advertises Enterprise embedding and row filters, and its open Core shows clearly where the hard edges are. This is a design walkthrough, not a working build: no NLQueries Enterprise build, embedding API or customer tenant was available for this article, so there are no endpoint names, SDK calls or token formats here, and nothing below demonstrates an end-to-end deployment. The material comes from vendor documentation, Core 0.4.1 source at a pinned commit, the PostgreSQL docs, and a few small SQL checks whose limits are stated where they appear.

Where the tenant ID comes from decides everything

Start with the most basic question: who tells the system which tenant is asking?

If the answer is "the browser", you've already lost. A tenant ID in a URL, a hidden form field or a JavaScript variable is a request, not an entitlement, and anyone with developer tools can change it. The rule for the whiteboard: a signed identity has to be bound, on the server, to the tenant memberships and agents that user is allowed to use. The client never supplies its own entitlement. It proves who it is, and the server looks up what that person may see.

It's easy to blur when you're wiring an embed, because the quickest path is to pass the tenant along with the session. Resist it. The flow you want: your app authenticates the user, your backend resolves their tenant and role from your own records, and only then does anything downstream, whether the NL2SQL engine, a row filter or a database session, receive a tenant scope that the user had no hand in choosing.

It also helps to be clear about what existing building blocks do and don't give you. NLQueries Core's MCP server, for example, documents real network authentication: OIDC verification, a grants file that maps each identity-provider subject to specific agents and actions, and an audit record for every allowed and denied decision (Authenticating the MCP server). That's useful machinery, but it authorizes agents and actions, not rows. And a Python entry point like Core's run_query, which takes an explicit execution flag and returns a structured result (sync_runner.py), says nothing about how a browser embed authenticates. Neither is evidence of an Enterprise embedding contract, so don't extrapolate your token flow from them.

Get the contract and the rights in writing first

There are two documents you need before you write integration code, and most teams go looking for them too late.

The first is the supported integration contract. NLQueries' Core-vs-Enterprise matrix lists embedded and white-label analytics, REST and webhooks, row filters, and team identity and admin features as Enterprise capabilities (NLQueries product page). That's a vendor claim, not an API reference, and none of those Enterprise capabilities were tested for this article. Before you build, you want the Enterprise build version, the embedding and authentication guide, the OpenAPI or SDK schemas, the configuration reference for row filters, agents and tenants, and how sessions start and end: token issuer, audience and lifetime, allowed embedding origins, logout and revocation. If those aren't written down, any code you write is a guess, and a guessed token claim is exactly the kind of thing that turns into an isolation bug.

The second is the commercial grant. This matters because the obvious shortcut, wrapping Core in your own portal, runs into Core's license. NLQueries Core ships under the Business Source License 1.1. Its Additional Use Grant allows production use provided that the use doesn't include offering the Licensed Work to third parties on a hosted or managed basis, where a third party remotely accesses its functionality as a service. The license names June 4, 2030 as the Change Date and grants Apache 2.0 rights on that date or the fourth anniversary of the specific version's first public distribution, whichever comes first (NLQueries Core LICENSE). A customer-facing "ask your data" feature in a SaaS product looks a lot like the thing that grant excludes. This isn't a legal reading, and the Enterprise commercial terms weren't available for this article. But for a team in that position, the written grant covering its customers, hosting model and any redistribution is the first thing to ask the vendor for, before the first pull request. Source availability doesn't mean hosting rights.

A tenant filter in the generated SQL is not a boundary

Here's the running example: three tables (customers, orders, order_items), with customers 1 and 2 in tenant A and customer 3 in tenant B. The question is "For tenant A, what were paid-order revenue and paid-order count in October 2026?" An order with a NULL amount still counts, but adds nothing to revenue. The fixture is plain SQL and doesn't depend on any NLQueries edition.

-- Sample data (synthetic). SQLite / PostgreSQL compatible.
CREATE TABLE customers(id INTEGER PRIMARY KEY, tenant_id TEXT NOT NULL, region TEXT);
CREATE TABLE orders(id INTEGER PRIMARY KEY, customer_id INTEGER REFERENCES customers(id),
                    order_date TEXT, amount_cents INTEGER, status TEXT);
CREATE TABLE order_items(id INTEGER PRIMARY KEY, order_id INTEGER REFERENCES orders(id), sku TEXT);
INSERT INTO customers VALUES (1,'A','East'),(2,'A','West'),(3,'B','North');
INSERT INTO orders VALUES
  (101,1,'2026-10-01',10000,'PAID'), (102,1,'2026-10-02',5000,'CANCELLED'),
  (103,2,'2026-10-31',20000,'PAID'), (104,3,'2026-10-02',70000,'PAID'),
  (105,1,'2026-11-01',40000,'PAID'), (106,2,'2026-10-15',NULL,'PAID');
INSERT INTO order_items VALUES (1,101,'a'),(2,101,'b'),(3,103,'c');

-- Reference answer for tenant A
SELECT SUM(o.amount_cents) AS revenue_cents, COUNT(*) AS paid_order_count
FROM orders AS o JOIN customers AS c ON c.id = o.customer_id
WHERE c.tenant_id = 'A' AND o.status = 'PAID'
  AND o.order_date >= '2026-10-01' AND o.order_date < '2026-11-01';

Against this fixture the reference query returns (30000, 3) on SQLite 3.45.3, and PostgreSQL 18.6 gives the same answer. Now drop the tenant predicate, the one line a model might forget or a prompt injection might talk it out of, and the same fixture returns (100000, 4). That's tenant B's 70,000-cent order folded into tenant A's answer, with nothing in the output to tell you it happened.

So the tenant predicate in the reference query is just the arithmetic for the expected answer. It isn't an authorization boundary. Reviewing generated SQL is a weak control on its own, too: the dangerous version is shorter, still valid, and returns a plausible number. The boundary has to live somewhere the generated SQL can't reach: an engine-level row filter you've confirmed is enforced, the database itself, or ideally both.

Make the database refuse on its own

On PostgreSQL, the database half usually means row-level security (RLS). The policy set below is custom PostgreSQL, not an NLQueries feature in either edition. It gives tenant A its own login role and filters each table through its parent: customers by tenant, orders through customers, items through orders.

-- Custom PostgreSQL, not an NLQueries Core or Enterprise feature.
-- Each statement was run on PostgreSQL 18.6 with the sample data above; 16 and 17 were not.
-- The reader role must not own these tables, and must not have BYPASSRLS.
CREATE ROLE tenant_a_reader LOGIN NOSUPERUSER NOBYPASSRLS NOCREATEDB NOCREATEROLE;
GRANT SELECT ON customers, orders, order_items TO tenant_a_reader;

ALTER TABLE customers ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders    ENABLE ROW LEVEL SECURITY;

CREATE POLICY customers_tenant_a ON customers FOR SELECT TO tenant_a_reader
  USING (tenant_id = 'A');
-- The subquery on customers is itself filtered by the policy above.
CREATE POLICY orders_tenant_a ON orders FOR SELECT TO tenant_a_reader
  USING (EXISTS (SELECT 1 FROM customers c WHERE c.id = orders.customer_id));
-- Every granted table needs its own policy. order_items is filtered through orders.
ALTER TABLE order_items ENABLE ROW LEVEL SECURITY;
CREATE POLICY items_tenant_a ON order_items FOR SELECT TO tenant_a_reader
 USING (EXISTS (SELECT 1 FROM orders o WHERE o.id=order_items.order_id));
-- Assess FORCE ROW LEVEL SECURITY separately if anything that owns these
-- tables could ever run these queries. It is not covered here.

The order_items policy is the one that's easy to miss. The reference query only touches customers and orders, so policies on those two make the headline number look right while the GRANT quietly hands over a third table with no row filter at all. Row security applies only where it's enabled. The way to catch the gap is a canary: a tenant-B row, planted in a table the reference query doesn't read, that tenant A must never see.

-- As the table owner, plant a tenant-B row (order 104 belongs to customer 3).
INSERT INTO order_items VALUES(99,104,'TENANT_B_CANARY');
SET ROLE tenant_a_reader;
SELECT current_user, id,sku FROM order_items ORDER BY id;
SELECT SUM(o.amount_cents),COUNT(*) FROM orders o WHERE status='PAID' AND order_date>='2026-10-01' AND order_date<'2026-11-01';
SELECT COUNT(*) AS tenant_b_canary_rows_after_fix FROM order_items WHERE id=99;
RESET ROLE;

On PostgreSQL 18.6, with the two order_items statements left out, the reader role got the predicate-free aggregate right at (30000, 3), because orders was already covered, yet a plain SELECT on order_items returned all four rows, including id 99, TENANT_B_CANARY. With those statements in place, the same reader saw ids 1 to 3 and the canary count was zero. A correct aggregate proves nothing about the tables it didn't read.

Treat that check as a starting point, not a certificate. It covered one tenant role on one PostgreSQL version. It didn't probe customers directly, try other joins, create a tenant B role or run on PostgreSQL 16 or 17, and the owner's own query path remains a separate decision. All of that belongs in your own test run, with a canary in every table the reader can see. Per-tenant roles also cost something: a role, grants and policies for every tenant, and pooled connections that can't move between tenants without a reset. That's the price of a boundary the query can't argue its way past.

PostgreSQL's own documentation is explicit that superusers and roles with BYPASSRLS skip row policies, and that table owners normally do too (PostgreSQL 18: Row Security Policies). NLQueries' hardening guide makes the same point for its reader role, then adds a caveat that deserves underlining: a policy keyed on a session variable that submitted SQL can itself change is not a boundary. It recommends a per-tenant login, or setting the value in a connection hook the query can't reach and resetting pooled connections when they're returned (Hardening the database NLQueries reads). That's why the policy set above uses a per-tenant role rather than a current_setting('app.tenant_id') policy. Core's SQL policy is an allowlist that refuses session variables and functions it doesn't list, and at Core 0.4.1 it rejects SELECT current_setting('app.tenant_id') as an unrecognised function. Defense in depth still means not betting tenant isolation on a parser.

Keep the two layers distinct in your docs. An engine row filter and database RLS are separate mechanisms, and the database version is the one a security reviewer can inspect without trusting the application. Enterprise's row-filter implementation wasn't available for this article, so verify it as its own control.

Caches, history and exports leak quietly

Even with the SQL boundary right, an NL2SQL system keeps state: caches, conversation history, looked-up values, documents, exports and audit logs. Each is a cross-tenant path that never shows up in the generated SQL.

NLQueries' Core architecture notes are unusually candid about this. The semantic cache is partitioned per agent, and every entry is readable by anyone who may query that agent. That's fine as long as the agent is the unit of authorization, since cached SQL replays through the same filtered connector. The document then sets an invariant: anything that makes two callers of the same agent entitled to different results (per-user row filters, per-user document ACLs, RLS keyed on caller identity, per-user column masking) must contribute its distinguishing value to cache_context on both reads and writes, and the comparison is an equality, not a subset test. "If it cannot, it must not be built" (NLQueries architecture: cache partitioning). The connector interface carries the same idea further down. A wrapper that restricts rows per request has to declare a cache_scope, so looked-up literal values aren't shared across requests that may see different rows, and it records rewritten statements in executed_sql (connectors/base.py).

Rows aren't the only cached thing. In a small Core 0.4.1 check with a stubbed cache lookup, a cache hit re-ran the stored SQL when execution was allowed, so after the fixture changed the rows came back as (32000, 3) while the stored narrative still read "Revenue was 30000 cents." Persistent cache retrieval wasn't part of that check, but the point stands: generated prose is state too, and it carries whatever data it was written from.

Read all of this as a design lesson, not a product guarantee. It describes Core's contract at one commit, and whether every Enterprise path honors it for tenants is exactly what you'd test. The practical tool is an entitlement matrix: list every surface where data lives or leaves, and write down what it's keyed on.

Surface What must scope it What failure looks like
Generated SQL and returned rows Server-side tenant identity enforced below the model B's question returns A's rows
Semantic/answer cache Tenant (and any per-user entitlement) on read and write B gets A's cached answer or narrative
Looked-up values / grounding Same row scope as the query A's customer names suggested to B
Conversation history User and tenant A follow-up reuses another tenant's context
Documents Per-tenant ACL A citation from A's runbook in B's answer
Exports and downloads Same entitlement as the view A CSV with more rows than the screen showed
Audit logs Admin role, with elevated access audited Tenants read each other's question history

The last row deserves its own thought, because support staff will sometimes need to see across tenants. Give them an explicit, audited elevated path rather than making the customer-facing database principal an owner or a bypass role "just for admins". Core's connectors/base.py notes that its base connector emits a query_execution audit event on every call, including ones the policy refuses. That's the kind of record you want for elevated access too, wherever your build produces it.

The tests that should fail safely before launch

A tenant claim is only as good as the attempts to break it, and the visible SQL predicate is the least interesting thing to check. Here's a plan for a real Enterprise test environment using the six-row fixture, with tenant A, tenant B and a user whose access gets revoked partway through. None of it has been run against an Enterprise build.

  1. Contract gate. Before any test, confirm you have the versioned embedding and authentication documentation and the written commercial grant. If either is missing, stop. Don't fill gaps with guessed endpoints.
  2. Two-tenant isolation. Plant a tenant-B canary in every table tenant A could reach. Authenticate as A and as B and ask identical questions. Then tamper: edit tenant and agent references, swap session tokens, and ask for the other tenant's rows by name. Check the executed SQL and the rows that came back. Then exercise schema browsing, documents, exports and audit access the same way.
  3. Lifecycle and cache. Warm the cache as A, then ask the same question as B. Change a user's role and repeat. Replay an old token after logout. Check that pooled database connections don't carry one tenant's session state into the next request.

The pass criterion is deliberately unforgiving: any cross-tenant content, whether rows, a narrative, a citation, a cached answer or a download, is a failure. As the canary showed, a correct headline number can't earn a pass on its own. Keep the evidence: exact build versions, configurations and redacted outputs. "We tested isolation" means nothing six months later without the traces.

What to publish, and what to hold back

When you write this up, internally or for customers, describe only the integration you've actually demonstrated. Without the embedding contract, the honest artifact is a sequence diagram with generic boxes, an entitlement matrix and a test plan, not a code sample with invented endpoint names. Once the contract and build arrive, the tutorial writes itself: the real authentication bootstrap, the real row-filter configuration and the redacted results of the three tests above.

Also keep the editions apart. A Core wrapper you build doesn't inherit Enterprise tenancy features, and Core's source tells you nothing certain about Enterprise. If you go the Core route, the identity bridge, per-tenant cache context, row enforcement and audit are your code, and you'd still have the BSL hosted-service question to settle.

Your next step

This week, do two things in parallel. Send the vendor a short written request for the Enterprise build version, the embedding and authentication guide, the row-filter and tenant configuration reference, and a hosting grant that names your customer-facing use. Meanwhile, load the six-row fixture into a scratch PostgreSQL database, create a non-owner, NOBYPASSRLS role per tenant, and enable RLS with a policy on every table you grant. Confirm the reference query returns (30000, 3) for tenant A and that the predicate-free version returns (30000, 3) under tenant A's role instead of (100000, 4). Then plant a tenant-B canary in each of the three tables and confirm tenant A's role counts zero of them, directly and through joins. Once the database refuses to leak on its own, you'll be in a much better position to judge what any embedding layer adds on top.

References

All posts · RSS feed · Documentation