[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
Re: Installing LedgerSMB into a schema?
- Subject: Re: Installing LedgerSMB into a schema?
- From: Josh Berkus <..hidden..>
- Date: Mon, 6 Nov 2006 11:26:58 -0800
> 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:
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.
PostgreSQL @ Sun