Multi-tenant web app with Rocket and Diesel

Question

I have a multi-tenant web app that may need to support dozens of tenants (companies). I've been looking for a way to ensure that a tenant only has access to their own data (its important that there's no leaks) without having to pass around the tenant_id to every form and SQL query. My idea was to create an updatable view so that a user's query can only operate within the bounds of their company's data.

I'm doing this by creating a view (postgres):

CREATE VIEW products_tenant AS
SELECT *
FROM products
WHERE company_id = cast(current_setting('my.tenant_id') as int)
with local check option;
ALTER VIEW products_tenant ALTER COLUMN company_id SET DEFAULT cast(current_setting('my.tenant_id') as int);

This creates an updatable view that only allows queries on that company's data, without having to specify their tenant_id.

In Diesel, I've written the table! macros for the views so Diesel sees them as tables. In Rocket, I've wrapped my database connection Request Guard in another Request Guard that first sends a SQL query to set my.tenant_id to the user's tenant_id:

pub struct TenantView(DbConn);
...

impl<'a, 'r> FromRequest<'a, 'r> for TenantView {
    type Error = ();

    fn from_request(request: &'a Request<'r>) -> Outcome<Self, ()> {
        let conn = request.guard::<DbConn>().unwrap();
        let company_id = request.guard::<User>().unwrap().get_company_id();

        let query = sql_query(format!("SET session my.tenant_id = {}", company_id));
        query.execute(&*conn).expect("Failed to set session variable");
        Outcome::Success(Self(conn))
    }
}

The user can then use the request guard to make database queries and will only have access to their company's data. A tenant specific updatable view.

BUT my concern is this could result in a race condition. I'm not clear on how postgres session variables work with Diesel and Rocket. Suppose users from two different companies simultaneously submit a request to Rocket, and the session variable for User A is set to their tenant id, but before their transaction, User B sets the session variable to THEIR tenant id, causing both database requests to write to User B's tenant id. Can anyone shed some light on if this would be a problem or not? Or if there's a more straight forward way to handle multi tenant apps?

score 1 · Answer 1 · answered Aug 26 '20 at 00:01

I would just filter for the company_id within the query functions. I know it's exactly what you don't want but I'd argue that one argument more doesn't clutter your code that mutch.

use crate::schema::{
    products::dsl::{products as all_products},
    products,
};
...

#[derive(Queryable)]
#[table_name="products"]
pub struct Product {
    company_id: i32,
    ...
}

impl Product {
    pub fn all(user: &User, conn: &PgConnection) -> Option<Vec<Product>> {
        all_products.filter(products::company_id.eq(user.get_company_id())).load(conn).ok()
    }

    ...
}

I guess that you restrict access to a products page (or similar) by using a User request guard. Then you can just pass the user you already have to the function.

#[derive(serde::Serialize)]
pub struct AppContext<'a, T> 
where T: 'a {
    body: &'a T,
}

#[get("/product/all", rank = 1)]
pub fn products(conn: DbConn, user: User) -> Template {
    let context: AppContext<'_, Option<Vec<Product>>> = AppContext { 
        body: &Product::all(&user, &conn)  // <= get products
    };
    
    Template::render("products", &context)
}

#[get("/product/all", rank = 2)]
pub fn products_redirect() -> Redirect {
    Redirect::to("/login")
}

Furthermore, you should consider using request.local_cache if your FromRequest implementation retrieves the user from the database. Otherwise, you would query a given user at least two times per request, once for the User request guard within the view function and another time within the TennantView request guard.

Multi-tenant web app with Rocket and Diesel

1 Answers1