[Bug]: Filtering not working in Lizmap when layer authenticated against a qGIS auth config stored in qgis-auth.db

[cfsgarcia]

What is the bug? (in English)

It looks like when using an authentication config set up in qGIS to authenticate a layer against a Postgres instance, Lizmap won't be able to perform filtering actions through the web client.
As if Jelix was expecting to retrieve user/password stored within .qgs file.
In the lizmap errors.log, traces say Jelix failed connecting to Postgres basically: failed: fe_sendauth: no password supplied

Steps to reproduce the issue

1. Create a qgis project:
   -add a postis layer for which the authentication is 'Basic', passing on the postgres instance user/password.
   -add the same postgis layer but this time the authentication is done against a predefined authentication config (Settings > Options > Authentication > Add new authentication config...)

2. Enable WFS capability for both layers
   Lizmap plugin, in Layers enable Popup -> Automatic for both layers
   Publish the project.

3. Configure qgis-auth.db on server side
   QGIS_AUTH_DB_DIR_PATH="/home/qgis/qgisserverdb/"
   QGIS_AUTH_PASSWORD_FILE="/home/qgis/qgisserverdb/master_pwd.txt"

4. In Lizmap web client, both layers are shown
   Click a feature of the first layer to display the popup, click Filter button within the popup window > it does filter out ok.
   Click a feature of the second layer to display the popup, click Filter button within the popup window > a 500 (Internal jelix error) is thrown.

Versions, safeguards, check summary etc

GIS server: QGIS FCGI server - QGIS version 3.34.6-Prizren
Web server: NGINX & PHP 8.2
Lizmap Web Client: 3.7.7
Plugins:
lizmap_server v2.9.1
wfsOutputExtension v1.8.0
atlasprint v3.3.2

Check Lizmap plugin

• I have done the step just before in the Lizmap QGIS desktop plugin before opening this ticket. Otherwise, my ticket is not considered valid and might get closed.

Operating system

Ubuntu 22.04.2 LTS

Browsers

Chrome

Browsers version

124.0.6367.208 (Official Build) (64-bit)

Relevant log output

2024-05-24 12:14:30     [2]     pg_connect(): Unable to connect to PostgreSQL server: connection to server at "_my_postgis_instance_" (10.0.0.57), port 5432 failed: fe_sendauth: no password supplied     /var/www/lizmap-web-client-3.7.x/lizmap/vendor/jelix/jelix/lib/jelix/plugins/db/pgsql/pgsql.dbconnection.php    168
2024-05-24 12:14:30     [402]   error during the connection _my_postgis_instance_    /var/www/lizmap-web-client-3.7.x/lizmap/vendor/jelix/jelix/lib/jelix/plugins/db/pgsql/pgsql.dbconnection.php    184

[Gustry]

Please follow the bug template with the step in the Lizmap plugin

You will hit many issues with the QGIS Auth DB, as LWC is expecting to make SQL requests straight on the database ... (without using QGIS Server, which only QGIS Server can open the database with this authDB)

[cfsgarcia]

If we set the instance login credentials to profiles.ini.php and then tweak pgsql.dbconnection.php to catch authcfg property and apply the login credentials accordingly, it works flawlessly.
I've applied the workaround below to enable authcfg in lizmap:

-> lizmap-web-client-3.7.8/lizmap/var/config/profiles.ini.php

;qgis authentication config mirroring the one set in authentication database
[jdb:pglayer]
driver="pgsql"
database="my_database"
host="my_database_instance"
port=5432
user="admin_account"
password="admin_account_password"

-> lizmap-web-client-3.7.8/lizmap/modules/lizmap/classes/qgisVectorLayer.class.php
line 211: add authcfg property to the list of properties to retrieve while parsing qgis project file.

$parameters = array(
    'dbname', 'service', 'host', 'port', 'user', 'password',
    'sslmode', 'authcfg', 'key', ....

-> lizmap-web-client-3.7.8/lizmap/modules/lizmap/classes/qgisVectorLayerDatasource.class.php
line 25:

/**
 * @var array Regexes used to get datasource parameters
 */
protected $datasourceRegexes = array(
    'dbname' => "dbname='?([^ ']+)'? ",
    'service' => "service='?([^ ']+)'? ",
    'host' => "host='?([^ ']+)'? port=",
    'port' => 'port=([0-9]+) ',
    'user' => "user='?([^ ']+)'? ",
    'password' => "password='?([^ ']+)'? ",
    'sslmode' => "sslmode='?([^ ']+)'? ",
    'authcfg' => "authcfg='?([^ ']+)'? ",
    'key' => "key='?([^ ']+)'? ",

-> lizmap-web-client-3.7.8/lizmap/vendor/jelix/jelix/lib/jelix/plugins/db/pgsql/pgsql.dbconnection.php
line 144: check if authcfg property is found in qgis project file for the current layer; if so apply login credentials found in /lizmap/var/config/profiles.ini.php accordingly:

// we do isset instead of equality test against an empty string, to allow to specify
// that we want to use configuration set in environment variables
if (isset($this->profile['authcfg']) && $this->profile['authcfg']) {
 $ini = new \Jelix\IniFile\IniModifier(jApp::varConfigPath('profiles.ini.php'));
$profiles = $ini->getSectionList();
foreach($profiles as $profile) {
if ($profile == 'jdb:'.$this->profile['authcfg']) {
		 $options = $ini->getValues($profile);
		 $str .= ' user=\''.$options["user"].'\'';
		 $str .= ' password=\''.$options["password"].'\'';
		 break;
}
}
}
else {
	if (isset($this->profile['user'])) {
	    $str .= ' user=\''.$this->profile['user'].'\'';
	}

	if (isset($this->profile['password'])) {
	    $str .= ' password=\''.$this->profile['password'].'\'';
	}
}

[Gustry]

Nice to know. Do you think you can provide a pull request ?
Just a question, what would be the difference with using a PG Service file ?

[gioman]

Nice to know. Do you think you can provide a pull request ?

@cfsgarcia agree, this should go into LMWC code.

[github-actions]

This issue is missing some feedbacks. 👻
Please have a look to the discussion, thanks. 🦎

[gioman]

For tickets closed for missing feedback, can it be used a different label other than "not planned"? It is not really what is going on: a ticket can be perfectly valid and just lacking feedback, "not planned" does not seems right, for both devs and users.

[Gustry]

I agree about "not planned", but IMHO, both options are not right according to me. The other option is "completed", which is not true as well. But I don't really care about what is the final status.

Anyway, this ticket is still valid and a PR would be welcome.