lizmap-web-client: Error when login after configure lizmap to use postgresql as jdb and lizlog

I configured lizmap to work with postgresql either for jdb than for lizlog, and also configured to authenticate by Active Directory: I deleted all the tables and sequences in postgres; I recreated the structure as a .sql file of which I paste the commands below but when I try to login as admin (not active directory user) the error in the error.log is the same 2018-02-05 10:17:27 192.168.23.171 error 2018-02-05 10:17:27 [403] query non valida (ERROR: current transaction is aborted, commands ignored until end of transaction block(SELECT "log_counter"."id", "log_counter"."key", "log_counter"."counter", "log_counter"."repository", "log_counter"."project" FROM "log_counter" AS "log_counter" WHERE "log_counter"."key" = 'login' AND "log_counter"."repository" IS NULL AND "log_counter"."project" IS NULL LIMIT 1 OFFSET 0)) /opt/www/lizmap/lib/jelix/plugins/db/pgsql/pgsql.dbconnection.php 165

CRETE SCRIPT

------------------------
BEGIN TRANSACTION;

DROP TABLE IF EXISTS jlx_user CASCADE;
CREATE TABLE IF NOT EXISTS jlx_user (
	usr_login	varchar ( 50 ) NOT NULL DEFAULT '',
	usr_password	varchar ( 120 ) NOT NULL DEFAULT '',
	usr_email	varchar ( 255 ) NOT NULL DEFAULT '',
	firstname	VARCHAR ( 100 ) NOT NULL DEFAULT '',
	lastname	VARCHAR ( 100 ) NOT NULL DEFAULT '',
	organization	VARCHAR ( 100 ) DEFAULT '',
	phonenumber	VARCHAR ( 20 ) DEFAULT '',
	street	VARCHAR ( 150 ) DEFAULT '',
	postcode	VARCHAR ( 10 ) DEFAULT '',
	city	VARCHAR ( 150 ) DEFAULT '',
	country	VARCHAR ( 100 ) DEFAULT '',
	comment	TEXT DEFAULT '',
	PRIMARY KEY(usr_login)
);
INSERT INTO jlx_user (usr_login,usr_password,usr_email,firstname,lastname,organization,phonenumber,street,postcode,city,country,comment) VALUES ('admin','$2y$10$BvCLd01nukWxY/7Tn1XpZuHuY/KQ0V.4jfhEZjqT5mEQQXv./Uqyu','admin@localhost.localdomain','','','','','','','','',''),
 ('lizadmin','$2y$10$wz6Edhdlhmvtx7.AE.uPWu/vCivpD6ucjfwH8EQGh5pYTGhxLefk6','lizadmin@nomail.nomail','','','','','','','','',''),
 ('logintranet','$2y$10$HnIO1sHG9oEtlJHXBdnpP.BxtMdKfXFjUJ5eq.Athwu3Y7Q7XL2oi','logintranet@nomail.nomail','','','','','','','','','');
 
DROP TABLE IF EXISTS jacl2_user_group CASCADE;
CREATE TABLE IF NOT EXISTS jacl2_user_group (
	login	varchar ( 50 ) NOT NULL,
	id_aclgrp	varchar ( 50 ) NOT NULL,
	PRIMARY KEY(login,id_aclgrp)
);
INSERT INTO jacl2_user_group (login,id_aclgrp) VALUES ('admin','__priv_admin'),
 ('admin','admins'),
 ('lizadmin','users'),
 ('lizadmin','__priv_lizadmin'),
 ('logintranet','users'),
 ('logintranet','__priv_logintranet'),
 ('lizadmin','lizadmins'),
 ('logintranet','intranet');

DROP TABLE IF EXISTS jacl2_subject_group CASCADE;
CREATE TABLE IF NOT EXISTS jacl2_subject_group (
	id_aclsbjgrp	VARCHAR ( 50 ) NOT NULL,
	label_key	VARCHAR ( 60 ) NOT NULL,
	PRIMARY KEY(id_aclsbjgrp)
);
INSERT INTO jacl2_subject_group (id_aclsbjgrp,label_key) VALUES ('acl.grp.user.management','jacl2db~acl2db.acl.grp.user.management'),
 ('acl.grp.group.management','jacl2db~acl2db.acl.grp.group.management'),
 ('auth.grp.user.management','jelix~auth.acl.grp.user.management'),
 ('lizmap.admin.grp','admin~jacl2.lizmap.admin.grp'),
 ('lizmap.grp','admin~jacl2.lizmap.grp');

