Hi all;
This seems at least somewhat on-topic here, so at the risk of seeming
shamelessly self-promoting, I figured I would ask.
I am the main developer of an open source CRM suite
(http://hermesweb.sourceforge.net). We are in the beta-testing of our
0.3.0 release which will include experimental support for shared hosting
environments. I would be interested in helping any interested parties
beta test the software. Any feedback would also be greatly appreciated.
One of the main focuses of this upcoming version is that it will have at
least experimental support for shared hosting environments under
PostgreSQL (only). It accomplishes this by building on its portability
features. Here is the basic design and structure. Any feedback on this
part would be appreciated as well.
In order to maintain a consistent administrative interface across
database managers, my program maintains its own catalog of permissions,
group memberships etc. One of the main differences is that tables are
clustered together into modules and permissions are assigned to these
modules. This helps ensure that the permissions remain somewhat
internally consistent and that it is easy to administrate. In a
dedicated hosting environment, these permissions, when "activated" are
then translated into database permissions, and users login with their
own database accounts.
The shared hosting layer builds on the applications'
user/group/permissions catalogs and adds:
1) A function sh_authenticate(username, password) which creates a
temporary table with the authentication credentials. sh_su and
sh_revert_user are added to further manage identities.
2) Every non-catalog table has a rule which raises an exception if a
SELECT query is issued when no select permissions exist.
3) Every table has a trigger which raises an exception if insert,
update, or delete queries are run without appropriate permissions.
The advantage of this approach is that, with the exception of the
initial authentication, that the application doesn't have to know what
has changed. This also adds quite a bit of defense in depth.
However I have a few questions:
1) How effectively can triggers be applied to temporary tables? I would
like to add this to control access to the credential temporary table.
2) I am assuming that this could create a reasonable performance
overhead. But since this environment is likely to be most often used by
small-to-midsized businesses, is this likely to be a problem?
Best Wishes,
Chris Travers
