Custom driver to enforce custom permission model

Hey!

I’m planning to connect Metabase to a Microsoft SQL Server database but I would like to enforce my own permission model. Therefore I’m considering to create my own custom driver for this, but would like to ask if you think that is feasible or not?

I have looked at the “Data Sandbox” feature, but that doesn’t solve all my needs since I need to dynamically calculate the permission before each query is executed.

This is how I’m planning to build the driver:

  1. Create a custom driver that wraps the sqlserver driver.
  2. Override the actual “execute query”.
  3. Run custom sql queries to build the permission model.
  4. Append an extra where statement to the MBQL query sent from Metabase.
  5. Execute the query

Do you think this would work? And how much work do you think it would be? When I read the docs I get a feeling that it should be pretty straight forward to do.

I also wonder if I can get hold of the UserID in my custom driver? Could I perhaps extract it from the MBQL query sent from Metabase?

Thanks in advance, Jens

1 Like

I’d be really keen to be involved in this. Do you need anyone to test or try with different databases? I do a lot of work with SQL, sadly not with Clojure.

Thanks a lot!

I haven’t written to much clojure my self so that’s why I’m asking before I’m starting to implement my idea. Would be awesome to hear from somebody who has written a driver him/her self.

I’ve done / am doing a similar thing but based on the Postgres driver and using Postgres’ row level security policies feature (instead of appending “where”). It looks like MSSQL has a similar RLS feature so probably best use that.

Main thing is to write a driver that implements the method “metabase.driver/execute-query” (v0.34.2) or “metabase.driver/execute-reducible-query” (v0.35). This is the point where the metabase query processor hands off to the driver, and so you can set the backend context before / after running the user’s query. Look at the sql_jdbc implementation for a guide as to what the in/out interface etc is. It’s possible to get the user-id of the user for whom the current query is being run either from the API context, or from query metadata passed to the driver (and maybe the query processor directly) - the latter being important when there is no ‘current-user’ e.g. for scheduled pulses / alerts. From that you can also use or look up other details for the user if needed e.g. email, permission groups, etc.

Misc. advice would be to

  • read the (kind of incomplete) wiki pages on writing a driver,
  • review the dozen or so drivers for other databases on GitHub to see the basic required structure for code and tests,
  • avoid using clojure.java.jdbc library methods because it has strange transaction handling (instead use the underlying JDBC objects, as is done in v0.35).

If you have never written Clojure (or any Lisp) before then give yourself a decent amount of time to read through Clojure for the Brave and True and get used to how it all works. If you’re not keen on emacs either then the Intellij has at least syntax highlighting or you may find the Cursive plugin useful (it doesn’t always figure out dynamic stuff like macros etc). How long it takes to implement is a piece-of-string question but I found the majority of the work is at the backend setting up RLS policies that are correct and secure and that don’t tank performance.

3 Likes

Thank you so much for your reply!