Multi-tenant data with row-level security

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.

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

-- 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 must not bypass the policies. On PostgreSQL, a superuser or a role with BYPASSRLS bypasses them (PostgreSQL row security). 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.

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

Related

• Audience: the audiences that each tenancy offers.
• Publish and staging: try an app as one customer.