Multiple PostgreSQL schemas and users on Django with subdomains
I have seen various questions on multi-site and multi-host Django, including subdomains and specific schemas per subdomain. What I have not seen is a solution (or tips so I can code one) to this problem.
- I am using Django + PostgreSQL on a site, let's say myapp.com
- The main site myapp.com is used for registration of companies
- A registered company gets its own subdomain, company.myapp.com, and logs in and works from there.
My idea of doing this is by making 2 initial schemas in PostgreSQL.
- Schema "auth" for companies and users
- Schema "empty_company_template" with the basic tables for a company, all empty but hooked up to the right sequences etc.
When a new company registers, I want this to happen:
- Create a new schema for the company, derives from empty_company_template
- Create a new DB user for the company, named company (the company name)
- Set the search path for this new user to company, auth (no access to empty_company_template, no access to other users schema's)
To me this seems better than the existing solutions that all seem to depend on one single database user for the entire application (with access to all schemes). However, I struggle to get this to work. Is this indeed a viable approach? Can anyone point me in the right direction? It's Django, so perhaps it's been done and I just haven't found it?
I have a working solution that does everything except separate users.
It's a small piece of middleware (just process_request) that determines the subdomain, and executes a SET search_path query on the database. It's good enough for me for now.
Anyone interested in the code, just contact me. I'll publish it somewhere when it's final.
EDIT Dec 22, 2010:
I published the code on my blog at http://blog.dyve.net/django-subdomains-and-postgresql-schemas