[Bug]: Requests for data in GeoJSON format (i.e. attribute table) do not work if PostGIS layer comes from a connection set up with .pg_service.conf #4674

gioman · 2024-08-01T17:44:22Z

What is the bug? (in English)

This was not fun to troubleshoot (no entries whatsoever in Apache log, Lizmap log, PHP log or QGIS Server log):

If in a project you have a postgis layer that has been added from a connection set up with .pg_service rather than using clear text username and password then there are functionalities that do not work in Lizmap, specifically what is not working is exporting such layer in GeoJSON format (other formats work), this means that also opening the attribute table shows nothing.

On the dev console of FF the error is a generic 500 which does not leaves any trace in system logs.

Steps to reproduce the issue

publish a project with a postgis layer added from a connection using .pg_service
you must ensure that your QGIS Server instance also is using such file, i.e.

SetEnv PGSERVICEFILE /mnt/qgis_authdb/.pg_service.conf

add that layer to WFS capabilities and in lizmap plugin add it to the "attribute table" list
test the project > exporting as geojson and exploring the table are a no go

Versions, safeguards, check summary etc

Versions :

Lizmap Web Client : 3.6.14
Lizmap plugin : 4.3.16
QGIS Desktop : 3.34.9
QGIS Server : 3.34.9
Py-QGIS-Server : not used
QGIS Server plugin atlasprint : not found
QGIS Server plugin lizmap_server : 2.9.4
QGIS Server plugin wfsOutputExtension : 1.8.1

List of safeguards :

Mode : normal
Allow parent folder : yes
Number of parent : 2 folder(s)
Prevent other drive : yes
Prevent PG service : no
Prevent PG Auth DB : yes
Force PG user&pass : yes
Prevent ECW : yes

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

Browsers

Firefox, Chrome

Browsers version

Latest updates

Relevant log output

No response

The text was updated successfully, but these errors were encountered:

Gustry · 2024-08-01T19:47:53Z

All demos on https://demo.lizmap.com which have PostGIS data are using a PG Service for connection.

For instance Cats in NY. The attribute table and exporting to GeoJSON works.

You can download the cat project by using the button at the top or they are all on GitHub

So you have an URL and it gives a 500 ? You should see somewhere in your logs who raised this 500 error ? Is the error in QGIS Server, is QGIS server hit by the request ?
What are QGIS Server logs when doing this HTTP request ? You can turn off deleting temporary layers made by WfsOutputExtension https://github.com/3liz/qgis-wfsOutputExtension?tab=readme-ov-file#debug-on-production

gioman · 2024-08-02T07:55:24Z

All demos on https://demo.lizmap.com which have PostGIS data are using a PG Service for connection.

For instance Cats in NY. The attribute table and exporting to GeoJSON works.

@Gustry weird isn't it? But believe me, I spent the better part of an afternoon troubleshooting this, and the moment I replaced the layer with one from a connection using normal authentication all worked as expected. It took time to realize that because anything else worked fine, even exporting in other formats. Only requests made trough lizmap fail as making a similar request directly to qgis server works, i.e.

curl "https://***/lizmap/www/index.php/lizmap/service" -X POST -H "User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:128.0) Gecko/20100101 Firefox/128.0" -H "Accept: */*" -H "Accept-Language: en-US,en;q=0.5" -H "Accept-Encoding: gzip, deflate, br, zstd" -H "Content-Type: application/x-www-form-urlencoded;charset=UTF-8" -H "Origin: ***" -H "Sec-Fetch-Dest: empty" -H "Sec-Fetch-Mode: cors" -H "Sec-Fetch-Site: same-origin" -H "Connection: keep-alive" -H "Cookie: PHPSESSID=1sn332b0hus6d1m3oe3bjd3ies" -H "Priority: u=0" --data-raw "repository=obrasparticulares&project=obras_particulares&SERVICE=WFS&REQUEST=GetFeature&VERSION=1.0.0&OUTPUTFORMAT=GeoJSON&TYPENAME=caop_2023&GEOMETRYNAME=extent"
<!DOCTYPE html>
<html>
<head>
<title>Application Error</title>
</head>
<body>
<p>Error 500.
A technical error has occured. Sorry for this trouble.</p>
</body>

vs

