Using Impersonation to execute queries based on search path set on PostgreSQL role

[AakashMahesha]

Hi,
We are using Metabase Enterprise version and I’m trying to apply Impersonation Permissions on my PostgreSQL databases.

we want to provide multi-tenant data permission for our product. On our databases, we have a schema for each tenant and each schema has same and equal number of tables.

to execute impersonation, we’ve created a role on postgres for a particular schema, granted usage and select access on that schema, and set the search_path of that role to that same schema and public.

For example, this is how the search path for that role looks like
{search_path = tenant_schema, public}

Now, I was able to verify if the impersonation is working or not, by creating two seperate charts using two different schemas, and only the chart that is powered by the impersonated schema shows the data. That’s all good.

But when I try to build a schema agnostic chart, like
SELECT * FROM table1;

now, it throws an error saying not able to find “table1”. Why is this happening?

How can I build such charts so that I can provide true multi-tenant data permissions?

[dwhitemv]

We’ve been discussing this idea in this thread:

Reuse across schemas

Just getting started with Metabase. We have a database where each tenant in our service is in a separate named schema, to keep client data segregated. Each tenant’s schema table structure is exactly the same. It is a single database connection. How can we set up models/metrics/questions to be reused across the schemas?

The biggest problem is that Metabase is aware of schemas and trying to dupe it by using search_path breaks its assumptions. It works with independent database connections as the metabase is maintained separately, but impersonation reuses the metadata. It knows that ‘schema1.table1’ and ‘schema2.table1’ are different tables.

There may be some related issues (that you may be experiencing), especially if a proxy like pgbouncer is in use, with the search_path setting getting cleared or reset. We ran into problems with this with TimeZone getting reset.

Tenant-per-schema is the next frontier for Metabase multitenant support. Paid Metabase folks, start beating that drum