Re: [JDBC] [SQL] Thoughts on a Isolation/Security problem. - Mailing list pgsql-admin

On Tue, 2006-04-18 at 14:32 +0300, Achilleus Mantzios wrote:
> Thanx for your thoughts, but this would require touching
> 173 tables +
> 2,594 SQL statements in a sum of 324 programs (which sum into 125,085
> lines of code)


We did a very similar conversion to the one proposed here a couple of
years back, and switched an old Java app from a dedicated schema-per-
client model to a shared schema with company ID model.  I figured I'd
share our results, to at least give you a data point on what you could
expect if you went that route.

At the time, the application consisted of roughly 90 tables and 90,000
lines of code.  Never counted the SQL statements there were probably
very roughly 1000.

We had the advantage of having the 90,000 LOC spread across only two
larger applications instead of a bunch of small programs.

It actually ended up being a much easier process than we had expected,
because the application logic didn't really need change all that much.
The major steps were:

1. Add an extra company FK to the top-level objects in our data model.
For example, we added companyId to the userdetail table, but not to the
address table which depends on the userdetail table.  In our case, this
meant that we needed to add a column to about 30 (one third) of our
tables.  Not very difficult.

2. Inserts into those 30 tables were modified to include an extra
parameter.  The actual insert change is trivial, but depending on your
plumbing it might take some refactoring to get the companyId to the code
that does the inserts.  This involved a moderate code refactoring, but
the changes were very straightforward and not too prone to errors.

3. Because we didn't support moving from one company to another, the
update and most of the select logic didn't change at all.

4. Needed to update all of the search queries to only search within the
current company.  Fortunately, this type of query is relatively rare.
In our case, there were only about 20 that needed updates out of our
1000 queries.  The change is also simple, just add one AND to the WHERE
clause.

5. Security checks.  This is the only step that took a good chunk of
time, because our application is web-based and so a mischievous user
could muck with id's in the HTTP POSTS on pretty much any page in our
application.  This meant that we needed to be careful to always do
company checks before processing any user input.  Fortunately for us, at
the same time we were enhancing our security model anyway, so doing the
extra company check really added no extra time on top of the work that
needed to be done anyway.

Steps 1-4 took a single developer 3 days to implement.  Step 5 was
harder to measure because as I mentioned we bundled it together with a
larger security overhaul, but to perform it separately (and do an audit
to make sure it was correct) would have taken perhaps a week or two.

Anyway, overall in our case we had a pretty successful conversion.  It
ended taking significantly less time than anticipated, and the decreased
maintenance of only managing one database and the ease of building new
global administrative and reporting tools definitely made the project
worth while.

-- Mark Lewis