DROP TABLE IF EXISTS jacl2_subject CASCADE;
CREATE TABLE IF NOT EXISTS jacl2_subject (
	id_aclsbj	varchar ( 100 ) NOT NULL,
	label_key	varchar ( 100 ) DEFAULT NULL,
	id_aclsbjgrp	VARCHAR ( 50 ) DEFAULT NULL,
	PRIMARY KEY(id_aclsbj)
);
INSERT INTO jacl2_subject (id_aclsbj,label_key,id_aclsbjgrp) VALUES ('acl.user.view','jacl2db~acl2db.acl.user.view','acl.grp.user.management'),
 ('acl.user.modify','jacl2db~acl2db.acl.user.modify','acl.grp.user.management'),
 ('acl.group.modify','jacl2db~acl2db.acl.group.modify','acl.grp.group.management'),
 ('acl.group.create','jacl2db~acl2db.acl.group.create','acl.grp.group.management'),
 ('acl.group.delete','jacl2db~acl2db.acl.group.delete','acl.grp.group.management'),
 ('acl.group.view','jacl2db~acl2db.acl.group.view','acl.grp.group.management'),
 ('auth.users.list','jelix~auth.acl.users.list','auth.grp.user.management'),
 ('auth.users.view','jelix~auth.acl.users.view','auth.grp.user.management'),
 ('auth.users.modify','jelix~auth.acl.users.modify','auth.grp.user.management'),
 ('auth.users.create','jelix~auth.acl.users.create','auth.grp.user.management'),
 ('auth.users.delete','jelix~auth.acl.users.delete','auth.grp.user.management'),
 ('auth.users.change.password','jelix~auth.acl.users.change.password','auth.grp.user.management'),
 ('auth.user.view','jelix~auth.acl.user.view','auth.grp.user.management'),
 ('auth.user.modify','jelix~auth.acl.user.modify','auth.grp.user.management'),
 ('auth.user.change.password','jelix~auth.acl.user.change.password','auth.grp.user.management'),
 ('lizmap.admin.access','admin~jacl2.lizmap.admin.access','lizmap.admin.grp'),
 ('lizmap.admin.services.update','admin~jacl2.lizmap.admin.services.update','lizmap.admin.grp'),
 ('lizmap.admin.repositories.create','admin~jacl2.lizmap.admin.repositories.create','lizmap.admin.grp'),
 ('lizmap.admin.repositories.update','admin~jacl2.lizmap.admin.repositories.update','lizmap.admin.grp'),
 ('lizmap.admin.repositories.delete','admin~jacl2.lizmap.admin.repositories.delete','lizmap.admin.grp'),
 ('lizmap.repositories.view','admin~jacl2.lizmap.repositories.view','lizmap.grp'),
 ('lizmap.admin.repositories.view','admin~jacl2.lizmap.admin.repositories.view','lizmap.admin.grp'),
 ('lizmap.admin.services.view','admin~jacl2.lizmap.admin.services.view','lizmap.admin.grp'),
 ('lizmap.tools.edition.use','admin~jacl2.lizmap.tools.edition.use','lizmap.grp'),
 ('lizmap.tools.loginFilteredLayers.override','admin~jacl2.lizmap.tools.loginFilteredLayers.override','lizmap.grp'),
 ('lizmap.tools.displayGetCapabilitiesLinks','admin~jacl2.lizmap.tools.displayGetCapabilitiesLinks','lizmap.grp'),
 ('lizmap.tools.layer.export','admin~jacl2.lizmap.tools.layer.export','lizmap.grp');

 DROP TABLE IF EXISTS jacl2_rights CASCADE;
 CREATE TABLE IF NOT EXISTS jacl2_rights (
	id_aclsbj	varchar ( 100 ) NOT NULL,
	id_aclgrp	varchar ( 50 ) NOT NULL,
	id_aclres	varchar ( 100 ) NOT NULL DEFAULT '-',
	canceled	integer NOT NULL DEFAULT 0,
	PRIMARY KEY(id_aclsbj,id_aclgrp,id_aclres)
);
INSERT INTO jacl2_rights (id_aclsbj,id_aclgrp,id_aclres,canceled) VALUES ('acl.group.modify','admins','-',0),
 ('acl.group.create','admins','-',0),
 ('acl.group.delete','admins','-',0),
 ('acl.group.view','admins','-',0),
 ('acl.user.modify','admins','-',0),
 ('acl.user.view','admins','-',0),
 ('auth.users.list','admins','-',0),
 ('auth.users.view','admins','-',0),
 ('auth.users.modify','admins','-',0),
 ('auth.users.create','admins','-',0),
 ('auth.users.delete','admins','-',0),
 ('auth.users.change.password','admins','-',0),
 ('auth.user.view','admins','-',0),
 ('auth.user.modify','admins','-',0),
 ('auth.user.change.password','admins','-',0),
 ('auth.user.view','users','-',0),
 ('auth.user.modify','users','-',0),
 ('auth.user.change.password','users','-',0),
 ('lizmap.admin.repositories.view','admins','-',0),
 ('lizmap.admin.services.view','admins','-',0),
 ('lizmap.admin.access','admins','-',0),
 ('lizmap.admin.repositories.create','admins','-',0),
 ('lizmap.admin.repositories.delete','admins','-',0),
 ('lizmap.admin.repositories.update','admins','-',0),
 ('lizmap.admin.services.update','admins','-',0),
 ('lizmap.admin.access','lizadmins','-',0),
 ('lizmap.admin.services.update','lizadmins','-',0),
 ('lizmap.admin.repositories.create','lizadmins','-',0),
 ('lizmap.admin.repositories.delete','lizadmins','-',0),
 ('lizmap.admin.repositories.update','lizadmins','-',0),
 ('lizmap.admin.repositories.view','lizadmins','-',0),
 ('lizmap.admin.services.view','lizadmins','-',0),
 ('lizmap.tools.edition.use','admins','intranet',0),
 ('lizmap.repositories.view','admins','intranet',0),
 ('lizmap.tools.loginFilteredLayers.override','admins','intranet',0),
 ('lizmap.tools.displayGetCapabilitiesLinks','admins','intranet',0),
 ('lizmap.tools.layer.export','admins','intranet',0),
 ('lizmap.tools.edition.use','admins','montpellier',0),
 ('lizmap.repositories.view','admins','montpellier',0),
 ('lizmap.tools.loginFilteredLayers.override','admins','montpellier',0),
 ('lizmap.tools.displayGetCapabilitiesLinks','admins','montpellier',0),
 ('lizmap.tools.layer.export','admins','montpellier',0),
 ('lizmap.tools.edition.use','lizadmins','intranet',0),
 ('lizmap.repositories.view','lizadmins','intranet',0),
 ('lizmap.tools.loginFilteredLayers.override','lizadmins','intranet',0),
 ('lizmap.tools.displayGetCapabilitiesLinks','lizadmins','intranet',0),
 ('lizmap.tools.layer.export','lizadmins','intranet',0),
 ('lizmap.tools.edition.use','lizadmins','montpellier',0),
 ('lizmap.repositories.view','lizadmins','montpellier',0),
 ('lizmap.tools.loginFilteredLayers.override','lizadmins','montpellier',0),
 ('lizmap.tools.displayGetCapabilitiesLinks','lizadmins','montpellier',0),
 ('lizmap.tools.layer.export','lizadmins','montpellier',0),
 ('lizmap.tools.edition.use','intranet','intranet',0),
 ('lizmap.repositories.view','intranet','intranet',0),
 ('lizmap.tools.loginFilteredLayers.override','intranet','intranet',0),
 ('lizmap.tools.displayGetCapabilitiesLinks','intranet','intranet',0),
 ('lizmap.tools.layer.export','intranet','intranet',0),
 ('lizmap.tools.edition.use','intranet','montpellier',0),
 ('lizmap.repositories.view','intranet','montpellier',0),
 ('lizmap.tools.loginFilteredLayers.override','intranet','montpellier',0),
 ('lizmap.tools.displayGetCapabilitiesLinks','intranet','montpellier',0),
 ('lizmap.tools.layer.export','intranet','montpellier',0),
 ('lizmap.tools.edition.use','__anonymous','montpellier',0),
 ('lizmap.repositories.view','__anonymous','montpellier',0),
 ('lizmap.tools.loginFilteredLayers.override','__anonymous','montpellier',0),
 ('lizmap.tools.displayGetCapabilitiesLinks','__anonymous','montpellier',0);

