How to approach multi-tenancy with FOSUserBundle #413

Closed
breerly opened this Issue Nov 21, 2011 · 23 comments

Comments

Projects
None yet
10 participants
@breerly

breerly commented Nov 21, 2011

I'm looking to create a site that has many different accounts, who each have their own user. The end site will have a url structure like so :account.site.com/login which would allow that user to login against that account. Much like any SaaS site, like Basecamp for example.

I'm curious if FOSUserBundle could be used/bent in this direction and if so what is the best way to go about it?

Thanks,
(merrix)

@stof

This comment has been minimized.

Show comment Hide comment
@stof

stof Nov 21, 2011

Owner

FOSUserBundle is not about providing way to login (this is handled by the SecurityBundle) but about providing management of users in a storage.

Owner

stof commented Nov 21, 2011

FOSUserBundle is not about providing way to login (this is handled by the SecurityBundle) but about providing management of users in a storage.

@breerly

This comment has been minimized.

Show comment Hide comment
@breerly

breerly Nov 21, 2011

Sure.

My point was more - given FOSUserBundle's implementation, what is the best way to add multi-tenant support with, say, ORM?

breerly commented Nov 21, 2011

Sure.

My point was more - given FOSUserBundle's implementation, what is the best way to add multi-tenant support with, say, ORM?

@breerly

This comment has been minimized.

Show comment Hide comment
@breerly

breerly Nov 21, 2011

I suppose maybe make my Entity\User have one Entity\Account which all other entities would tie back to. Assuming this approach isn't horrible - I have two questions.

  • What's the best way to add an account field to login and make sure that it matches?
  • How could I add a validation to the registration process to verify uniqueness of a user within an account?

breerly commented Nov 21, 2011

I suppose maybe make my Entity\User have one Entity\Account which all other entities would tie back to. Assuming this approach isn't horrible - I have two questions.

  • What's the best way to add an account field to login and make sure that it matches?
  • How could I add a validation to the registration process to verify uniqueness of a user within an account?
@breerly

This comment has been minimized.

Show comment Hide comment
@breerly

breerly Nov 21, 2011

I guess another approach would be to have separate databases for each account. I could create a doctrine listener that changes all queries to the correct database according to the current account. The challenge here is getting Doctrine Migrations to rotate through all accounts. I'll write an update here when I get into it.

breerly commented Nov 21, 2011

I guess another approach would be to have separate databases for each account. I could create a doctrine listener that changes all queries to the correct database according to the current account. The challenge here is getting Doctrine Migrations to rotate through all accounts. I'll write an update here when I get into it.

@nurikabe

This comment has been minimized.

Show comment Hide comment
@nurikabe

nurikabe Nov 22, 2011

I'm using the second approach you mention.

I'm using the second approach you mention.

@bmeynell

This comment has been minimized.

Show comment Hide comment
@bmeynell

bmeynell Feb 19, 2012

You could also approach this by adding an account_id to the user table and creating an unique constraint between username_canonical and account_id.

However, to do this, the existing unique contraints on usernameCanonical and emailCanonical would have to be removed. Is this possible with FOSUserBundle? If not, what's the work around other than forking the project?

You could also approach this by adding an account_id to the user table and creating an unique constraint between username_canonical and account_id.

However, to do this, the existing unique contraints on usernameCanonical and emailCanonical would have to be removed. Is this possible with FOSUserBundle? If not, what's the work around other than forking the project?

@stof

This comment has been minimized.

Show comment Hide comment
@stof

stof Feb 19, 2012

Owner

it is possible if you redo the mapping yourself instead of extending from the mapped superclass (there is an entry in the doc about it). But you will still have an issue: the security system loads the user by username, and you would need both the username and the account id in your case to find it.

Owner

stof commented Feb 19, 2012

it is possible if you redo the mapping yourself instead of extending from the mapped superclass (there is an entry in the doc about it). But you will still have an issue: the security system loads the user by username, and you would need both the username and the account id in your case to find it.

@breerly

This comment has been minimized.

Show comment Hide comment
@breerly

breerly Feb 20, 2012

oh dear magical @stof - please enlighten us as to the "right" way.

breerly commented Feb 20, 2012

oh dear magical @stof - please enlighten us as to the "right" way.

@bmeynell

This comment has been minimized.

Show comment Hide comment
@bmeynell

bmeynell Feb 20, 2012

@nurikabe: Regarding the approach you chose, wouldn't having that many databases be a huge performance/resource hog? Especially databases with hundreds of tables...

@nurikabe: Regarding the approach you chose, wouldn't having that many databases be a huge performance/resource hog? Especially databases with hundreds of tables...

@breerly

This comment has been minimized.

