Using a connection service file

As the number of connection options grows, you may want to consider using a connection service file.

The connection service file allows you to give a single name to a set of connection parameters. This can be accessed centrally to avoid the need for individual users to know the host and port of the database, and it is more resistant to future change.

You can set up a system-wide file as well as individual per-user files. The default file paths for these files are /etc/pg_service.conf and ~/.pg_service.conf respectively.

A system-wide connection file controls service names for all users from a single place, while a per-user file applies only to that particular user. Keep in mind that the per-user file overrides the system-wide file: if a service is defined in both files, then the definition in the per-user file will prevail.

How to do it…

First, create a file named pg_service.conf with the following content:

[dbservice1]
host=postgres1
port=5432
dbname=postgres

You can then copy it either to /etc/pg_service.conf or another agreed central location. You can then set the PGSYSCONFDIR environment variable to that directory location.

Alternatively, you can copy it to ~/.pg_service.conf. If you want to use a different name, set PGSERVICEFILE. Either way, you can then specify a connection string like the following:

service=dbservice1 user=sriggs

The service can also be set using an environment variable named PGSERVICE.

How it works…

This feature applies to libpq connections only, so it does not apply to JDBC.

The connection service file can also be used to specify the user, though that would mean that the username would be shared.

The pg_service.conf and .pgpass files can work together, or you can use just one of the two as you choose. Note that the pg_service.conf file is shared, so it is not a suitable place for passwords. The per-user connection service file is not shared, but in any cases, it seems best to keep things separate and confine passwords to .pgpass.