pg profile storage

[lionelbargeot]

Hi I try to store profile in a pg database instead of sqlite for an neux lizmap3 fresh install. I changed the profile declaration like this in profiles.ini.php

[jdb:jauth]
driver=pdo
dsn="pgsql:host=localhost;dbname=lizjauth;port=5432"
user="lizappli"
password="xxx"

When I play the install script I get the following error in postgresql logs

2016-01-05 18:59:11 CET ERREUR:  ne peut pas insérer les commandes multiples dans une instruction préparée
2016-01-05 18:59:11 CET INSTRUCTION :  CREATE TABLE jlx_user (
      usr_login character varying(50) NOT NULL DEFAULT '',
      usr_password character varying(120) NOT NULL DEFAULT '',
      usr_email character varying(255) NOT NULL DEFAULT ''
    );

    ALTER TABLE ONLY jlx_user
        ADD CONSTRAINT jlx_user_pkey PRIMARY KEY (usr_login);

It seems like if install script trys to do something that is not authorized in postgresql.

[rldhont]

Hi @lionelbargeot take a look at the Jelix 1.6 documentation: http://docs.jelix.org/en/manual-1.6

Do not use PDO for Postgresql connection: http://docs.jelix.org/en/manual-1.6/db/configuration#postgresql-profile

If you think it's useful, you can update the Lizmap documentation: https://github.com/3liz/lizmap-documentation

[pieri70]

Hello I'm also interested in this solution. If you find a way to handle Lizmap roles and credentials in postgres I'll be grateful if you share your solutions. My only concern is about changes to do on database structure with lizmap WC upgrade. Are they radical and frequent with lizmap changes or the db is almost static as structure?

Thanks Pietro

[mdouchin]

Hi pietro. It works indeed, and we use this storage for some of our projects. Since lizmap v3, updates will be handled automatically by Lizmap. When needed, Lizmap will create the new columns or new tables to modify the structure. Users tables will not change much.

We will try to improve documentation to help with this

[pieri70]

Super! Let me know when the documentation will be available. Thanks!

[3liz]

Moved to https://github.com/3liz/lizmap-documentation/issues/48