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