Show comment Hide comment
@breerly

breerly Feb 21, 2012

Of course. I think it depends on the data isolation requirements. I believe this is fairly common with enterprise SaaS, to have different organization accounts separated at the data level. @nurikabe do you think you could share how you did this?

breerly commented Feb 21, 2012

Of course. I think it depends on the data isolation requirements. I believe this is fairly common with enterprise SaaS, to have different organization accounts separated at the data level. @nurikabe do you think you could share how you did this?

@nurikabe

This comment has been minimized.

Show comment Hide comment
@nurikabe

nurikabe Feb 22, 2012

@bmeynell Not really. Sure we create a lot of tables for new users, but these tables are mostly empty. The ability to slice off heavy load users by simply moving their database outweighs the negligible space hit of have a lot of empty tables.

Note that we're using MySQL; databases in MySQL are more like schemas in PostgreSQL-land, and elsewhere.

@breerly I've got a custom Request component that determines which database to use per subdomain. It's starting to get i the way of our automated tests, however, so I'm looking to move this into a listener of sorts.

@bmeynell Not really. Sure we create a lot of tables for new users, but these tables are mostly empty. The ability to slice off heavy load users by simply moving their database outweighs the negligible space hit of have a lot of empty tables.

Note that we're using MySQL; databases in MySQL are more like schemas in PostgreSQL-land, and elsewhere.

@breerly I've got a custom Request component that determines which database to use per subdomain. It's starting to get i the way of our automated tests, however, so I'm looking to move this into a listener of sorts.

@breerly

This comment has been minimized.

Show comment Hide comment
@breerly

breerly May 22, 2012

@nurikabe - have you made any progress sorting your solution into something more reusable? I'd love to collaborate on some code or help test the viability of your approach. Mayhaps we can get this in a PR for this bundle :)

breerly commented May 22, 2012

@nurikabe - have you made any progress sorting your solution into something more reusable? I'd love to collaborate on some code or help test the viability of your approach. Mayhaps we can get this in a PR for this bundle :)

@nurikabe

This comment has been minimized.

Show comment Hide comment
@nurikabe

nurikabe May 22, 2012

@breerly Part of the ugly hack in Request moved into a less unsightly hack in an overloaded ConnectionFactory. I'm waiting on 2.1 to come out (in a couple of weeks?) before really trying clean this up this though. At that point I would be keen to work on a PR or a "MultitenantConnectionBundle" or something, though I'm kind of hoping that 2.1 makes it a moot point.. :-p

@breerly Part of the ugly hack in Request moved into a less unsightly hack in an overloaded ConnectionFactory. I'm waiting on 2.1 to come out (in a couple of weeks?) before really trying clean this up this though. At that point I would be keen to work on a PR or a "MultitenantConnectionBundle" or something, though I'm kind of hoping that 2.1 makes it a moot point.. :-p

@breerly

This comment has been minimized.

Show comment Hide comment
@breerly

breerly May 22, 2012

@nurikabe gotcha, let's synch up when 2.1 comes out.

breerly commented May 22, 2012

@nurikabe gotcha, let's synch up when 2.1 comes out.

@chrisben

This comment has been minimized.

Show comment Hide comment
@chrisben

chrisben Oct 10, 2012

@nurikabe 2.1 is out now. Do you have a better idea on when/how you'll be able to share your work on that multitenancy bundle? Much appreciated :)

@nurikabe 2.1 is out now. Do you have a better idea on when/how you'll be able to share your work on that multitenancy bundle? Much appreciated :)

@nurikabe

This comment has been minimized.

Show comment Hide comment
@nurikabe

nurikabe Oct 10, 2012

End of the month I think. We've just started migration now.

End of the month I think. We've just started migration now.

@ickmund

This comment has been minimized.

Show comment Hide comment
@ickmund

ickmund Nov 12, 2012

How did people finally end up doing this?

After fiddling around some today I finally decided to extend Doctrine\DBAL\Connection and set the dbname param in the construct as such:

<?php

namespace Acme\TestBundle\Doctrine\DBAL;
use Doctrine\DBAL\Connection;
use Doctrine\DBAL\Driver\PDOMySQL\Driver;
use Doctrine\DBAL\Configuration;
use Symfony\Bridge\Doctrine\ContainerAwareEventManager;

class TestConnection extends Connection
{
    public function __construct(array $params, Driver $driver, Configuration $config = null, ContainerAwareEventManager $eventManager = null)
    {
        $domain_array = explode(".", $_SERVER['HTTP_HOST']);
        $subdomain = $domain_array[0];
        $params['dbname'] = $subdomain;

        parent::__construct($params, $driver, $config, $eventManager);
    }
}

