#389 Multitenancy with PostgreSQL pro

Thanks Ryan, great as usual! I wonder if Heroku or Appfog have any issues around using Postgres Schemas e.g. limitations on the dev databases therein? I used scopes 2 years ago when I last did a multi-tenant app but at the time I wanted to make use of Schemas I have to say - it seems less fussy and more secure plus less likely to forget to scope data etc. Maybe I'll migrate the old site to this method (only 3 subdomains including www) if Heroku supports it...

One last possible thing might be a way to restrict users to specific tenants. So that a use can only log into one tenant. Maybe with some kind of authorizaton system like cancan??

@4awre
there are several ways, look at this:

for Devise:
https://github.com/plataformatec/devise/wiki/How-To:--Isolate-users-to-log-into-a-single-subdomain

http://blog.plataformatec.com.br/tag/subdomain/

http://blog.plataformatec.com.br/tag/subdomain/

Authorisation:
and you should take a look to authorisation from scratch. it works perfectly for me,
but i'm still very interested to see what cancan2 will bring :)

How to give size storage of tenant? Is it possible to check the actual size of the tenant through the Rails application? If so, can i get a detailed view such as how many size of tenant?

Any thoughts?

Postgres isn't the only database that provides Schemas. You can achieve the same thing in Oracle with Schemas and Public Synonyms.

I'm having a bit of an issue with this. I am using 'Account' rather than 'Tenant' but otherwise everything is as the example.
When I do 'db:migrate --trace' the first time I get -
'
** Invoke db:migrate (first_time)
** Invoke environment (first_time)
** Execute environment
** Invoke db:load_config (first_time)
** Execute db:load_config
** Invoke multiaccount:db:migrate (first_time)
** Invoke environment
** Invoke db:load_config
** Execute multiaccount:db:migrate
rake aborted!
PG::Error: ERROR: relation "accounts" does not exist
LINE 4: WHERE a.attrelid = '"accounts"'::regclass
^
: SELECT a.attname, format_type(a.atttypid, a.atttypmod), d.adsrc, a.attnotnull
FROM pg_attribute a LEFT JOIN pg_attrdef d
ON a.attrelid = d.adrelid AND a.attnum = d.adnum
WHERE a.attrelid = '"accounts"'::regclass
AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum
...
'

Has anyone else encountered this issue ?

With this set up, how can I determine the max number of tenants that can be supported on a single server? How to test this out?

Is it possible to have models with relationships spanning schemas? Like if a you had a user in a tenant schema and wanted to associate them with the tenant model in the public schema.

Has anyone figured out how to do this if you're also using hstore?
I can't work out how to replace:

load Rails.root.join("db/schema.rb")

with a similar line that will load and execute the file db/structure.sql instead.

IMHO, we should really careful when deciding to use schemas fo multi-tenants. It is a one-trip ticket, you need to pay so much if you regrets.

I'm working on a app that uses postgres schemas for multi-tenants. And one of the most important requirement in this new release, is to share data between schema.
And for some reason, we cannot get rid of the schemas.

As a result, we need to switch between schemas dynamically according to the context. In some special cases, we need switch between schemas for several times in one request.
This really causes a lot of pain for us.

We try our best to move the common data into public schema, and leave the rest in schemas. Then we can use the search_path to enable us make query across schemas.

But the really pain is that, ActiveRecord caches the query result in its DbAdapter(aka ActiveRecord::Base.connection).
When schema switched, the cached is not cleared. Then you will get wrong data.
If you manually clear the cached, it hurts performance, and you might the association of your model works improperly, which strongly depends on the caches.

And another issue is that the schemas.rb, totally not work, since it cannot distinguish the schemas. So the gems depends on it, won't work properly, e.g. annotate gem.

At last, I wrote an utility called MultiSchema to help us to switch between schemas, hope it helps. You can find it in this post

Any advice for converting an existing app. Should the database be destroyed and recreated? Does the tenant model and an instance need to be created first? Any tutorials or examples online? Thank you.

Does anyone have any tips for making this work with domains instead of subdomains? I'd like to have the tenants set up so that it's grabbing the tenant from the actual domain name. Then I can mask the domain, so that multiple domains are using the application, and depending on which domain they are using, they will have a different schema.
Thanks,
--Mark

This is great, however how can I maintain the non subdomain portion?

I'd like that to exist still as a master backend?

