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 to either /etc/pg_service.conf or another agreed-upon 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, indicate it using PGSERVICEFILE. Either way, you can then specify the name of the service in a connection string, such as in the following example:
psql "service=dbservice1=cookbook user=gciolli"
The service can also be set using an environment variable named PGSERVICE.
How it works…
The connection service file can also be used to specify the user, although that means that the database username will be shared.
The pg_service.conf and .pgpass files can work together, or you can use just one of the two. 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 case, it seems best to keep things separate and confine passwords to .pgpass.
There’s more...
This feature applies to libpq connections only, so it does not apply to clients using other libraries, such as Java database connectivity (JDBC).