When developing multi-organization web applications it is of utmost importance that data from one organization is not visible to the other. There are several software aproaches for this. Usually this can be solved by having an organization ID and whenever a query is made a ‘where’ clause is added to filter by organization. Although this approach works fine it is up to developers to always filter by org_id but its certainly error prone.
To solve this problem there are other approaches. One is that each organization gets a copy of the database and a single database user to log to this database. When the user logs in the application uses the URL to determine the organization (say: http://organization.domain.com/login). With the organization name extracted from the URL, the aplication determines which database instance, database user and password to use for the application to use. This obviously solves the problem since, by definition, there is no way data from the two organizations can mix: they live in different databases alltogether.
The problem with the above approach is that, if you have a large number of organizations that means you have a large number of databases. In the case of thousands of organizations (don’t think corporations, think small business owners which use your webapp daily) then there might be performance issues in the database since regular databases are not designed to have thousands of small databases. At least the ones I know of.
Chatting about this problem with the nice folks over at the #postgres IRC channel on the FreeNode servers they pointed me to two different approaches. One was to use PL/pgSQL code to isolate all access to the data. You have PL/pgSQL code which takes a user as a parameter and uses it to filter or insert the data into tables which have an org_id column.
A second approach which seemed more interesting to me was to create a schema for each organization and encode the organization_id in the schema name. Then you can create an updatable view which uses the schema name to filter the data by organiation_id.
The last approach appealed to me more because I wanted the database to take care of the filtering. The problem with it is that you have to create a schema for each new user. Another setback the guys at #postgres pointed out was that connection spooling is not possible (I don’t know enough postgres to understand why).
In this article I will point out a slighly different approach our team at Easytech came up with inspired by this last method. The idea is that when the applications logs into the database it sets the organization ID as a kind of ’session parameter’ . All data is accesible through updatable views and this views filter the data using de org_id. When data is updated in the table the org_id is pulled from the ’session’ and inserted in the original table so that it can be marked as beloging to that organization.
The trick here is to create a run-time paramater. To do this you need to modify your postgres.conf file and add a parameter. For example:
# Organization ID
# ——————————————————————————
custom_variable_classes = ‘org’ # list of custom variable class names
org.id = ‘private’ # set initial value
After re-starting the server you can now connect to the server with psql and set the value of org.id with:
Lets see how we can use this to create views which display different data to different application users:
$ psql
Welcome TO psql 7.3.4, the PostgreSQL interactive terminal.
Type: \copyright FOR distribution terms
\h FOR help WITH SQL commands
\? FOR help ON internal slash commands
\g OR terminate WITH semicolon TO execute query
\q TO quit
postgres=# select version();
version
———————————————————————————————————
PostgreSQL 8.2.5 ON i686-pc-linux-gnu, compiled BY GCC gcc (GCC) 3.3.2 20031022 (Red Hat Linux 3.3.2-1)
postgres=# create database corp;
CREATE DATABASE
postgres=# \connect corp
You are now connected TO DATABASE corp.
corp=# create table secret_projects ( org_id varchar, project_name varchar);
CREATE TABLE
corp=# insert into secret_projects values (’apple’, ‘iPhone’);
corp=# insert into secret_projects values (’apple’, ‘iMac’);
corp=# insert into secret_projects values (’microsoft’, ‘Zune’);
…
corp=# create view secret_projects_v as
SELECT *
FROM secret_projects
WHERE org_id = ( SELECT current_setting(‘org.id’) );
CREATE VIEW
corp=# set session org.id to ‘apple’;
SET
corp=# select * from secret_projects_v;
org_id | project_name
——–+————–
apple | iPhone
apple | iMac
(2 rows)
corp=# set session org.id to ‘microsoft’;
SET
corp=# select * from secret_projects_v;
org_id | project_name
———–+————–
microsoft | Zune
(1 row)
As you can see with this approach each application user can see different data from the same table. When a user logs into the application we pull the organization from the URL and use it to setup the session parameter. Once the session paramater is set, all views of sensitive data filter by org_id. Of course if you select from the original tables then you will see all data. It is important that the development team only use this filtered views to access the data.
We still have to investigate how this technique might work in a real application. Another issue to study is how to perform joins with several views and how it performs. We will report how this technique turns out in another posting.
Entries (RSS)
July 13th, 2009 at 5:41 am
this approach you’ve described is good when you have completely separate organizations but in many cases you need to manage access rights for groups and people inside the same organizations.
In those cases you need a more dynamic approach. One could be to mark the ‘owner’ of the record, solving dynamically the access right to that record when someone else wants access. This approach could involve dynamic sql generation and business rules declared to manage access provileges.