Anyone know a good opensource CRM that actually installs with Posgtres?

Bradley Kieser wrote:

I hope that someone has cracked this one because I have run into a
brick wall the entire week and after 3 all-nighters with bad
installations, I would appreciate hearing from others!

I am looking for a decent OpenSource CRM system that will run with
Postgres. SugarCRM seems to be the most popular but it's MySQL-centric
and its opensource parts are very restricted.

Not a recommendation, coz I haven't actually used it, but you might try
Drupal, if you haven't already. I've seen Drupal work OK, & it claims to
support Postgres.

Brent Wood

Bradley Kieser wrote:

I hope that someone has cracked this one because I have run into a
brick wall the entire week and after 3 all-nighters with bad
installations, I would appreciate hearing from others!

I am looking for a decent OpenSource CRM system that will run with
Postgres. SugarCRM seems to be the most popular but it's MySQL-centric
and its opensource parts are very restricted.

Bradley, I've got about 2.5 out of 3 of what you are looking for,
perhaps it might work out for you. We have a GPL database application
framework that we have used for a handful of CRM-style applications
(links below). It runs on Postgres, is completely GPL, and is written
in PHP.

Now the bad news :)

We have only crude CRM stuff, and you may end up having to put more into
this area than you want to. As I said, we use it ourselves for some
stuff, but our needs are simple in that area. Its primary purpose is
high-powered business database apps.

Now on the third hand, we have a pure business app for Medical Practice
Management and we are about to add the CRM to it so that the
scheduling/billing database also serves the doctor's public website,
doing things like showing schedules, listing active insurances and other
nifty stuff like that. And of course the doc can enter new articles.
We think its really cool to be able to integrate CRM with business this way.

The only other bad news is that it is Linux only. It has been installed
on Mac but nobody here can support you with that. In principle it can
run on Windows because Apache, PHP and Postgres run on windows, but
again, you'd become the guy I send other people to once you get it going :)

Here are three CRM sites running it. They are my company site, the
project site itself, and a site for a rental home:

www.secdat.com
www.andromeda-project.org
www.manisteeforestretreat.com

The middle one is the actual project, its got some docs, some tutorials,
and a link to the sourceforge download.

On Mar 8, 2007, at 6:26 PM, Brent Wood wrote:

Bradley Kieser wrote:

I hope that someone has cracked this one because I have run into a
brick wall the entire week and after 3 all-nighters with bad
installations, I would appreciate hearing from others!

I am looking for a decent OpenSource CRM system that will run with
Postgres. SugarCRM seems to be the most popular but it's MySQL-
centric and its opensource parts are very restricted.

Not a recommendation, coz I haven't actually used it, but you might
try Drupal, if you haven't already. I've seen Drupal work OK, & it
claims to support Postgres.

It's not a CRM, though.

Last time I looked, about a year ago, I came to the same conclusion
as the OP.

There are at least some excellent commercial CRMs that support
postgresql, so there's no reason why the open source ones shouldn't,
other than the whole open-source / PHP / MySQL hack mindset. That and
the whole mysql installed base issue.

Cheers,
Steve

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 03/08/07 20:38, Kenneth Downs wrote:
[snip]

Management and we are about to add the CRM to it so that the
scheduling/billing database also serves the doctor's public website,

Is that wise? One bug and a cracker is poking around some very
private stuff!!

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFF8N6/S9HxQb37XmcRAsgfAKCq5hSw1XpU+piaL2RBoihoPTMfZwCdG5D3
YndimzGriPXUM49P9b596og=
=wU1C
-----END PGP SIGNATURE-----

On Fri, 2007-03-09 at 01:22 +0000, Bradley Kieser wrote:

I hope that someone has cracked this one because I have run into a brick
wall the entire week and after 3 all-nighters with bad installations, I
would appreciate hearing from others!

I am looking for a decent OpenSource CRM system that will run with
Postgres. SugarCRM seems to be the most popular but it's MySQL-centric
and its opensource parts are very restricted.

vTiger is also mySQL-centric.

I thought that I had a corker of a system with "centricCRM" but when it
came to actually installing it, I am 48 hours down and hacking through
screen after screen of installation errors. Basically, it relies way too
much on ant and Java tools. Nothing against Java but my experience with
ant used for installing PG schemas is a dismal track record of error and
frustration. centric CRM is no exception. Frankly, it just doesn't work
and after trying to hack out the ant into a PG script I have decided to
give it up as a bad job.

XRMS promises to run on PG but... it doesn't. The core system is fine,
but useless without the plugins. The Plugins are mySQL-specific again, I
spent several all-nighters previously hacking through installation
screens attempting to convert mysql to PG, making software patches...
you get the picture.

