Installing LedgerSMB into a schema?

["Chris Travers" <..hidden..>]

On 11/6/06, Josh Berkus <..hidden..> wrote:
> 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.

My larger concern has to do with data separation and security of
sensitive information.  But this works too :-).

> 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.
I agree here but for a different reason.  Public schema tables are one
thing if your application exists in a vacuum.  Now, if you want to be
able to interface on the db level with another application, like, say,
Interchange, then you have issues.  it is cleaner from an interop
perspective to get things out of the public schema.

> 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.

Good point.
> 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
I would suggest calling the main schema something like erp or lsmb.
Logins might be lsmbusers, and reports can just be 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.

Also, I think having reporting separate from the main application
makes sense from a number of other perspectives.

1)  Interop.  Suppose LSMB and Interchange are in the same db,
different schemas, and integrated via triggers.  Having reports in a
separate schema allows an admin to create adhoc reports across such
application combinations.

2)  I think that having reports in a separate schema is conceptually cleaner.

I think the reason why SL does the things the way it does is that SL
has become trapped by the problem of cross-rdbms schema portability.
In particular Oracle enforces a 1:1 relationship between schemas and
users(!) so multiple schemas are sort of problematic if one wants to
tack on Oracle support.

Best Wishes,
Chris Travers