Lesson

Configuring permissions for different customer schemas

Learn how to set up permissions for a one-schema-per-customer model.

Most organizations that use Metabase to deliver multi-tenant analytics to their customers will use a single database schema and row- and/or column-level data sandboxing. This is the most straightforward route, and if all your customer data is in the same schema and on the same tables (often referred to as “data commingling”), data sandboxing is the way to go.

However, if your customer data is stored in separate tables in the same schema or different schemas within one database, you’ll need another option for structuring permissions in Metabase. Which route you take depends on whether or not your customers need to access Metabase’s native SQL editor.

If you only have a handful of customers, it will be easiest to configure permissions manually, as we’ll walk through below. But if you’re catering to hundreds or thousands of customers, consider scripting this process using the Metabase API. If you need to set up a new Metabase instance or want to replicate the same dashboards and saved questions for each of your customers, Metabase Pro or Enterprise Edition’s serialization feature can help speed things along. Keep in mind that serialization does not include permissions settings, so you’ll need to either script or manually configure which groups can access each table.

Option 1: Granting customers self-service or view-only access to their schema

Let’s say that you have a single database that contains ten different tables, and each of these tables corresponds to one customer (in this case, different companies). Our goal is to make sure that each customer can only access the table that contains their own information.

Note: The method below assumes that your customers do not need access to Metabase’s native SQL editor and will instead use the query builder to ask simple and custom questions. If your customers need native SQL access, jump to Option 2.

  1. If you haven’t already, connect your data source to Metabase.

  2. In the Admin settings > Permissions > Data, revoke the All Users group’s access to the database.

  3. In the Admin settings > People, create a group for your first customer. You may only need one group for each company, but if your customers want some of their employees to be able to ask questions and others to only view them, you’ll need to create multiple groups per customer so that you can set different levels of permissions. Naming your groups according to some defined convention can help you stay organized — consider something like Company A (Self-service) and Company A (View only).

  4. Return to the Permissions > Data > Databases page, then grant your newly created group access to the table that corresponds with the correct customer. If you want your customers to be able to ask questions and create dashboards within their table, change their permissions to Unrestricted. In this context, “unrestricted” refers to the table, not the database as a whole.

    If your customers have some employees who should only view data (rather than have self-service access), you’ll need to create collections to house those specific questions and dashboards. See collection permissions.

    Avoid granting your groups access to the native SQL editor, as this native query editing allows people to query tables that they can’t see in Metabase and shouldn’t have access to.

  5. Invite your first user, adding them to the appropriate group in the process. If you’re using SSO to handle account creation, you won’t need to manually invite users.

  6. Repeat steps 3–5 to add the user accounts for each of your ten customers.

If you grant a group access to all tables within a database and later modify your database to include a new table, Metabase will default to giving that group access to the new table. However, if you’ve scoped each group’s permissions to a single table — like we did above — Metabase will default to hiding any new tables that you add.

Option 2: Granting customers native SQL access to their schema

Since Metabase’s SQL editor requires unrestricted access to your database as a whole, enabling it with the above method would let your customers query tables that don’t belong to them. If native SQL queries are a must for your customers:

  1. Create a user account at the database level — not in Metabase — for your first customer. This user account should only have permission to access their specific tables or schema within the database. For example, if you have a Postgres database, you’ll need to add a user to your database via psql. In Postgres you’ll then give them permissions only to the table(s) you want the customer to see.

  2. In Metabase, add a connection to your database with the user account you just created in your database.

  3. Make a new group in Metabase, and grant that group access to your newly added database — i.e. the connection that corresponds to that customer’s schema. Since the user account at the database level dictates what your customer can view, you can grant the group Unrestricted access to the database and Native query editing access along with it.

    People who belong to that group will be able to see all the tables in Metabase that the Postgres user (or whatever database you’re using) has access to in the database. If you want to hide a table later on, you’ll need to change permissions in the database itself, not in Metabase. Even if you hid a table in Metabase, a person with native access could still query them.

  4. Invite your first user, adding them to the appropriate group in the process. If you’re using SSO to handle account creation, you won’t need to manually invite users.

  5. Repeat steps 1–4 for the rest of your customers. In Metabase, it will look like you’ve added as many databases as you have customers.

Sandboxing incompatibility

Data sandboxing grants filtered access within tables on a row- or column-level basis according to a specific user attribute. Your setup is different though — you have different schemas for each customer, and those customers each access specific table(s) that contain the data they need. If your customers are each accessing entirely different tables, data sandboxing isn’t an option.

Further reading

Thanks for your feedback!

Get articles like this one in your inbox every month