XLSuite looks very promising. Awesome interface, looks great... only
it's just not ready yet. It is a year away from being at full PG
production level.

Compiere doesn't support PG.

OpenTAPS the demo won't even work. And it's US-centric whereas we are in
the UK. A pity that it's so very much tied to the US as it could be very
good.

I have tried numerous other CRMs but all the same - either don't run on
PG, claim to but in reality don't or are simply pre-Alpha and not ready
for production use.

So if anyone has actually cracked this, please let me know! I really
need a good CRM.

It has to be OpenSource, not just out of principle, but we need to
integrate it into an existing business with established inhouse software
so we need to be able to customise the code.

----
my experience with CRM stuff is that the general CRM application never
does what you want and you are going to have to hack it no matter what.
If you are comfortable with going PHP, you just download sugarcrm or
vtiger or whatever comes closest to your vision of your needs and hack
away from there.

Myself, I am very much enthralled with Ruby on Rails and see it as an
amazingly rapid development system and have been writing everything from
scratch for our non-profit.

Craig

On Fri, Mar 09, 2007 at 01:22:22AM +0000, Bradley Kieser wrote:

I hope that someone has cracked this one because I have run into a brick
wall the entire week and after 3 all-nighters with bad installations, I
would appreciate hearing from others!

I am looking for a decent OpenSource CRM system that will run with
Postgres. SugarCRM seems to be the most popular but it's MySQL-centric
and its opensource parts are very restricted.

Hi...

lxOffice runs with PostgreSQL.

http://www.lx-office.org

regards
Mario

--
-----------------------------------------------------
| havelsoft.com - Ihr Service Partner für Open Source |
| Tel: 033876-21 966 |
| Notruf: 0173-277 33 60 |
| http://www.havelsoft.com |
| |
| Inhaber: Mario Günterberg |
| Mützlitzer Strasse 19 |
| 14715 Märkisch Luch |
-----------------------------------------------------

Hello,

Bradley Kieser wrote:

I hope that someone has cracked this one because I have run into a brick
wall the entire week and after 3 all-nighters with bad installations, I
would appreciate hearing from others!

[...]

Compiere doesn't support PG.

You could checkout Adempiere wich is a fork of Compiere:
<http://www.adempiere.com/&gt;

They claim to support PG and they are actively improving on that:
<http://www.adempiere.com/wiki/index.php/Release_315&gt;

Download location:
<http://sourceforge.net/project/showfiles.php?group_id=176962&amp;package_id=207834&gt;

I have tried numerous other CRMs but all the same - either don't run on
PG, claim to but in reality don't or are simply pre-Alpha and not ready
for production use.

On the surface Adempiere seems to meet your requirements but I never used it so I
can't tell if its another dead end.

--
Luis Neves

centric crm works with postgres

John

Mario Guenterberg wrote:

Ron Johnson wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 03/08/07 20:38, Kenneth Downs wrote:
[snip]

Management and we are about to add the CRM to it so that the
scheduling/billing database also serves the doctor's public website,

Is that wise? One bug and a cracker is poking around some very
private stuff!!

We use the "Spartan" security model rather than perimeter defense, which
gives us the confidence to do things that others may not.

The general outline is as follows.

First, security is defined directly in terms of tables, it is not
arbitrated by code. The "public" group has SELECT access to the
articles table and the schedules tables, that's it. If a person figures
out how our links work and tries to access the "claims" table it will
simply come up blank (and we get an email). The "staff" group has
read/write access to scheduling, and so forth.

Second, the security assignments to tables are generated by a builder,
which revokes and grants all priveleges to all groups on all tables.
This is to prevent the possibility of error or omission if a person
were to implement it table-by-table.

Third, as you probably have guessed, we do not connect to the database
as a super-user and then arbitrate in code. Regular staff connect with
real database accounts and their security in the database is limited to
those accounts. Bugs in code that try to provide unauthorized
information will return server errors. A person with no account, such
as the public user, has the lowest security priveleges, as mentioned above.

Finally, our URL parameter scheme is really very simple and easy to
figure out, we are not hiding it. Anybody trying to get something they
can't would not take long to work it out (especially as the underlying
tools are GPL), they would simply find it did them no good.

It is good to be very concerned about security, especially HIPPA, where
there are legal ramifications. It is also very good to be able to
provide convenience and security in one package.

Bradley Kieser wrote:

I am looking for a decent OpenSource CRM system that will run with
Postgres.

OpenTAPS the demo won't even work. And it's US-centric whereas we are in
the UK. A pity that it's so very much tied to the US as it could be very
good.

What actually didn't work with OpenTaps? "OpenTaps" is additional modules that
work with the Apache Open For Business project core product.