curl "https://***/cgi-bin/qgis_mapserv.fcgi?map=/path/to/obras_particulares.qgs&SERVICE=WFS&VERSION=1.0.0&REQUEST=GetFeature&TYPENAME=caop_2023&OUTPUTFORMAT=GeoJSON&dl=1"
{"type": "FeatureCollection",
 "bbox": [ -8.7644, 39.9478, -8.4244, 40.1767],
 "features": [
  {"bbox":[-8.68006,40.126748,-8.614655,40.174142],"geometry":{"coordinates":[[[[-8.624966,40.14432],[-8.618902,40.138001],[-8.615059,40.134582],[-8.614655,40.133984],[-8.614907,40.133946],[-8.615364,40.133812],[-8.616168,40.133566],[-8.617836,40.133047],[-8.618675,40.132818],[-8.618803,40.1328],[-8.618983,40.132804],[-8.619136,40.132811],[-8.619139,40.132786],[-8.61915,40.132755],[-8.619175,40.132661],[-8.619255,40.132464],[-8.619257,40.132422],[-8.619286,40.132198],[-8.619335,40.132072],[-8.619356,40.132014],[-8.619565,40.131974],[-8.619843,40.131892],[-8.620129,40.13181],[-8.620447,40.131682],[-8.620714,40.131563],[-8.620936,40.131478],[-8.621015,40.131456],[-8.621045,40.131453],[-8.621372,40.131449],[-8.621667,40.131443],[-8.621994,40.131442],[-8.622033,40.131441],[-8.62211,40.131437],[-8.622153,40.131437],[-8.622464,40.131431],[-8.622803,40.131427],[-8.622979,40.131424],[-8.623015,40.131427],[-8.623055,40.131426],[-8.623351,40.131422],[-8.623441,40.131415],[-8.623758,40.131349],[-8.623804,40.131337],[-8.623949,40.131292],[-8.62398,40.131282],[-8.624186,40.131215],[-8.624225,40.131201],[-8.624265,40.13119],[-8.624531,40.131139],[-8.624572,40.131133],[-8.624612,40.13113],[-8.624922,40.131124],[-8.625219,40.13111],[-8.62553,40.131087],[-8.625614,40.131077],[-8.625778,40.131043],[-8.625819,40.131041],[-8.625857,40.131035],[-8.626163,40.131007],[-8.626468,40.130963],[-8.626769,40.130917],[-8.626817,40.130912],[-8.627152,40.130883],[-8.627483,40.130859],[-8.627781,40.130794],[-8.628076,40.130716],[-8.628374,40.130641],[-8.628662,40.130547],[-8.628952,40.130435],[-8.629216,40.130323],[-8.629241,40.130306],[-8.629257,40.130286],[-8.629297,40.130277],[-8.629409,40.130243],[-8.62....

while

curl "https://***/lizmap/www/index.php/lizmap/service" -X POST -H "User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:128.0) Gecko/20100101 Firefox/128.0" -H "Accept: */*" -H "Accept-Language: en-US,en;q=0.5" -H "Accept-Encoding: gzip, deflate, br, zstd" -H "Content-Type: application/x-www-form-urlencoded;charset=UTF-8" -H "Origin: ***" -H "Sec-Fetch-Dest: empty" -H "Sec-Fetch-Mode: cors" -H "Sec-Fetch-Site: same-origin" -H "Connection: keep-alive" -H "Cookie: PHPSESSID=1sn332b0hus6d1m3oe3bjd3ies" -H "Priority: u=0" --data-raw "repository=obrasparticulares&project=obras_particulares&SERVICE=WFS&REQUEST=GetFeature&VERSION=1.0.0&OUTPUTFORMAT=CSV&TYPENAME=caop_2023&GEOMETRYNAME=extent"
gml_id,gid,fid,dicofre,freguesia,municipio,distrito,area_ha,des_simpli
caop_2023.1,"1",1133,"061501",Alfarelos,Soure,Coimbra,1398.44,Alfarelos
caop_2023.2,"2",1134,"061504",Figueiró do Campo,Soure,Coimbra,1127,Figueiró do Campo
caop_2023.3,"3",1135,"061506",Granja do Ulmeiro,Soure,Coimbra,505.19,Granja do Ulmeiro
caop_2023.4,"4",1136,"061508",Samuel,Soure,Coimbra,3137.29,Samuel
caop_2023.5,"5",1137,"061509",Soure,Soure,Coimbra,9232.46,Soure
caop_2023.6,"6",1138,"061510",Tapéus,Soure,Coimbra,1386.87,Tapéus
caop_2023.7,"7",1139,"061511",Vila Nova de Anços,Soure,Coimbra,2057.63,Vila Nova de Anços
caop_2023.8,"8",1140,"061512",Vinha da Rainha,Soure,Coimbra,2038.29,Vinha da Rainha
caop_2023.9,"9",1141,"061513",União das freguesias de Degracias e Pombalinho,Soure,Coimbra,3947.39,Degracias e Pombalinho
caop_2023.10,"10",1142,"061514",União das freguesias de Gesteira e Brunhós,Soure,Coimbra,1675.51,Gesteira e Brunhós

Moreover, as said in the original description, there is no trace of this error in logs (all of them).

You can turn off deleting temporary layers made by WfsOutputExtension https://github.com/3liz/qgis-wfsOutputExtension?tab=readme-ov-file#debug-on-production

Is there any point doing this? GeoJson is not added by WfsOutputExtension and anyway no temp layers are generated as it fails before that, or did I got it wrong?

Gustry · 2024-08-02T08:23:00Z

Is there any point doing this? GeoJson is not added by WfsOutputExtension and anyway no temp layers are generated as it fails before that, or did I got it wrong?

Sorry, indeed GeoJSON is native !

Moreover, as said in the original description, there is no trace of this error in logs (all of them).

There must be, for sure. You should see the incoming HTTP request at least (not the error, but incoming request)

With the working request,

curl "https://***/lizmap/www/index.php/lizmap/service"

do you see the HTTP log on the server side about this request ?

gioman · 2024-08-02T10:29:15Z

You should see the incoming HTTP request at least (not the error, but incoming request)

82.154.59.116 - - [02/Aug/2024:10:21:46 +0000] "POST /lizmap/www/index.php/lizmap/service HTTP/1.1" 500 1070 "-" "Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:128.0) Gecko/20100101 Firefox/128.0"
127.0.0.1 - - [02/Aug/2024:10:21:46 +0000] "GET /cgi-bin/qgis_mapserv.fcgi?map=***obras%5Fparticulares%2Eqgs&outputformat=XMLSCHEMA&request=describefeaturetype&service=WFS&typename=caop%5F2023&version=1%2E0%2E0&Lizmap%5FUser=admin&Lizmap%5FUser%5FGroups=admins%2C%20users&Lizmap%5FOverride%5FFilter=1 HTTP/1.1" 200 5028 "-" "Lizmap"

and for some reason there is no trace of the 500 error in both apache and php error logs (yes, they are logging other errors).

github-actions · 2024-09-02T14:13:06Z

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

gioman · 2024-09-09T16:52:06Z

This should not have being closed automatically.

gioman · 2024-09-15T22:44:10Z

@Gustry can this be reopened please?

Gustry · 2024-09-16T14:47:03Z

Yes it can, but IMHO, you will have to debug more unfortunately.
I didn't reproduce this issue, and no one reported this issue from customers. I tried on the online project

https://demo.lizmap.com/lizmap/index.php/view/map?repository=features&project=observations

Did you try on the demo ? Can you install the demo on your server ? You have the INSTALL.md.

Maybe some content is causing issue in the GeoJSON ? a apostrophe or double apostrophe ?

Lizmap Web Client : 3.6.14
QGIS Desktop : 3.34.9

We do not have these two versions on our infrastructure. If customers want a QGIS 3.34, it needs minimum LWC 3.7.
Our demo server is on LWC 3.7. (I will make the switch to 3.8, I didn't realise).
Any chance to try LWC 3.8 ? Maybe the issue is with LWC 3.6 and QGIS 3.34 ?

gioman · 2024-09-17T11:35:05Z

@Gustry I'll check again, this time on 3.7.

gioman · 2024-09-17T11:35:16Z

and 3.8.

Gustry · 2024-09-17T12:30:54Z

If I were you, I would go straight to 3.8.X (3.8.1-pre in the meantime, or 3.8.1 soon).
3.8 looks better polished than 3.7

gioman added the bug label Aug 1, 2024

Gustry added the feedback label Aug 1, 2024

github-actions bot added the stale This ticket might be closed soon label Sep 2, 2024

github-actions bot closed this as not planned Won't fix, can't repro, duplicate, stale Sep 9, 2024

3liz-bot removed the stale This ticket might be closed soon label Sep 9, 2024

Gustry reopened this Sep 16, 2024

Gustry added the QGIS Server label Sep 16, 2024

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

[Bug]: Requests for data in GeoJSON format (i.e. attribute table) do not work if PostGIS layer comes from a connection set up with .pg_service.conf #4674

[Bug]: Requests for data in GeoJSON format (i.e. attribute table) do not work if PostGIS layer comes from a connection set up with .pg_service.conf #4674

gioman commented Aug 1, 2024 •

edited

Loading

Gustry commented Aug 1, 2024

gioman commented Aug 2, 2024

Gustry commented Aug 2, 2024

gioman commented Aug 2, 2024

github-actions bot commented Sep 2, 2024

gioman commented Sep 9, 2024

gioman commented Sep 15, 2024

Gustry commented Sep 16, 2024

gioman commented Sep 17, 2024

gioman commented Sep 17, 2024

Gustry commented Sep 17, 2024

[Bug]: Requests for data in GeoJSON format (i.e. attribute table) do not work if PostGIS layer comes from a connection set up with .pg_service.conf #4674

[Bug]: Requests for data in GeoJSON format (i.e. attribute table) do not work if PostGIS layer comes from a connection set up with .pg_service.conf #4674

Comments

gioman commented Aug 1, 2024 • edited Loading

What is the bug? (in English)

Steps to reproduce the issue

Versions, safeguards, check summary etc

Check Lizmap plugin

Operating system

Browsers

Browsers version

Relevant log output

Gustry commented Aug 1, 2024

gioman commented Aug 2, 2024

Gustry commented Aug 2, 2024

gioman commented Aug 2, 2024

github-actions bot commented Sep 2, 2024

gioman commented Sep 9, 2024

gioman commented Sep 15, 2024

Gustry commented Sep 16, 2024

gioman commented Sep 17, 2024

gioman commented Sep 17, 2024

Gustry commented Sep 17, 2024

gioman commented Aug 1, 2024 •

edited

Loading