One of the numerous features offered by QGIS is the ability to save our projects. We can store all the specific configurations, layers, styles, analyses, and other aspects in a file, allowing us to return to that point at any time. This is particularly useful when dealing with complex project organization. This functionality is also very helpful for collaborative work, as we can exchange projects with other team members.
However, when creating a project for sharing, certain aspects need to be taken into account, such as including connections to a database. In this article, we will discuss some interesting key points for accomplishing this.
Database Connections in QGIS Projects: The Recommended Approach
Most of the time, when creating a QGIS project with PostgreSQL/PostGIS layers, the data connection details are directly written, including storing the password in plain text within the project.
When done this way, within the project file created by QGIS with .qgz/.qgs extension, there will be a line like this:
<datasource>dbname='test' host=localhost port=5439 user='test_user' password='test_user' sslmode=disable key='id' srid=4326 type=Point checkPrimaryKeyUnicity='0' table="public"."cities" (geom)</datasource>This approach creates several problems when it comes to deploying the project or sharing it with others:
- Usernames and passwords need to be shared among several people, making it impossible to establish logical individual or group permissions.
- If we don’t hardcode the username and password, every person using the project will have to enter the data each time.
- Passwords are stored in plain text.
- If the server needs to be changed, for example, from a testing environment to a production one, the project needs to be modified.
- …
Although there are some ways to improve this situation, such as using .pgpass, using the Authentication Manager, or using custom embedded scripts within the project, they are not the best options and have issues.
The best approach, or at least the one we can recommend based on our experience, is to associate the connection details with an identifier (a pre-agreed name) and establish the connection in QGIS using the same identifier.
PostgreSQL allows achieving this through the Connection Service File. It is a text file located in Linux at ~/.pg_service.conf and in Windows at %APPDATA%\postgresql\.pg_service.conf, and it would look like this:
[test]
host=localhost
port=5439
dbname=test
user=test_user
password=test_user
sslmode=disable
# Avoid hardcode the password in prod
[production]
host=my.production.server
port=5432
dbname=my_project
user=my_user
In QGIS, the connection would be established like this:
Here, you only need to fill in the connection name and the service name, which will be our identifier (the text within the brackets) in the .pg_service.conf file. This is explained in more detail in the QGIS documentation.
The advantages of using connections with an identifier are clear and solve the aforementioned problems. One person can prepare the project locally, and then, when moving to production, they only need to send the project and indicate the connection name and parameters that the rest of the team should fill in the .pg_service.conf file.
Furthermore, since this file is directly managed by libpq, the standard PostgreSQL client library used by 99% of applications, this strategy will work with any other client that needs to use that database connection, not just QGIS.
For example, in console commands:
ogrinfo PG:"service=test" cities
psql service=test
or in QGIS scripts:
layer = QgsVectorLayer("service=test table=cities (geom)", "Cities", "postgres")
QgsProject.instance().addMapLayer(layer)
The Service File allows defining numerous parameters, and few of them are mandatory. For instance, if we don’t define the password, any application using libpq will execute a normal workflow to try to discover it: searching in the PGPASSWORD variable, checking the existence of the .pgpass file, or directly prompting us to provide it.