Tenant isolation & SQL hardening

The Genie Assistant lets users ask questions in natural language; the model generates SQL and the engine runs it. By default the generated SQL runs on the app’s connection and tenant scope is only suggested to the model in its system prompt. That is convenient for a trusted or single-tenant demo, but a model that hallucinates, is jailbroken, or is prompt-injected could omit the CompanyId filter and read another tenant’s rows.

Prompt text is not an authorization boundary

A rule in the system prompt is a hint, not a guarantee. For real multi-tenant isolation the database — not the model — must enforce which rows come back. This mirrors the engine-wide disclosure gate and multi-tenant scoping.

This guide makes tenant isolation enforced by the database, using two layers the engine already supports:

1. A dedicated, read-only, least-privilege DB login for the assistant (Assistant:ConnectionString). Even if the SELECT-only validator is bypassed, this login physically cannot write, DDL, or read objects it wasn’t granted.
2. Row-Level Security (RLS) driven by a DB session context. Before running each query the engine pushes the caller’s identity into a session context; RLS policies filter every tenant table by it — so the correct rows come back regardless of what SQL the model wrote.

Both layers are opt-in: with no Assistant:ConnectionString configured the assistant falls back to the app connection and prompt-only scope (unchanged behaviour). Enforcement turns on once you (a) point the assistant at a restricted login and (b) apply the RLS policies below via your host migrations.

How the engine pushes identity

For every assistant query, AssistantQueryExecutor opens the dedicated connection, starts a transaction, and sets a session context before running the model’s SQL:

Key (SQL Server SESSION_CONTEXT)	Key (PostgreSQL GUC)	Value
genie.CompanyId	genie.company_id	the caller’s company id
genie.UserId	genie.user_id	the caller’s user id
genie.IsSystem	genie.is_system	1 for System-role users, else 0

The context is always set (never skipped), so your RLS predicate can fail closed: any connection that did not go through the executor has no context and sees nothing. System-role users are granted full access via the explicit IsSystem flag — not by an absent value.

Division of labour

The engine sets the context; you create the read-only login and the RLS policies (your host owns the physical tables). Apply the DDL below in a host migration or DB bootstrap script.

SQL Server

1. Read-only, least-privilege login

-- A login/user the assistant uses. It can only SELECT, and only on the tables you grant.
CREATE LOGIN genie_assistant_ro WITH PASSWORD = '<strong-secret>';
CREATE USER  genie_assistant_ro FOR LOGIN genie_assistant_ro;

-- Least privilege: read only. Grant SELECT on the business schema(s) the assistant may query,
-- and explicitly DENY the rest (e.g. the auth/identity + engine internals).
GRANT SELECT ON SCHEMA::dbo TO genie_assistant_ro;
DENY  SELECT ON SCHEMA::Genie    TO genie_assistant_ro;   -- engine internals (ViewStore, chats, …)
DENY  SELECT ON SCHEMA::Identity TO genie_assistant_ro;   -- users/roles/tokens
-- (no INSERT/UPDATE/DELETE/EXECUTE granted → writes are impossible)

2. RLS predicate + policy (apply to every tenant table with a CompanyId)

CREATE SCHEMA sec;
GO
-- Returns 1 (row visible) when the caller is System, or the row's company matches the session context.
-- No context set → both SESSION_CONTEXT calls are NULL → returns nothing → fail closed.
CREATE FUNCTION sec.fn_assistant_company_scope(@CompanyId bigint)
RETURNS TABLE WITH SCHEMABINDING
AS RETURN
    SELECT 1 AS ok
    WHERE  TRY_CONVERT(int,    SESSION_CONTEXT(N'genie.IsSystem'))  = 1
       OR  TRY_CONVERT(bigint, SESSION_CONTEXT(N'genie.CompanyId')) = @CompanyId;
GO
-- Repeat this block per tenant table:
CREATE SECURITY POLICY sec.AssistantScope_Product
    ADD FILTER PREDICATE sec.fn_assistant_company_scope(CompanyId) ON dbo.Product
    WITH (STATE = ON);
GO

Filter predicates apply to every login

RLS filter predicates apply to all logins hitting the table. Scope the policy so it only affects the assistant: either gate the predicate on the login (e.g. AND ORIGINAL_LOGIN() = 'genie_assistant_ro') or, cleaner, keep the assistant login separate and only add policies you’re comfortable applying app-wide. Test against your app’s own queries before enabling STATE = ON in production.

PostgreSQL

1. Read-only, least-privilege role

CREATE ROLE genie_assistant_ro LOGIN PASSWORD '<strong-secret>';

GRANT USAGE  ON SCHEMA public TO genie_assistant_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO genie_assistant_ro;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO genie_assistant_ro;
REVOKE ALL ON SCHEMA "Genie", "Identity" FROM genie_assistant_ro;   -- keep internals off-limits
-- no INSERT/UPDATE/DELETE granted → writes are impossible

2. RLS policy (apply to every tenant table with a company_id)

ALTER TABLE public."Product" ENABLE ROW LEVEL SECURITY;
ALTER TABLE public."Product" FORCE ROW LEVEL SECURITY;   -- apply even to the table owner

CREATE POLICY assistant_company_scope ON public."Product"
    FOR SELECT
    USING (
        current_setting('genie.is_system', true) = '1'
        OR "CompanyId" = NULLIF(current_setting('genie.company_id', true), '')::bigint
    );

current_setting(key, true) returns NULL when the key is unset (missing_ok), so with no context the predicate is false → fail closed.

Wire the assistant to the restricted login

Point the assistant at the read-only connection (kept out of source, like the API key):

appsettings.json

"Assistant": {
  "Provider": "DeepSeek",
  // …
  "ConnectionString": ""   // leave empty to use the app connection (prompt-only scope)
}

Set it via environment variable so the secret stays out of config (nested keys map via __):

setx Assistant__ConnectionString "Server=…;User Id=genie_assistant_ro;Password=…;TrustServerCertificate=True"

With it set, AssistantQueryExecutor routes every generated query through that login inside a transaction with the session context applied. With it empty, behaviour is unchanged (prompt-only scope). See Configuration & providers for the full options reference.

What this does and doesn’t cover

• ✅ Writes/DDL: impossible on the read-only login (belt-and-suspenders with the SELECT-only validator in ExecuteQueryTool).
• ✅ Cross-tenant row reads: blocked by RLS regardless of the model’s SQL, once policies are applied.
• ✅ System role: sees all tenants (via the IsSystem flag).
• ✅ Object/column metadata: the schema context the model receives is built from Genie’s modeled metadata (EntityStore) and permission-filtered — a non-System caller only learns about tables reachable through a view they hold View/List on, and engine/identity internals are never modeled there, so they are never described to the model. (Applies whenever EntityStore is populated; a host with no modeled entities falls back to a raw catalog read.)
• ⚠️ You must add an RLS policy for each tenant table you want protected; tables without a policy are unfiltered (but still read-only under the restricted login).

Related

• AI Assistant overview — the tool orchestrator and RBAC-scoped execution.
• Multi-tenant company scoping — how the rest of the engine enforces tenancy in SQL.
• Security model — the layered model this hardening plugs into.