1

Hi all,

Thanks for a very cool product. I am wondering if it is possible (designed or not) to intercept a query. My scenario is that I have a tenancy model including multiple tenants in the same postgres database, only discriminated by a table-level tenant id. So, I either have to intercept the queries that Redash fires in order to append a tenant id to the where clause, or I will need to separate my tenants into actual databases so that they are partitioned at the connection level instead.

Any advice on potential techniques or alternate approaches here? Thanks in advance for any help.

Regards,
Cory

2

Hey Cory,

although your inquiry might be outdated by now … here are a few hints. I am assuming that you want to re-use queries without modification and somehow transparently “inject” the tenant id into the queries, but will list all alternatives I can think of right now.

Options:

  • use tenant-specific views: requires database-level definition of tenant-specific views and redash query-level usage of those views; the views contain conditions specific to selecting a particular tenant
  • use rules/privileges
  • use row-level security

For rules/privileges:

  1. create a role per tenant
  2. create a datasource per role
  3. use this datasource in redash queries
  4. create (tenant-wise independent, reusable) views
  5. define rules that restrict row access to that role

See remarks below for row-level security, can be applied here as well.

See https://www.postgresql.org/docs/10/static/rules-privileges.html

For row-level security:

  1. create a role per tenant
  2. create a datasource per role
  3. use this datasource in redash queries
  4. enable row level security
  5. create (tenant-wise independent, reusable) views
  6. define policies that restrict row access to that role

This requires either altering your table schemas (e.g. integrating a user column) or having multiple policies with CHECK clauses for all tenant ids. This is somewhat tedious and error-prone if you add tenants but forget to update all policy sets.

See https://www.postgresql.org/docs/9.6/static/ddl-rowsecurity.html

Some online research came up with efforts on database-level tenant separation and schema-level tenant separation as well. As I have a multi-tenant database schema as well I’d be happy to hear of any further solutions and suggestions.

Regards,
Frank