What functions that SugarCRM supply that you need in OpenTaps? We are funding
($$,$$$ USD) a lot of changes right now in OpenTaps and OFBiz, and perhaps you
could let me know where the feature gap is so that I don't end up with a "I
can't believe we forgot about that".

--
Walter

On Fri, Mar 09, 2007 at 08:08:11AM -0500, Kenneth Downs wrote:

First, security is defined directly in terms of tables, it is not
arbitrated by code. The "public" group has SELECT access to the
articles table and the schedules tables, that's it. If a person figures
out how our links work and tries to access the "claims" table it will
simply come up blank (and we get an email).

How ?

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

Karsten Hilbert wrote:

On Fri, Mar 09, 2007 at 08:08:11AM -0500, Kenneth Downs wrote:

First, security is defined directly in terms of tables, it is not
arbitrated by code. The "public" group has SELECT access to the
articles table and the schedules tables, that's it. If a person figures
out how our links work and tries to access the "claims" table it will
simply come up blank (and we get an email).

How ?

Karsten

If a user has not logged in, that is, if they are an anonymous visitor,
the web framework will connect to the database as the default "public"
user. Our system is deny-by-default, so this user cannot actually read
from any table unless specifically granted permission. In the case
being discussed, the public user is given SELECT permission on some
columns of the insurance carriers table, and on the schedules table.

The column-level security is important, as you don't want anybody seeing
the provider id!

If the user figures out our URL scheme, they might try something like
"?gp_page=patients" and say "Wow I'm clever I'm going to look at the
patients table", except that the public user has no privilege on the
table. The db server will throw a permission denied error.

First, security is defined directly in terms of tables, it is not
arbitrated by code. The "public" group has SELECT access to the
articles table and the schedules tables, that's it. If a person
figures out how our links work and tries to access the "claims" table
it will simply come up blank (and we get an email).

If a user has not logged in, that is, if they are an anonymous visitor,
the web framework will connect to the database as the default "public"
user. Our system is deny-by-default, so this user cannot actually read
from any table unless specifically granted permission. In the case
being discussed, the public user is given SELECT permission on some
columns of the insurance carriers table, and on the schedules table.

Huh. Does that imply that the web framework still holds a number of
different DB credentials? Or does each user need to supply their
specific DB credentials as their authentication so the web framework is
merely a proxy to the DB?

(Having recently discovered a major security oversight in one of my
employer's webapps, my mind's hot on this kind of thing.)

Kevin

In response to Kevin Hunter <hunteke@earlham.edu>:

First, security is defined directly in terms of tables, it is not
arbitrated by code. The "public" group has SELECT access to the
articles table and the schedules tables, that's it. If a person
figures out how our links work and tries to access the "claims" table
it will simply come up blank (and we get an email).

If a user has not logged in, that is, if they are an anonymous visitor,
the web framework will connect to the database as the default "public"
user. Our system is deny-by-default, so this user cannot actually read
from any table unless specifically granted permission. In the case
being discussed, the public user is given SELECT permission on some
columns of the insurance carriers table, and on the schedules table.

Huh. Does that imply that the web framework still holds a number of
different DB credentials? Or does each user need to supply their
specific DB credentials as their authentication so the web framework is
merely a proxy to the DB?

(Having recently discovered a major security oversight in one of my
employer's webapps, my mind's hot on this kind of thing.)

What's hot in my mind is "how do you securely maintain the database connection
information between page loads?"

--
Bill Moran
http://www.potentialtech.com

On Fri, Mar 09, 2007 at 11:02:45AM -0500, Kenneth Downs wrote:

First, security is defined directly in terms of tables, it is not
arbitrated by code. The "public" group has SELECT access to the
articles table and the schedules tables, that's it. If a person figures
out how our links work and tries to access the "claims" table it will
simply come up blank (and we get an email).

If a user has not logged in, that is, if they are an anonymous visitor,
the web framework will connect to the database as the default "public"
user. Our system is deny-by-default, so this user cannot actually read

from any table unless specifically granted permission. In the case

being discussed, the public user is given SELECT permission on some
columns of the insurance carriers table, and on the schedules table.

The column-level security is important, as you don't want anybody seeing
the provider id!

If the user figures out our URL scheme, they might try something like
"?gp_page=patients" and say "Wow I'm clever I'm going to look at the
patients table", except that the public user has no privilege on the
table. The db server will throw a permission denied error.

My interest was more towards the "we get an email" part.
What level do you send that from ? A trigger ?

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 03/09/07 10:02, Kenneth Downs wrote:

Karsten Hilbert wrote:

On Fri, Mar 09, 2007 at 08:08:11AM -0500, Kenneth Downs wrote:

