{"id":14415,"date":"2024-05-28T15:18:00","date_gmt":"2024-05-28T13:18:00","guid":{"rendered":"https:\/\/www.opengis.ch\/?p=14415"},"modified":"2025-08-04T15:19:51","modified_gmt":"2025-08-04T13:19:51","slug":"the-postgresql-connection-service-file-and-why-we-love-it","status":"publish","type":"post","link":"https:\/\/www.opengis.ch\/fr\/2024\/05\/28\/the-postgresql-connection-service-file-and-why-we-love-it\/","title":{"rendered":"The PostgreSQL Connection Service File and Why We Love It"},"content":{"rendered":"\n

The PostgreSQL Connection Service File pg_service.conf<\/code> is nothing new. It has existed for quite some time and maybe you have already used it sometimes too. But not only the new QGIS plugin PG service parser<\/a> is a reason to write about our love for this file, as well we generally think it\u2019s time to show you how it can be used for really cool things.<\/em><\/strong><\/p>\n\n\n\n

What is the Connection Service File?<\/strong><\/h2>\n\n\n\n

The Connection Service File allows you to save connection settings for each so-called \u201cservice\u201d locally.<\/p>\n\n\n\n

So when you have a database called gis<\/code> on a local PostgreSQL with port 5432<\/code> and username\/password is docker<\/code>\/docker<\/code> you can store this as a service called my-local-gis<\/code>.<\/p>\n\n\n\n

# Local GIS Database for Testing purposes\n<\/code><\/pre>\n\n\n[my-local-gis]\n\n\n\n
host=localhost port=5432 dbname=gis user=docker password=docker<\/p>\n\n\n\n
This Connection Service File is called pg_service.conf<\/code> and is by client applications (such as psql<\/a> or QGIS<\/a>) generally found directly in the user directory. In Windows it is then found in the user\u2019s application directory postgresql.pg_service.conf<\/code>. And in Linux it is by default located directly in the user\u2019s directory ~\/.pg_service.conf<\/code>. <\/p>\n\n\n\n
But it doesn\u2019t necessarily have to be there. The file can be anywhere on the system (or on a network drive) as long as you set the environment variable PGSERVICEFILE<\/code> accordingly:<\/p>\n\n\n\n
export PGSERVICEFILE=\/home\/dave\/connectionfiles\/pg_service.conf <\/code><\/pre>\n\n\n\n
Once you have done this, the client applications will search there first \u2013 and find it.<\/p>\n\n\n\n
If the above are not set, there is also another environment variable PGSYSCONFDIR<\/code> which is a folder which is searched for the file pg_service.conf<\/code>.<\/p>\n\n\n\n
Once you have this, the service name can be used in the client application. That means in psql<\/a> it would look like this:<\/p>\n\n\n\n
~$ psql service=my-local-gis\npsql (14.11 (Ubuntu 14.11-0ubuntu0.22.04.1), server 14.5 (Debian 14.5-1.pgdg110+1))\nSSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)\nType \"help\" for help.\n\ngis=#<\/code><\/pre>\n\n\n\n
And in QGIS like this:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
If you then add a layer in QGIS, only the name of the service is written in the project file. Neither the connection parameters nor username\/password are saved. In addition to the security aspect, this has various advantages, more on this below.<\/p>\n\n\n\n
But you don\u2019t have to pass all of these parameters to a service. If you only pass parts of them (e.g. without the database), then you have to pass them when the connection is called:<\/p>\n\n\n\n
$psql \"service=my-local-gis dbname=gis\"\npsql (14.11 (Ubuntu 14.11-0ubuntu0.22.04.1), server 14.5 (Debian 14.5-1.pgdg110+1))\nSSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)\nType \"help\" for help.\n\ngis=#<\/code><\/pre>\n\n\n\n
You can also override parameters. If you have a database gis<\/code> configured in the service, but you want to connect the database web, you can specify the service and explicit the database:<\/p>\n\n\n\n
$psql \"service=my-local-gis dbname=web\"\npsql (14.11 (Ubuntu 14.11-0ubuntu0.22.04.1), server 14.5 (Debian 14.5-1.pgdg110+1))\nSSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)\nType \"help\" for help.\n\nweb=#<\/code><\/pre>\n\n\n\n
Of course the same applies to QGIS.<\/p>\n\n\n\n
And regarding the environment variables mentioned, you can also set a standard service.<\/p>\n\n\n\n
export PGSERVICE=my-local-gis<\/code><\/pre>\n\n\n\n
Particularly pleasant in daily work with always the same database.<\/p>\n\n\n\n
$ psql\npsql (14.11 (Ubuntu 14.11-0ubuntu0.22.04.1), server 14.5 (Debian 14.5-1.pgdg110+1))\nSSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)\nType \"help\" for help.\n\ngis=#<\/code><\/pre>\n\n\n\n
And why is it particularly cool?<\/strong><\/h2>\n\n\n\n
There are several reasons why such a file is useful:<\/p>\n\n\n\n