Installing LedgerSMB in a schema other than public (was: Integrating LedgerSMB with a member database)

[Nicolas Couchoud <..hidden..>]

Erik Huelsmann wrote:
> >     [I wrote earlier]
> >     The fact that LedgerSMB also uses
> >     PostgreSQL to store accounting data is very promising from this point of
> >     view.
> >       So I have some questions to ask :
> > 
> >     1) To ease integration between alumni and accounting data, I would like to
> >     have them both in the same database. This implies to put accounting data in
> >     a different namespace than alumni data to avoid name clashes. Is this
> >     possible ?
> > 
> > 
> > It *should* be possible, but in all honesty, I haven't tested.
> >  
> > 
> >     If I change the db_namespace variable in
> >     /etc/ledgersmb/ledgersmb.conf, is it enough to ensure that LedgerSMB will
> >     never do anything in namespace "public" ?
> > 
> > 
> > That's the intent indeed. 

I answered:
>   I tried to set this up using setup.pl on the web server. Here is what I
> obtained, with db_namespace=ledgersmb in ledgersmb.conf :
> 1) When trying on a non-existing database, it creates tables in schema
> public, then complains (in the server logs) that schema ledgersmb does not
> exists, and aborts.
> 2) When trying on an existing empty database, it says it found an unknown
> database, and does not do anything but back it up.
>   So what should I do ? Am I supposed to patch
> tools/prepare-company-database.sh to set things up manually ?

  Essentially, this is what I did. But this was not enough : for example,
LedgerSMB.pl tries to read table "defaults" without specifying a schema.

  The version I use is the Debian package version 1.3.46-1~bpo8+1
(jessie-backports).
  Here is what I eventually did to make things work :
  The initial situation is :
* The schema "public" in the database where I want to install LedgerSMB
contains many things I do not want to alter.
* Only few users have the USAGE privilege on it.
* Debian package's install script created the role "ledgersmb" with
superuser rights.
  What I did is :
1) To make sure the existing database installation will not be altered,
ALTER ROLE ledgersmb WITH NOSUPERUSER CREATEROLE LOGIN;
2) In postgresql.conf, add ledgersmb at the end of variable search_path.
3) CREATE SCHEMA AUTHORIZATION ledgersmb;
   GRANT USAGE ON SCHEMA ledgersmb to PUBLIC;
  So, the role "ledgersmb" and any roles created by LedgerSMB will have the
schema "ledgersmb" as their default schema (and it is the only schema on
which they have the USAGE privilege).
4) Install the necessary extensions (first four lines of Pg-database.sql)
as user "postgres" (user "ledgersmb cannot do that without superusers
rights).
5) Run a patched version of prepare-company-database.sh where the parts
creating the database and user "ledgersmb" have been commented out -- which
implies that it does not need to be run as root.
  Strangely, the role "admin" was not created, but was present in the
database. But now, setup.pl on the web page recognizes the database, so one
can use it to create a user, and it seems one can now run LedgerSMB (I have
not tested extensively).

------------------------------------------------------------------------------
_______________________________________________
Ledger-smb-users mailing list
..hidden..
https://lists.sourceforge.net/lists/listinfo/ledger-smb-users