Increasing security level by restricting Dataset access to associated user on SQL server

[Nils <..hidden..>]

Dear LSMB community,

I just completed my install of LSMB 1.2.14 and trying to figure out what can I do to (or how to) improve the security at the SQL access level.

Two words about my setup:
- a web server with LSMB installed
- a DB server with PostgreSQL 8.1 installed
- Current pg_hba.conf file:
host    ledgersmb       ledgersmb       <web server IP>/32  md5
host    template1       ledgersmb       <web server IP>/32  md5
host    template1       postgres       <web server IP>/32  md5
host    company1       ledgersmb       <web server IP>/32  md5
host    company1       postgres       <web server IP>/32  md5
host    company1       user1       <web server IP>/32  md5
host    company2       ledgersmb       <web server IP>/32  md5
host    company2       postgres       <web server IP>/32  md5
host    company2       user2       <web server IP>/32  md5

What I understood:
When creating a LSMB user via the web interface, in the database section, a user and password is requested to connect to SQL.
Using here the "ledgersmb" account is fine. However, using the SQL user "user1" (of course previously created in PSQL and pg_hba.conf updated) give the following error:
Error!


UPDATE defaults
SET value = ?
WHERE setting_key = ?
ERROR: current transaction is aborted, commands ignored until end of transaction block

What I'm trying to achieve:
Create a 1-to-1 match between a LSMB user and a dedicated SQL user.
The idea behind is to insure the chain of authentification/authorization/accounting from the LSMB front-end up to the PostgreSQL back-end. Not having a single user like "ledgersmb" used in LedgerSMB web with full right to all Dataset/DB. Maybe it is not so simple as the LSMB users are sorted in the ledgersmb SQL DB that obviously need to be read from the web server...

I saw some post about the possible rework of the auth method for the futur v1.3, maybe I'ma asking for something not possible in 1.2.x that is planned in 1.3.x ?

Not to forgot, thanks a lot for the job done on LSMB ! I reviewed several free accounting softwares and LSMB get 1st rank, mostly due to these facts: opensource+GPL/web interface/simple interface/locale/security focused/dynamic.

Thanks in advance for any helps and best regards

Nils