Connecting using Service File

The connection service file also called just a service file, allows libpq connection parameters to be associated with a single service name. That service name can then be specified by a libpq connection, and the associated settings will be used.

libpq is the C application programmer’s interface to PostgreSQL. libpq is a set of library functions that allow client programs to pass queries to the PostgreSQL backend server and to receive the results of these queries.
libpq is also the underlying engine for several other PostgreSQL application interfaces, including those written for C++, Perl, Python, Tcl and ECPG.

Service file can have entries for many connections with each connection detail set headed by a connection name written in []. Either all connection parameters can be mentioned there or only some parameters can be mentioned there and the rest of the parameters can be given on the ‘psql’ connection prompt.

Service file can also be used in conjunction with the password file. The service file method works with libpq connections only, not JDBC.

Service file can be defined at many levels.

• User level – at the location ~/.pg_service.conf
• User level – at the location defined by environment variable PGSERVICEFILE
• System level – at the location `pg_config –sysconfdir`/pg_service.conf
• System level – in the directory specified by the environment variable PGSYSCONFDIR

Let’s deploy the system level pg_service.conf method to make a service-based connection. To achieve that, we need to do the following:

1. find the location of the pg_service.conf file as dictated by PostgreSQL installation
2. create the file if it doesn’t exist
3. give write access to all so that we can edit it
4. add the service details to the file.

echo `pg_config --sysconfdir`/pg_service.conf
sudo touch /etc/postgresql-common/pg_service.conf
sudo chmod 777 /etc/postgresql-common/pg_service.conf
sudo cat <<EOT>> /etc/postgresql-common/pg_service.conf
[app_conn]
host=127.0.0.1
dbname=postgres
user=hr_user
port=5433
EOT
cat /etc/postgresql-common/pg_service.conf

Connect to the database using the service name.

psql service=app_conn

The same connection can also be made using PGSERVICE environment variable.

export PGSERVICE=app_conn
psql