Remote Access to postgresql database

[FabianStoehr]

Hi there, I am completely new and apologize if I ask something that probably has been solved many times before... but I just couldn't find anything similar in all Issues here (even the closed ones). I tired all of google + chat gpt and really tried following the manual.

I have my own remote server and installed the whole qwc_docker setup. So far I achieved many things such as the client running and publishing the demo data. You can see for yourself under fabian-stoehr.de (it doesn't work with www. yet). I want to use the running postgresql server and more databases there where I can set up tables with geodata which are used in qgis projects which then also are stored in the very same database. I managed to remotely connect to the postgresql server via port 5439 and did exacly that. I created a test qgis project and included data which is all stored in a new "projects" database on the postgresql server running inside the docker container and saved the project there too. As described in the manual, I added

[qgisprojects] host=qwc-postgis port=5432 dbname=projects user=postgres password=XXXX sslmode=disable

in the qwc_docker/pg_service.conf file.

I also added my project in the themesConfig.json:

"themes": { "items": [ { "id": "Demo", "title": "Demo", "url": "/ows/qwc_demo", "attribution": "Demo attribution", "attributionUrl": "https://127.0.0.1/", "backgroundLayers": [ { "name": "bluemarble", "printLayer": "bluemarble_bg", "visibility": true }, { "name": "mapnik", "printLayer": "osm_bg" } ], "searchProviders": [ "coordinates", "nominatim", { "provider": "solr", "default": [] } ], "mapCrs": "EPSG:3857", "additionalMouseCrs": [], "extent": [-1000000, 4000000, 3000000, 8000000], "skipEmptyFeatureAttributes": true, "printResolutions": [300], "thumbnail": "default.jpg" }, { "name": "Test_Projekt", "title": "Test Project", "url": "/ows/pg/test_gebiet/Test_Projekt", "mapCrs": "EPSG:25832", "thumbnail": "images/thumbnails/my_project.png", "backgroundLayers": [], "layers": [], "print": {} } ] },

But the problem is that when I want to generate the service configuration file:

INFO: Reading theme /ows/pg/test_gebiet/Test_Projekt CRITICAL: Could not get WMS GetProjectSettings from http://qwc-qgis-server/ows/pg/test_gebiet/Test_Projekt: b'\nLayer(s) not valid\n'

I guess this is because the datasource of the layers points to the server and the port 5439 but the qgis server only knows the postgres database running inside the container with the port 5432 and hence doesn't find the layers.

Apologies again for such a long text. I just created my github account and started the whole process of qwc docker only 5 days ago. I watched all of your fossgis tutorials and hope to find more.

thank you in advance!

Fabian

[manisandro]

The error message indicates that the project stored in the DB could be read successfully, however a layer within the project failed to load. Check the log of the qgis-server container (docker-compose logs qwc-qgis-server) and you should see which one.

[FabianStoehr]

Thank you for the quick response @manisandro

I checked the logs and identified the issue: qwc-qgis-server-1 | 10:25:43 WARNING PostGIS[96]: Connection to database failed qwc-qgis-server-1 | connection to server at "mydomain.de" (XXX.XX.XXX.XXX), port 5439 failed: fe_sendauth: no password supplied

The project sees the datasource as a remote datasource even tho the data is stored in the same database on the same postgres server as the qgis_projects database where the project is stored which is inside the container localhost, port 5432 but remotely mydomain.de port 5439. When I open the qgis project remotely, I need to log in with the user that i specifically created for that projects database. But when qgis-server wants to access the files, there is no password automatically supplied. I'll try to find a way around this.

[manisandro]

You can use a pg service definition, and connect through the service name in qgis, and then make sure that your pg_service.conf contains the service definition with the credentials.

[FabianStoehr]

Super!! I understood the concept finally and it works! Thank you very much @manisandro