# Multi-tenant data with row-level security

> Tenancy is how a database lays out each customer's data for Bonnard. The options are row-level security, a dataset or database per tenant, or none.

Updated: 2026-10-14

Tenancy lets one app serve all your customers, while each customer gets only its own data.

When someone asks a question, Bonnard runs the query as their customer:

1. Bonnard reads the customer's tenant, the value that marks its data in your database, from **Tenant binding** on
   the **Customers** page.
2. Your row-level security policies filter the rows to that tenant, or the query reads only the tenant's own dataset
   or database.
3. If Bonnard finds no tenant, it refuses the query.

In the console at `app.bonnard.ai`, you select the tenancy when you connect a database, and change it on its **Tenancy**
tab.

## Row-level security

Row-level security, **Multi-tenant (RLS)** in the console, keeps all customers in shared tables. Before each query,
Bonnard puts the tenant in a setting that your policies read. On SQL Server,
a tenant has 128 characters or fewer.

Table: The setting that your policies read

| Database | Setting |
| --- | --- |
| PostgreSQL, Amazon Redshift | `current_setting('app.tenant_id')` |
| Microsoft SQL Server | `SESSION_CONTEXT(N'tenant_id')` |

The **See setup SQL** link in the console gives this SQL for PostgreSQL:

```sql
-- Repeat for each table with customer rows.
-- FORCE applies the policy to the table owner too
ALTER TABLE public.orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.orders FORCE ROW LEVEL SECURITY;

-- Return only the rows of the tenant.
-- Cast to the type of tenant_id, for example ::int
CREATE POLICY tenant_isolation ON public.orders
  FOR SELECT USING (
    tenant_id = current_setting('app.tenant_id')::text
  );
```

Bonnard's [read-only user](https://bonnard.ai/docs/connect-data/postgresql.md#1-create-a-read-only-user) must not bypass the policies. On
PostgreSQL, a superuser or a role with `BYPASSRLS` bypasses them
([PostgreSQL row security](https://www.postgresql.org/docs/current/ddl-rowsecurity.html)). On SQL Server, keep the
login out of `sysadmin` and `db_owner`, and do not grant it `ALTER ANY SECURITY POLICY`. If a SQL Server policy reads a
key other than `tenant_id`, Bonnard refuses the app's queries.

## Dataset per tenant on BigQuery

With Dataset per tenant, each customer has its own BigQuery dataset and service account. Bonnard queries as the
customer's service account, so grant each one only its own dataset. Map each customer to both on the app's **Audience**
card. While its row lacks one, the customer gets no data, and **Customers** shows the app as **Needs setup**. See
[Audience](https://bonnard.ai/docs/apps/audience.md#bigquery-with-dataset-per-tenant).

## Database per tenant on Azure SQL Database

With Database per tenant, each customer has its own database, named by a pattern, for example `customer_{tenant}`.
Bonnard offers it on Azure SQL Database only, which refuses queries that name another database.
When you save the tenancy, Bonnard reads the server's edition. On other SQL Server editions, use row-level security.

## No tenancy

No tenancy gives everyone the same data. Use it for your own team, or for data that all customers
can read.

## Tenancy options by database

Table: Which database offers which option

| Database | Tenancy options |
| --- | --- |
| PostgreSQL | No tenancy, Multi-tenant (RLS) |
| Amazon Redshift | No tenancy, Multi-tenant (RLS) |
| Microsoft SQL Server | No tenancy, Multi-tenant (RLS), Database per tenant (Azure SQL Database only) |
| BigQuery | No tenancy, Dataset per tenant |
| ClickHouse | No tenancy |
| DuckDB / MotherDuck | No tenancy |
| MySQL / MariaDB | No tenancy |

## Related

- [Audience](https://bonnard.ai/docs/apps/audience.md): the audiences that each tenancy offers.
- [Publish and staging](https://bonnard.ai/docs/apps/publish-and-staging.md): try an app as one customer.