Rodauth for multi tenancy

[Bert Goethals]

Hi all,

So I'm eager to try out Rodauth after working with Devise for several years.

In a new project we are starting, we will be using multi tenancy. This is (in part) enforced via the domain name. So it may be that a same email address / login would be in the database.

I know there is no direct multi tenancy options built in, nor have a I found any documentation, gem or blog post about that. However, i think I have a solution. However it seems "to easy" and I wanted to run it by the community.

I was thinking to base the solution on this documentation: http://rodauth.jeremyevans.net/rdoc/files/doc/guides/alternative_login_rdoc.html and have the `username` be the combination of a tenant ID and the user email address. Something like `"#{account_id}_#{email}".

What do you think? Is there a more idiomatic or conventional way of doing this?

Thanks for your time!

_ bert

[Jeremy Evans]

That may work.  You could also store the email/username column normally, but do not make it unique.  Instead, have a composite unique constraint on (tenant_id, email).  Then override account_from_login to something like:

account_from_login do |login|

    ds = db[accounts_table].where(login_column=>login, :tenant_id=>request.env[:tenant_id])

   ds = ds.select(*account_select) if account_select
   ds = ds.where(account_status_column=>[account_unverified_status_value, account_open_status_value]) unless skip_status_checks?
   ds.first

end

Here you would store the tenant_id in the rack environment, which you could do in a rack middleware, or anywhere in your Roda routing tree before dispatching to Rodauth.

Potentially we could make this easier, by adding support for something like:

account_login_filter do |login|

  {login_column=>login, :tenant_id=>request.env[:tenant_id]}

end

However, I've been hesitant to add methods to Rodauth that expose too much of it's internal usage of Sequel.  The current idea with Rodauth is using the existing configuration methods, you could avoid using Sequel completely.  However, that approach requires you reimplement all of the low level database-type code for doing things like determining the account to use for a login by yourself (as evidenced by the account_from_login example above), so it definitely has its own issues.

Thanks,

Jeremy

[Bert Goethals]

META: It seems I have accidentally sent my reply only to Jeremy. I'll try my best to reconstruct it here.

Hey Jeremy,

Thanks for the quick reply. Your solution does seem a lot less "hacky" than what I had in mind. 

I'll give it a shot.

I don't mind writing the SQL code, especially if I'm deviating from the framework defaults.

I was thinking that there might be a cleaner solution by introducing something like `accounts_scope`. This would cover the tenants use case, and possibly more.

It could be as simple as the below code, where the default implementation returns `db[accounts_table]`. 

```

accounts_scope do

  db[accounts_table].where(tenant_id: request.env[:tenant_id])

end

```

We can also hide Sequel entirely, with a column setting, and a proc.

```

accounts_scope_column :tenant_id

accounts_scope do

  request.env[:tenant_id]

end

```

The above code could even support arrays for very complex scoping requirements.

Just my 2 cents,

- bert

[Jeremy Evans]

On Tue, Sep 28, 2021 at 3:03 AM Bert Goethals <bert.g...@trustedfamily.net> wrote:

META: It seems I have accidentally sent my reply only to Jeremy. I'll try my best to reconstruct it here.

Hey Jeremy,

Thanks for the quick reply. Your solution does seem a lot less "hacky" than what I had in mind. 

I'll give it a shot.

I don't mind writing the SQL code, especially if I'm deviating from the framework defaults.

I was thinking that there might be a cleaner solution by introducing something like `accounts_scope`. This would cover the tenants use case, and possibly more.

It could be as simple as the below code, where the default implementation returns `db[accounts_table]`. 

```

accounts_scope do

  db[accounts_table].where(tenant_id: request.env[:tenant_id])

end

```

This directly exposes a Sequel dataset, which is something Rodauth tries to avoid.  That's one reason I prefer account_login_filter:

account_login_filter do |login|

  super.merge(:tenant_id=>request.env[:tenant_id])

end

This doesn't expose any Sequel internals, though it does require an object be returned that can operate as a filter expression in Sequel.  Rodauth tries to avoid that as well, but it's certainly not as big of an issue as returning a Sequel-specific object.

 

We can also hide Sequel entirely, with a column setting, and a proc.

```

accounts_scope_column :tenant_id

accounts_scope do

  request.env[:tenant_id]

end

```

The above code could even support arrays for very complex scoping requirements.

This approach is less flexible and requires multiple configuration methods instead of a single configuration method.  So if we do want to support this, I think something like account_login_filter would work best.

Thanks,

Jeremy

[Bert Goethals]

Hey,

I got this working well, and it works great.

On the accounts filter, I gave it some thought and I think I like the original suggestion best now, but i would tweak it in one important way

```

# Original suggestion

account_login_filter do |login|

  {login_column=>login, :tenant_id=>request.env[:tenant_id]}

end

# Tweaked suggestion

account_login_filter do |login|

  {:tenant_id=>request.env[:tenant_id]}

end

```

In the tweaked suggestion we only add the scope, so that the existing systems that build the account query keep working without surprises.

Possibly this could also be used s information that goes into the `before_create_account` block, so that new account are built in a account_login_filter compatible way.

Kind regards,

- bert

[Jeremy Evans]

On Fri, Oct 1, 2021 at 3:15 AM Bert Goethals <bert.g...@trustedfamily.net> wrote:

Hey,

I got this working well, and it works great.

That's good to hear!

 

On the accounts filter, I gave it some thought and I think I like the original suggestion best now, but i would tweak it in one important way

```

# Original suggestion

account_login_filter do |login|

  {login_column=>login, :tenant_id=>request.env[:tenant_id]}

end

# Tweaked suggestion

account_login_filter do |login|

  {:tenant_id=>request.env[:tenant_id]}

end

```

In the tweaked suggestion we only add the scope, so that the existing systems that build the account query keep working without surprises.

That is less flexible.  What if you want to have a filter that does not include a matching login column? 

Either way shouldn't affect existing systems, since the default behavior without specifying account_login_filter would remain the same.

Like most things in Rodauth, you can use super to avoid redundancy:

account_login_filter do |login|

  super.merge(:tenant_id=>request.env[:tenant_id])

end

Possibly this could also be used s information that goes into the `before_create_account` block, so that new account are built in a account_login_filter compatible way.

Unfortunately, I don't think that is workable.  Consider a login filter that checks that the login is in another table via a subquery, or uses a LIKE expression.  While there is some overlap between what you can use in a SELECT WHERE clause and what you can use in an INSERT VALUES clause, it's definitely not something that could be done by default.

If you wanted to create accounts supporting multitenancy, you would want to use new_account:

new_account do |login|

  super.merge(:tenant_id=>request.env[:tenant_id])

end

 

Thanks,

Jeremy