On 18th of December 2024, Michael Paquier committed patch:

psql: Add more information about service name
 
This commit adds support for the following items in psql, able to show a
service name, when available:
- Variable SERVICE.
- Substitution %s in PROMPT{1,2,3}.
 
This relies on 4b99fed7541e, that has made the service name available in
PGconn for libpq.
 
Author: Michael Banck
Reviewed-by: Greg Sabino Mullane
Discussion: https://postgr.es/m/6723c612.050a0220.1567f4.b94a@mx.google.com

Services are less known, and (to my eyes) not fully really advertised option (there is no named command line parameter to provide service name!) for connecting to Pg from libpq-using apps.

For example, instead of using:

=$ psql -h some.complex.hostname -p 6532 -U app.user -d backend

I could make ~/.pg_service.conf file that would contain:

[prod]
host=some.complex.hostname
port=6532
user=app.user
dbname=backend

and then I can:

=$ psql service=prod

To get the same connection.

There are more options that can be configured, too.

The thing is that while I can see where I'm connected to (in psql) using either prompt:

=# \SET PROMPT1 'user: %n, db: %/, host: %M, port: %> :: '
USER: depesz, db: test, host: [LOCAL], port: 5430 ::

Or using \conninfo command:

test=# \conninfo
You are connected TO DATABASE "test" AS USER "pgdba" ON host "localhost" (address "::1") at port "5430".

But I can't really see what service I used to connect. This problem is no more 🙂

While it doesn't get displayed in \conninfo, I can, at the very least:

=$ \echo :SERVICE
testsrv

Or, I can put it in any prompt, like here:

=# \SET PROMPT1 'user: %n, db: %/, host: %M, port: %>, service: %s :: '
USER: pgdba, db: test, host: localhost, port: 5430, service: testsrv ::

That is great. Thanks to everyone that was involved.