There are so many ways to connect to PostgreSQL. One of my favourite, yet underutilised is using service definition, the feature of any application using the libpq library. In this article we will explore what service definition is, where and how it can make your life with PostgreSQL much easier.

PostgreSQL connection methods

PostgreSQL offers several methods to establish connections to databases, each with its own benefits and use cases:

  • Providing individual parameters like host, port, database, username, and password directly
  • Using a URI format like postgresql://username:password@hostname:port/dbname
  • Using environment variables PGHOST, PGPORT, PGDATABASE, etc.
  • Command line arguments
  • And using pre-defined connection profiles stored in the service connection file(s)

While the first four methods are widely known and used, service definitions remain somewhat of a hidden gem despite offering significant advantages in terms of security, maintainability, and convenience.

Service definition and format

Connection service file allows defining either per-user or system-wide connections (if both exist, the user one will take precedence). The service file uses INI format and employs the full list of libpq parameters to configure the service. A sample file looks like:

[mydb]
host=localhost
port=5432
user=some_admin
password=password123
dbname=my_db

You can consult the list of parameters, together with their description, directly using the documentation. All you need to do is place the file in the correct location, which is: - either user-specific ~/.pg_service.conf - or system-wide pg_service.conf in the PostgreSQL's configuration directory

and try it directly using:

psql service=mydb

Why use service definitions?

With all other methods available to connect to PostgreSQL, why even bother with service definition? The primary benefit is the level of separation. While creating connection strings/DNS and environment variables is easy, they all suffer from the same fundamental problem: tight coupling between your application and database connection details.

Service definitions break this coupling by extracting connection parameters into a standardised configuration format that any libpq-based application can reference. Imagine using a single service definition across development, test, and production environments. All it takes is to define:

service=my_app_db

and supply the correct service definition for the target environment. The application remains unaware of the underlying connection details, creating a clean abstraction layer with clear service name identification.

Another aspect is re-usability. While your application might rely on an easy to change configuration mechanism, you can easily spot the difference between these two commands:

pg_dump --schema-only -h my-app-db.internal -p 5432 -U some_user -d application > schema.dump
pg_dump service=my_app_db > schema.dump

The same applies to even internal mechanisms inside the running PostgreSQL (dblink being a prime example - see below). This simplification extends to database migration, replication setup, and monitoring configuration. The consistency of service-based connections reduces errors and streamlines operational procedures.

The benefits increase with improved security. Having a service definition file stored outside your project GIT repository significantly reduces accidental credentials sneaking inside the repository. Separation (which you can further increase by use of passfile instead of password) extends flexibility, while improving the security baseline.

Using Service Definitions

Here are some ways to use service definitions:

With DSN keywords:

service=my_app_db
service=my_app_db application_name=myapp

In connection URLs:

postgresql:///?service=my_app_db
postgresql:///?service=my_app_db&application_name=myapp

Via environment variables:

PGSERVICE=my_app_db

Given that libpq is the foundation of most programming language drivers, the same is applicable almost across the board:

In Go:

// Go
db, err := sql.Open("postgres", "service=myservice")

// Go (pgx)
conn, err := pgx.Connect(context.Background(), "service=my_app_db")

Same with Python:

# Python (with psycopg2)
conn = psycopg2.connect("service=my_app_db")

Java:

// Java
System.setProperty(
    "org.postgresql.pgservicefile",
    "/path/to/pg_service.conf");
Connection conn = DriverManager.getConnection("jdbc:postgresql:///?service=my_app_db"

Not forgetting PHP:

// PHP
$conn = pg_connect("service=my_app_db");

// PHP PDO
$pdo = new PDO("pgsql:service=my_app_db");

Or Rust:

// Rust
let (client, connection) =
    tokio_postgres::connect("service=my_app_db", NoTls).await?;

Unfortunately, the services definitions are not available in drivers/modules not based on libpq, which includes, for example, Node's pg module.

Using service definition inside PostgreSQL

A notable use case for service definition is within PostgreSQL itself. Hardcoding configuration details - either directly or using idempotent schema files - is not particularly fun. Service definition significantly improves configuration management in these cases.

This is the case for dblink:

SELECT * FROM dblink('service=my_app_db', 'SELECT user_id, email FROM users');

As well as FDW configuration:

CREATE SERVER foreign_server
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (service 'my_app_db');

While postgres_fdw naturally supports service definition, being based on libpq, please note this is an exception and you can't expect it for other foreign data wrappers.

Similar to that, you need to consider the validity of the credentials, as libpq reads the service definition at connection time, not at definition time. I.e., for Postgres FDW:

  • Service name is stored on CREATE SERVER execution
  • Service definition is read when connection is established (foreign table accessed for the first time or service connection is validated)

Similar for dblink where the definition is read each time it establishes a new connection. Don't forget the existing connections are not affected.

Partial Service Definitions

The flexibility offered by service definition does not end there. The libpq library follows a specific order when determining connection parameters:

  1. Command-line parameters take highest precedence
  2. Environment variables come next
  3. Service definitions provide the next level
  4. Default values are used as a last resort

This hierarchy enables you to define only some parameters in the service definition:

[mydb]
host=localhost
port=5432
dbname=my_db

With no credentials defined, you can later present the specific keywords (like credentials in this example) using other means:

PGUSER=user1
PGPASSWORD=pass1 psql service=my_db

The same applies to connection strings/DSNs, etc. This allows you to use a "best of both worlds" approach, combining the consistency of service definition with the flexibility to override it.

Conclusion

Service definitions represent a powerful but often overlooked feature of PostgreSQL's connection system. They provide a clean separation of connection details from application code, enhance security by centralising credential management, and simplify connection management across different environments and applications.