Re: Installing LedgerSMB into a schema?

[Josh Berkus <..hidden..>]

Chris,

> Questions include:
> 1)  Should multiple companies be multiple schemas in one db or in
> multiple databases?

I think that in general that this would be unworkable because of difficulties 
with namespacing, backup, etc.

> 2)  Shoud aspects of the application be moved into separate schemas?

Yes.  Specifically, regardless of what we move to specific schema, I believe 
that all data tables should be moved out of the public schema.

The reason for this is simple: the public schema is just that, public.  By 
default, any user who can connect to the database has full rights on the 
tables in the public schema, as well as rights to execute any SPs etc.  These 
rights need to be restricted deliberately in order to secure objects.

If we have data tables in the public schema which are unsecured -- something 
which is easy to forget to do -- then pg_hba.conf becomes our *only* line of 
security against someone with a user account on the same server from 
connecting and messing with accounts.  Even more so if we conflate database 
users with LedgerSMB users as has been proposed.

On the other hand, if the lsmb tables are in a different schema, users must be 
granted rights on the schema before they can access anything.  That way if we 
forget the permissions on a specific table, it doesn't open us up.

Given divisions, I'd see a single-account ledgerSMB instance having 3 schema:

logins
main
reports

The reason for a "logins" schema has already been discussed.
"main" would contain the main data tables.
There's two reasons why I'm making "reports" separate:
1) because it's not uncommon for a lsmb admin to want to grant specific users 
access to some or all reports but none of the rest of LedgerSMB, and
2) if admins want to create ad-hoc reports tied to views they create, having a 
separate schema helps avoid namespace collisions.

-- 
Josh Berkus
PostgreSQL @ Sun
San Francisco