First, security is defined directly in terms of tables, it is not
arbitrated by code. The "public" group has SELECT access to the
articles table and the schedules tables, that's it. If a person
figures out how our links work and tries to access the "claims" table
it will simply come up blank (and we get an email).

How ?

Karsten

If a user has not logged in, that is, if they are an anonymous visitor,
the web framework will connect to the database as the default "public"
user. Our system is deny-by-default, so this user cannot actually read
from any table unless specifically granted permission. In the case
being discussed, the public user is given SELECT permission on some
columns of the insurance carriers table, and on the schedules table.

The column-level security is important, as you don't want anybody seeing
the provider id!

If the user figures out our URL scheme, they might try something like
"?gp_page=patients" and say "Wow I'm clever I'm going to look at the
patients table", except that the public user has no privilege on the
table. The db server will throw a permission denied error.

What about an SQL injection bug that allows for increased privileges?

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFF8ZHlS9HxQb37XmcRAjTMAKDSBDYYmTt9/ivGtl59YtITz5Lb4ACffLzQ
MlCCcfGd5sS3aNhtgDrd+rA=
=cwTh
-----END PGP SIGNATURE-----

Karsten Hilbert wrote:

If the user figures out our URL scheme, they might try something like
"?gp_page=patients" and say "Wow I'm clever I'm going to look at the
patients table", except that the public user has no privilege on the
table. The db server will throw a permission denied error.

My interest was more towards the "we get an email" part.
What level do you send that from ? A trigger ?

The web framework does that. The web framework decodes the HTTP request
and executes any SQL it thinks the user wants. If there is a
permissions error then it sends an email to the administrator.

The underlying idea is that the GET/POST parameters are pretty standard
and easy to decode and convert into SQL commands. For instance, by
default we assume a page = a table, and lacking any code that overrides
that assumption, a request for a page becomes a search request in the
table of the same name. This is the first thing a cracker would depend
upon if he were trying to pry.

Ron Johnson wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 03/09/07 10:02, Kenneth Downs wrote:

Karsten Hilbert wrote:

On Fri, Mar 09, 2007 at 08:08:11AM -0500, Kenneth Downs wrote:

First, security is defined directly in terms of tables, it is not
arbitrated by code. The "public" group has SELECT access to the
articles table and the schedules tables, that's it. If a person
figures out how our links work and tries to access the "claims" table
it will simply come up blank (and we get an email).

How ?

Karsten

If a user has not logged in, that is, if they are an anonymous visitor,
the web framework will connect to the database as the default "public"
user. Our system is deny-by-default, so this user cannot actually read
from any table unless specifically granted permission. In the case
being discussed, the public user is given SELECT permission on some
columns of the insurance carriers table, and on the schedules table.

The column-level security is important, as you don't want anybody seeing
the provider id!

If the user figures out our URL scheme, they might try something like
"?gp_page=patients" and say "Wow I'm clever I'm going to look at the
patients table", except that the public user has no privilege on the
table. The db server will throw a permission denied error.

What about an SQL injection bug that allows for increased privileges?

Um, web programming 101 is that you escape quotes on user-supplied
inputs. That ends SQL injection.

After that, as stated above, anything the user attempts is executed at
his privilege level. For an anonymous user, that's the lowest.

The biggest security limitation we have is actually a weakness in
Postgres - the inability to restrict the abilities of a user with
CREATUSER rights, they can make somebody who can do anything. For
higher security this requires no ability for public registration of
accounts. This would be solved if we could restrict a CREATUSER user to
only GRANTing to roles they themselves are in.

Karsten-
You would need some manner of DML operation to take place (in this way the DB trigger could sense the change in DB state to activate e-mail)
Otherwise you could do so at your Webapp login
Does this answer your question?
Tak
Martin--
---------------------------------------------------------------------------
This e-mail message (including attachments, if any) is intended for the use of the individual or entity to which it is addressed and may contain information that is privileged, proprietary , confidential and exempt from disclosure. If you are not the intended recipient, you are notified that any dissemination, distribution or copying of this communication is strictly prohibited.
---------------------------------------------------------------------------
Le présent message électronique (y compris les pièces qui y sont annexées, le cas échéant) s'adresse au destinataire indiqué et peut contenir des renseignements de caractère privé ou confidentiel. Si vous n'êtes pas le destinataire de ce document, nous vous signalons qu'il est strictement interdit de le diffuser, de le distribuer ou de le reproduire.
----- Original Message -----
From: "Karsten Hilbert" <Karsten.Hilbert@gmx.net>
To: <pgsql-general@postgresql.org>
Sent: Friday, March 09, 2007 11:45 AM
Subject: Re: HIPPA (was Re: [GENERAL] Anyone know ...)