DROP TABLE IF EXISTS jacl2_group CASCADE;
CREATE TABLE IF NOT EXISTS jacl2_group (
	id_aclgrp	varchar ( 50 ),
	name	varchar ( 150 ) NOT NULL,
	grouptype	integer NOT NULL DEFAULT '0',
	ownerlogin	varchar ( 50 ),
	PRIMARY KEY(id_aclgrp)
);
INSERT INTO jacl2_group (id_aclgrp,name,grouptype,ownerlogin) VALUES ('__anonymous','anonymous',0,NULL),
 ('admins','admins',0,NULL),
 ('users','users',1,NULL),
 ('__priv_admin','admin',2,'admin'),
 ('lizadmins','lizadmins',0,NULL),
 ('intranet','Intranet demos group',0,NULL),
 ('__priv_lizadmin','lizadmin',2,'lizadmin'),
 ('__priv_logintranet','logintranet',2,'logintranet');

DROP TABLE IF EXISTS geobookmark CASCADE;
CREATE TABLE IF NOT EXISTS geobookmark (
	id	SERIAL PRIMARY KEY,
	usr_login	text NOT NULL,
	bname	text NOT NULL,
	bmap	text NOT NULL,
	bparams	text NOT NULL,
	FOREIGN KEY(usr_login) REFERENCES jlx_user(usr_login)
);

DROP TABLE IF EXISTS log_detail CASCADE;
CREATE TABLE IF NOT EXISTS log_detail (
	id	SERIAL PRIMARY KEY,
	log_key	VARCHAR NOT NULL,
	log_timestamp	TIMESTAMP WITH TIME ZONE,
	log_user	VARCHAR,
	log_content	TEXT,
	log_repository	VARCHAR,
	log_project	VARCHAR,
	log_ip	VARCHAR
);

DROP TABLE IF EXISTS log_counter CASCADE;
CREATE TABLE IF NOT EXISTS log_counter (
	id	SERIAL PRIMARY KEY,
	key	VARCHAR NOT NULL,
	counter	INTEGER,
	repository	VARCHAR,
	project	VARCHAR
);

COMMIT;

---

profiles.ini.php

;<?php die(''); ?>
;for security reasons, don't remove or modify the first line

[jdb]

; name of the default profile to use for any connection
default=jauth
;jacl2_profile=jauth
jacl2_profile=pgldapdao

[jdb:jauth]
driver=sqlite3
database="var:db/jauth.db"

[jdb:lizlog]
;driver=sqlite3
;database="var:db/logs.db"
driver = "pgsql"
database = "lizmap"
host = "localhost"
port = "5678"
user = "lizmap"
password = "*************"
;service =
persistent = "on"
force_encoding = on
timeout = "10"
single_transaction = "on"
;search_path = ""

; when you have charset issues, enable force_encoding so the connection will be
; made with the charset indicated in jelix config
;force_encoding = on

; with the following parameter, you can specify a table prefix which will be
; applied to DAOs automatically. For manual jDb requests, please use method
; jDbConnection::prefixTable().
;table_prefix =

; Example for pdo :
;driver=pdo
;dsn=mysql:host=localhost;dbname=test
;user=
;password=


[jcache]

; name of the default profil to use for cache
default=myapp


[jcache:myapp]
; disable or enable cache for this profile
enabled=1
; driver type (file, db, memcached)
driver=file
; TTL used (0 means no expire)
ttl=0


; Automatic cleaning configuration (not necessary with memcached)
;   0 means disabled
;   1 means systematic cache cleaning of expired data (at each set or add call)
;   greater values mean less frequent cleaning
;automatic_cleaning_factor = 0

; Parameters for file driver :

; directory where to put the cache files (optional default 'JELIX_APP_TEMP_PATH/cache/')
cache_dir=
; enable / disable locking file
file_locking=1
; directory level. Set the directory structure level. 0 means "no directory structure", 1 means "one level of directory", 2 means "two levels"...
directory_level=0
; umask for directory structure (default jelix one : 0775)
directory_umask=
; prefix for cache files (default 'jelix_cache')
file_name_prefix=
; umask for cache files (default jelix one: 0664)
cache_file_umask=

; Parameters for db driver :

; dao used (default 'jelix~jcache')
;dao = ""
; dbprofil (optional)
;dbprofile = ""


; Parameters for memcached driver :

; Memcached servers.
; Can be a list e.g
;servers = memcache_host1:11211,memcache_host2:11211,memcache_host3:11211 i.e HOST_NAME:PORT
;servers =

[jcache:qgisprojects]
enabled=1
driver=file
ttl=0

; Postgresql to store users authenticated by  Active Directory (LDAP)
;[jdb:__common__]
[jdb:pgldapdao]
driver = "pgsql"
database = "lizmap"
host = "localhost"
port = "5678"
user = "lizmap"
password = "***********"
;service =
persistent = "on"
force_encoding = on
timeout = "10"
single_transaction = "on"
;search_path = ""

;[jdb:pgldapdao]

;[jdb:lizlog]

; Active Directory authentication
[ldap:pgldap]
hostname=comune.spoleto.local
port=389
adminUserDn="CN=lizmap binduser,CN=Users,DC=comune,DC=spoleto,DC=local"
;adminUserDn="lizmap@comune.spoleto.local"
adminPassword="******************"

---

authldap.coord.ini.php

;<?php die(''); ?>
;for security reasons , don't remove or modify the first line

;============= Main parameters

; driver name : "ldap", "Db", "Class" or "LDS" (respect the case of characters)
driver = "ldapdao"

;============ Parameters for the plugin
; session variable name
session_name = "JELIX_USER"

; Says if there is a check on the ip address : verify if the ip
; is the same when the user has been connected
secure_with_ip = 0

;Timeout. After the given time (in minutes) without activity, the user is disconnected.
; If the value is 0 : no timeout
timeout = 0

; If the value is "on", the user must be authentificated for all actions, except those
; for which a plugin parameter  auth.required is false
; If the value is "off", the authentification is not required for all actions, except those
; for which a plugin parameter  auth.required is true
auth_required = off

; What to do if an authentification is required but the user is not authentificated
; 1 = generate an error. This value should be set for web services (xmlrpc, jsonrpc...)
; 2 = redirect to an action
on_error = 2

; locale key for the error message when on_error=1
error_message = "jauth~autherror.notlogged"

; action to execute on a missing authentification when on_error=2
on_error_action = "jauth~login:form"

; action to execute when a bad ip is checked with secure_with_ip=1 and on_error=2
bad_ip_action = "jauth~login:out"


;=========== Parameters for jauth module

; number of second to wait after a bad authentification
on_error_sleep = 0

; action to redirect after the login
after_login = "jauth~login:form"

; action to redirect after a logout
after_logout = "jauth~login:form"

; says if after_login can be overloaded by a "auth_url_return" parameter in the url/form for the login
enable_after_login_override = on

; says if after_logout can be overloaded by a "auth_url_return" parameter in the url/form for the login
enable_after_logout_override = on

;============ Parameters for the persistance of the authentification

; enable the persistance of the authentification between two sessions
persistant_enable=on

; the name of the cookie which is used to store data for the authentification
persistant_cookie_name=jelixAuthentificationCookie

; duration of the validity of the cookie (in days). default is 1 day.
persistant_duration = 1

;=========== parameters for password hashing

; method of the hash. 0 or "" means old hashing behavior of jAuth
; (using password_* parameters in drivers ).
; Prefer to choose 1, which is the default hash method (bcrypt).
password_hash_method = 1

; options for the hash method. list of "name:value" separated by a ";"
password_hash_options =

;=========== Parameters for drivers

[ldapdao]

compatiblewithdb = on

; name of the dao to get user data
dao = "jauthdb~jelixuser"

; profile to use for jDb
;profile = "jauth"
profile = "pgldapdao"

; profile to use for ldap
;ldapprofile = ""
ldapprofile = "pgldap"

; ldap needs clear password to connect, this is useless for our plugin
; except for the admin user.
; even if password_hash_method is activated, we set it to allow
; password storage migration
; @deprecated
password_crypt_function = sha1

; name of the form for the jauthdb_admin module
form = "jauthdb_admin~jelixuser"

; path of the directory where to store files uploaded by the form (jauthdb_admin module)
; should be related to the var directory of the application
uploadsDirectory= ""

;--- ldap parameters

; this is the jelix user that have admin rights. It will not be verified in the
; ldap
jelixAdminLogin="admin"

; base dn to search users. Used to search a user using the filter from searchUserFilter
; example for Active Directory: "ou=ADAM users,o=Microsoft,c=US", or "OU=Town,DC=my-town,DC=com"
;searchUserBaseDN="dc=XY,dc=fr"
searchUserBaseDN="OU=Comune di Spoleto,DC=comune,DC=spoleto,DC=local"

; filter to get user information, with the given login name
; example for Active Directory: "(sAMAccountName=%%LOGIN%%)"
;searchUserFilter="(&(objectClass=posixAccount)(uid=%%LOGIN%%))"
searchUserFilter="(sAMAccountName=%%LOGIN%%)"
; it can be a list:
;searchUserFilter[]=...
;searchUserFilter[]=...

; the dn to bind the user to login.
; The value can contain a `?` that will be replaced by the corresponding
; attribute value readed from the result of searchUserFilter.
; Or it can contain  `%%LOGIN%%`, replaced by the given login
; Or it can contain only an attribute name, starting with a `$`: the
; attribute should then contain a full DN.
;bindUserDN="uid=%?%,ou=users,dc=XY,dc=fr"
;bindUserDN="$dn"
bindUserDN="%%LOGIN%%@comune.spoleto.local"
;It can be a list of DN template:
;bindUserDN[]= ...
;bindUserDN[]= ...

; attributes to retrieve for a user
; for dao mapping: "ldap attribute:dao attribute"
; ex: "uid:login,givenName:firstname,mail:email" : uid goes into the login property,
; ldap attribute givenName goes to the property firstname etc..
; example for Active Directory: "cn,distinguishedName,name"
; or "sAMAccountName:login,givenName:firstname,sn:lastname,mail:email,distinguishedName,name,dn"
;searchAttributes="uid:login,givenName:firstname,sn:lastname,mail:email"
searchAttributes="sAMAccountName:login,givenName:firstname,sn:lastname,mail:email,distinguishedName,name,dn:dn"

; search ldap filter to retrieve groups of a user.
; The user will be assign to jAcl2 groups having the same name of ldap groups.
; Leave empty if you don't want this synchronisation between jAcl2 groups and
; ldap groups.
; !!! IMPORTANT !!! : if searchGroupFilter is not empty,
; the plugin will remove the user from all existing jelix groups
; and only keep the relation between the user and the group retrieved from LDAP
;searchGroupFilter="(&(objectClass=posixGroup)(cn=XYZ*)(memberUid=%%LOGIN%%))"
;searchGroupFilter=
searchGroupFilter="(&(objectClass=group)(CN=GeoPortale*)(member:1.2.840.113556.1.4.1941:=%%distinguishedName%%))"

; the property in the ldap entry corresponding to a group, that indicate the
; the group name
searchGroupProperty="cn"

; base dn to search groups. Used to search a group using the filter from searchGroupFilter
;searchGroupBaseDN=
searchGroupBaseDN="CN=Users,DC=comune,DC=spoleto,DC=local"
------------------------