Editing and PostgreSQL environment vars/.pgpass file

[shermp]

Description

I am attempting to get layer editing working for Lizmap WITHOUT having the Postgres credentials stored in the qgs file. I've tried setting PGUSER/PGPASSWORD environment variables. I've tried setting PGPASSFILE. I've tried using ~/.pgpass.

Looking at the code, a service would work... except that lizmap assumes the service will also contain the DB name, so never sets it, making a "generic" service unusable.

For everything I've attempted, I've gotten a very generic [402] error during the connection pgdb /www/lib/jelix/plugins/db/pgsql/pgsql.dbconnection.php 153 error from the log, which tells me very little.

I'm running a docker stack with PostGIS, py-qgis-server, lizmap 3.3.x, nginx. I've played with permissions. I've tried creating a user (for the pgpass file).

My last resort will be to hack lizmap/modules/lizmap/classes/qgisVectorLayer.class.php to read some env variables. But I really don't want to do this if there is any other way.

Thank you for your time.

Environment

• Lizmap version: 3liz/lizmap-web-client:3.3
• QGIS Server FULL version: 3liz/qgis-map-server:3.10
• QGIS Desktop FULL version: N/A
• OS: Ubuntu 18.04 with Docker
• Browser: N/A
• Lizmap admin information (users, logs…) stored into PostgreSQL

[mdouchin]

The way Lizmap parses the PostgreSQL layer datasource was fragile in versions <= 3.3.X. Service worked well with older QGIS ( 2.18 & 3.4). Not fully tested yet with 3.10.

In current master, and future 3.4.X, we have improved the datasource parsing, and added unit tests to cover some of the datasources. We did not dare to backport to 3.3.x branches, but you should be able to test the changes in your 3.3.x installation (do not try in production environment first !)

See: https://github.com/3liz/lizmap-web-client/pull/1706/files

A test should be added to cover the service both with and without database name. Could you provide here the full <datasource> of your layer (with service, no dbname and obviously no password) ?

[shermp]

Thanks for the quick reply! We're setting up a brand new system, so nothing to break in production yet 😄 Means I can play with different versions etc.

Just upgraded to the currently available 3.4 docker image (alpha 2 it appears to be). Editing still doesn't work with the current setup.

Here's the datasource for my test edit layer:

<datasource>dbname='db_name' service='service_name' sslmode=disable key='id' srid=2193 type=Polygon checkPrimaryKeyUnicity='1' table="public"."EditTest" (geom) sql=</datasource>

And the service file mounted in the docker container (as a config):

[service_name]
host=pgcontainer
port=5432
user=editinguser
password=editorpassword

(Yes, I know, .pgpass is preferred for passwords. But that requires a user account to exist, and pg_service.conf does not. I'm using the same file permissions as I would with the pgpass file to compensate)

As you can see, the service file does NOT have a database name set, that should come from the .qgs file. This setup works perfectly for QGIS server.

[shermp]

The following lines in lib/jelix/plugins/db/pgsql/pgsql.dbconnection.php makes me wonder if my desired setup will be possible at all:

if(isset($this->profile['service']) && $this->profile['service'] != ''){
     $str = 'service=\''.$this->profile['service'].'\''.$str;
 }
 else { ...

[mdouchin]

Indeed, we should investigate and see if jelix (PHP framework on which Lizmap Web Client is based) can use a database name when a service is given.

[mdouchin]

I will check the code, and I add @laurentj in the loop (creator of Jelix)

[shermp]

Thank you. From what I've read, I think libpq should support this. Whether the php pg_connect function does is another matter.

Alternatively, if we can't get the service to work in this manner, I do have another method I can use (it involves sed) for hostname handling, IF I can get lizmap/jelix/pg_connect/libpq to use the .pgpass file.

[mdouchin]

I will propose a PR ASAP for testing purpose

[mdouchin]

@shermp please test this PR #1723 (from master).

[shermp]

Any tips on building the docker image? Once I got a zip archive into the docker folder, it's now complaining about missing factory.manifest. What is this file, and where can I find it?

edit: might help to look at the Makefile...

[shermp]

Once I figured out the dependencies needed (composer, node), building the archive and a docker image was quick and painless. Having recently attempted to build another web mapping software, it was a breath of fresh air.

That PR works! No errors, and I can edit the layer successfully.

Thank you for looking into this, and I look forward to whatever permanent solution you settle on.

[mdouchin]

Thanks for testing. Il will do the necessary to have this change in 3.3.X branch (and obviously in master too). It will be available in next point release

[shermp]

Hi, I saw the change didn't make it into 3.3.9, any chance it will be in 3.3.10? I notice the PR isn't assigned a version number.

[mdouchin]

Sorry, I have not yet backported the bug fix. I will ASAP

[shermp]

Sorry, I have not yet backported the bug fix. I will ASAP

Thank you very much.

[mdouchin]

@shermp PR #1748 targets release_3_3, and will be in next 3.3.x point release if merged on time

[3liz-bot]

Hiya! This issue has gone quiet. 👻 We get a few questions in the bug tracker, so we currently slowly closing issues. If we missed this issue or if you want to keep it open, please reply here. As a friendly reminder: the best way to see this issue, or any other, fixed is to open a Pull Request. Thanks for being a part of the Lizmap community! 🦎

[TeoGoddet]

Hi I have a similar problem where my datasource is dbname='gaia_prod' host=xxxx.blek.ch port=5432 sslmode=require key='id' estimatedmetadata=true srid=2056 type=PolygonZ checkPrimaryKeyUnicity='1' table="2022"."scene" (geom)

When using those layer, the qgis client prompt the users for their credentials. Works OK

QGIS server is given the credentials using env var PGUSER and PGPASSWORD. Works OK

Lizmap edit doesnt work. Impossible de se connecter sur xxx.blek.ch (mauvais host, login ou mot de passe ?) /www/lib/jelix/plugins/db/pgsql/pgsql.dbconnection.php 166

Would you accept a pr to take username and password from env var if env is set but no username or password ?

The fix need to be done there : https://github.com/3liz/lizmap-web-client/blob/2932d421eacb58ff63b8d3eefed6e1201de97c3a/lizmap/modules/lizmap/classes/qgisVectorLayer.class.php#L251