How would Postgres table inheritance play into this? Couldn't it simplify the database migrations?

http://www.postgresql.org/docs/9.2/static/ddl-inherit.html

In particular:

"ALTER TABLE will propagate any changes in column data definitions and check constraints down the inheritance hierarchy. Again, dropping columns that are depended on by other tables is only possible when using the CASCADE option. ALTER TABLE follows the same rules for duplicate column merging and rejection that apply during CREATE TABLE."

I quickly run in to some kind of brick wall when trying this out, TDD style ofc ;) This blog post, by someone running faster than me, cleared most of it up: http://www.superpumpup.com/blog/1079982-postgresql-schema-multi-tenancy-and-rspec-testing/

So if you are having troubles with Database Cleaner not being cleanly enough with your schemas, give it a read.

I'm getting the following error when I load any page of my app:

undefined method `scope_schema' for nil:NilClass

Any rough idea as to why this is?

I am working on an app that has all the tenants data in one table. I am looking at segregating that for security reasons. My target market will have a range of security requiremnts - some won't care and will be fine with the shared set-up some would want something like Apartment and some perhaps extreme like a completly separate instance of the app on a subdomain or internally hosted.

Any suggestions on such a set-up? I am thinking I could have a 'basic' plan (shared db), a 'plus' plan (Apartment etc.) and a 'pro' plan (private server type setup). I have no real data but I would say the split would be 75%/20%/5%.

Is it worth the effort to maintain 3 versions of the same code base?

what is the best way to load seed data for a new tenant?

how about adding load Rails.root.join("db/seed_tenant#{id}.rb") to create_schema:


def create_schema
connection.execute("create schema tenant#{id}")
scope_schema do
load Rails.root.join("db/schema.rb")
load Rails.root.join("db/seed_tenant#{id}.rb")
connection.execute("drop table #{self.class.table_name}")
end
end

Hi Ryan,
Thanks for this. I was trying to write a custom rake task , say sending email And this has to be run against each tenant schema. I wrote lib/tasks/mailer.rake

desc "send mail for conditions"
task send_email: :environment do
#conditions
puts 'HHHHHHHHHHHHHHHHhh'
end

But this gives me error: undefined method `strip' for :"Run db:migrate for each tenant":Symbol

I have the lib/tasks/multitenant.rake like below

`db_tasks = %w[db:migrate db:migrate:up db:migrate:down db:rollback db:forward]
namespace :multitenant do
db_tasks.each do |task_name|
desc :"Run #{task_name} for each tenant"
task task_name => %w[environment db:load_config] do
Tenant.find_each do |tenant|
puts "Running #{task_name} for tenant#{tenant.id} (#{tenant.subdomain})"
tenant.scope_schema {Rake::Task[task_name].execute}
end
end
end
end

db_tasks.each do |task_name|
Rake::Task[task_name].enhance(["multitenant:#{task_name}"])`

Please help

If anyone are looking for a way to exclude new tables from the multitenant migration (i.e. Only create the new table in the public schema). This solved the problem for me:

if ActiveRecord::Base.connection.current_schema == 'public'
Your migration 
end

Complete example

class UsersHasAndBelongsToManyRoles < ActiveRecord::Migration
        def change
                if ActiveRecord::Base.connection.current_schema == 'public'
                        create_table :roles do |t|
                                t.string :role
                                t.timestamps
                        end

                        create_table :roles_users do |t|
                                t.belongs_to :user, :null => false
                                t.belongs_to :role, :null => false
                        end

                end
        end
end

Hi, I am using carrier wave for file upload

class ImageUploader < CarrierWave::Uploader::Base
  include CarrierWave::RMagick
  storage :file
  # This is a sensible default for uploaders that are meant to be mounted:
  def store_dir
    "uploads/#{model.class.to_s.underscore}/#{mounted_as}/#{model.id}"
  end
end

Here my question is how to specify the tenant.id like

"uploads/#{current_tenant.id}/#{model.class.to_s.underscore}
/#{mounted_as}/#{model.id}"

where current_tenant is a application controller helper method

Thanks

Please any idea of use delayed_jobs for multitenant app with Postgresql schemas.
Each task puts in current schema to the moment. But DJ getting tasks from public schema only.

I'm getting the following error when I load any page of my app:

undefined method `scope_schema' for nil:NilClass when users session creation
any body have idea about this