In apache I use wildcard subdomains and in config.yml I specifiy a wrapper_class together with the other dbal params:

doctrine:
    dbal:
        wrapper_class: "%database_wrapper_class%"

parameters.yml:

parameters:
    database_wrapper_class:     Acme\TestBundle\Doctrine\DBAL\TestConnection

Seems to work but haven't done much testing yet, simply tested a couple of subdomains and see what db it tries to connect to.

ickmund commented Nov 12, 2012

How did people finally end up doing this?

After fiddling around some today I finally decided to extend Doctrine\DBAL\Connection and set the dbname param in the construct as such:

<?php

namespace Acme\TestBundle\Doctrine\DBAL;
use Doctrine\DBAL\Connection;
use Doctrine\DBAL\Driver\PDOMySQL\Driver;
use Doctrine\DBAL\Configuration;
use Symfony\Bridge\Doctrine\ContainerAwareEventManager;

class TestConnection extends Connection
{
    public function __construct(array $params, Driver $driver, Configuration $config = null, ContainerAwareEventManager $eventManager = null)
    {
        $domain_array = explode(".", $_SERVER['HTTP_HOST']);
        $subdomain = $domain_array[0];
        $params['dbname'] = $subdomain;

        parent::__construct($params, $driver, $config, $eventManager);
    }
}

In apache I use wildcard subdomains and in config.yml I specifiy a wrapper_class together with the other dbal params:

doctrine:
    dbal:
        wrapper_class: "%database_wrapper_class%"

parameters.yml:

parameters:
    database_wrapper_class:     Acme\TestBundle\Doctrine\DBAL\TestConnection

Seems to work but haven't done much testing yet, simply tested a couple of subdomains and see what db it tries to connect to.

@chrisben

This comment has been minimized.

Show comment Hide comment
@chrisben

chrisben Nov 12, 2012

@ickmund I thought about doing something similar, just not using a different database name but a different database table prefix.
One question that pops in my mind with this solution is how you would deal with cached files: I suppose there should be separate caches for each domain. But perhaps sf2 caches only contain code and no data, so having different data for each domain doesn't matter much so we can keep sharing the same cache folder for all domains?

@ickmund I thought about doing something similar, just not using a different database name but a different database table prefix.
One question that pops in my mind with this solution is how you would deal with cached files: I suppose there should be separate caches for each domain. But perhaps sf2 caches only contain code and no data, so having different data for each domain doesn't matter much so we can keep sharing the same cache folder for all domains?

@nurikabe

This comment has been minimized.

Show comment Hide comment
@nurikabe

nurikabe Nov 12, 2012

@ickmund I'm using a similar approach to the one you describe above, except overriding createConnection() in Doctrine\Bundle\DoctrineBundle\ConnectionFactory as I mention above. It's been working well.

If I'm ever able to finish this freaking 2.1 upgrade (too.. many.. bc.. breaks..), I may get around to wrapping it up in a bundle, though will likely focus on sf-2.2 which finally (as of today) has good hostname support in the router: symfony/symfony#3378

I may also try to move logic into a Doctrine filter.

@ickmund I'm using a similar approach to the one you describe above, except overriding createConnection() in Doctrine\Bundle\DoctrineBundle\ConnectionFactory as I mention above. It's been working well.

If I'm ever able to finish this freaking 2.1 upgrade (too.. many.. bc.. breaks..), I may get around to wrapping it up in a bundle, though will likely focus on sf-2.2 which finally (as of today) has good hostname support in the router: symfony/symfony#3378

I may also try to move logic into a Doctrine filter.

@breerly

This comment has been minimized.

Show comment Hide comment
@breerly

breerly Jan 18, 2013

Did anyone ever have any luck with getting migrations to run via command-line with any of these strategies? Thanks.

breerly commented Jan 18, 2013

Did anyone ever have any luck with getting migrations to run via command-line with any of these strategies? Thanks.

@nmeirik

This comment has been minimized.

Show comment Hide comment
@nmeirik

nmeirik Apr 9, 2013

I've solved this by dynamically rewriting the config.yml with the db connections (and entity managers) once new tenants are added. Seems to work OK so far.

nmeirik commented Apr 9, 2013

I've solved this by dynamically rewriting the config.yml with the db connections (and entity managers) once new tenants are added. Seems to work OK so far.

@hguaymas

This comment has been minimized.

Show comment Hide comment
@hguaymas

hguaymas Jun 9, 2014

Hi @nmeirik, how did you solve? Thanks!

hguaymas commented Jun 9, 2014

Hi @nmeirik, how did you solve? Thanks!

@sstok

This comment has been minimized.

Show comment Hide comment

@merk merk closed this Jul 9, 2014

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment