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"
------------------------

About this issue

  • Original URL
  • State: closed
  • Created 6 years ago
  • Comments: 18

Most upvoted comments

Eureka!!! I realized that the 3.1.6 version of lizmap was released and I downloaded installed and configured it from scratch and to my surprise it works everything, both the Active Directory authentication and the saving of the login data and the acl in postgres, as well also logs. Now I will try to count as much as I can to document my lizmap integration solution with Microsoft Active Directory and Postgresql.