use of pg_service.conf

[rastermanden]

I have a project based on postgreSQL with a pretty complex datamodel with triggers therefore i'm editing directly into the database from the Input app (connected to VPN). Works great.

I want to get rid of usernames and passswords in my QGIS project files and .qlr files and therefor i'm using pg_serice.conf file.

Is it possible to use a pg_service.conf file with input ? If yes, where should I put the file on the mobile unit ?

[saberraz]

hi @rastermanden

For PG database, we highly recommend using DB-Sync (https://github.com/MerginMaps/mergin-db-sync). In summary:

• the app can work offline
• you do not need to expose your PG connections
• works outside your LAN

[rastermanden]

Thank you for the answer. i'm aware of mergin-db-sync. It is really nice. Unfortunately I didn't have success with it on the complicated data model i am using.

The problem for my setup is that it is a rather complicated data model with lots of triggers and writing to tables is done through interface views using INSTEAD OF triggers. There is also versioning built into the data model. I haven't had success replicating the complex schema to GeoPackage files (using --init-from-db). Hence I wanted to try using direct read/write while on LAN/VPN.

Maybe I should give it another try!

[saberraz]

In our experience, it is best to keep the data collection schema(s) as a "storage" only. If you have complicated logic, triggers, etc, it is best to off-load it to another schema/db or even better using a QGIS plugin for doing the heavy lifting.

[rastermanden]

Thanks.

Unfortunately, for this particular project it is not possible to change the database setup.

In this case, it would be nice to be able to use a pg_service file on the mobile units

[rastermanden]

I can put the file on the mobile: /Android/data/uk.co.lutraconsulting/files

and then set the environment variable: PGSERVICE=/Android/data/uk.co.lutraconsulting/files/pg_service.conf Or PGSERVICE env could default to this file location or PGSYSCONFDIR could default point to this directory

Is it a possibility to use environment variables on Mergin (Input) ?

[saberraz]

The mobile app will take into account contents of pg_service.conf file located in the app's directory (such as /Android/data/uk.co.lutranconsulting/files or similar), to allow connection to PostgreSQL servers when they are specified as a service in pg_service.conf. This configuration file would be uploaded to the phone/tablet manually through cable via a file manager. Only Android support is planned/needed. The rationale is that users will be able to access PostgreSQL databases without having their usernames and passwords specified in QGIS project files (usernames and passwords would be stored in pg_service.conf).

[Uffeshub]

We payed for the development of this feature. UnfortunateIy I can´t make it work on my android phone (merginmaps v2.5.0). First I tested the connection on my PC in QGIS. Everything works as expected. I placed the .pg_service.conf file here on my phone: \Android\data\uk.co.lutraconsulting\files. I opened merginmaps app and the project. The project fails to connect to service "geofa_park_gladsaxe" (postGIS). I just send the diagnostic log to you. What am I doing wrong / what is wrong? Best Regards Uffe Schougaard, Gladsaxe municipality

[PeterPetrik]

Hi @Uffeshub the feature was tested on project delivery, I assume there is some problem with the file or most probably its location. Can you make sure the location is one described here https://merginmaps.com/docs/gis/supported_formats/#postgresql-postgis . If yes, please can you follow the discussion as support query on [email protected] ? Thanks