PostgreSQL definition of service not found (pgAdmin, psql, ...)

Question

Context

I have a simple pg_service.conf file located at ${HOME}/pg_service.conf which content is as follow:

# comment
[service_name]
host=localhost
port=5432
dbname=databasename
user=username

Issue

Whenever I try to create a new server from pgAdmin III by setting 'service_name' under the option 'Service', I encounter this error message:

I also tried to copy the pg_service.conf file nearby the .pg_hba.conf and restarting PostgreSQL; the same problem occurred.

A same error occurs when I try to run a psql command:

psql: definition of service "service_name" not found

How could I make it work?
I found this, but that doesn't help me much because it's for Windows users.

Environment

I'm on Ubuntu 18.04 using "PostgreSQL 10.12 (Ubuntu 10.12-0ubuntu0.18.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit".

score 1 · Accepted Answer · edited Apr 02 '20 at 20:36

1

1. User based solution

Beware of the dot . in front of the file name!
The following setup works with PG 10.12 on CentOS 7.7 and .pg_service.conf located in $HOME directory:

   $ cat .pg_service.conf 
    [service_name]
    host=localhost
    port=5410
    dbname=postgres
    user=postgres

Then connect to PostgreSQL as follow;

    $ psql postgresql://?service=service_name
    psql (10.12)
    Type "help" for help.

Port checking:

    postgres=# show port;
     port 
    ------
     5410
    (1 row)

Connection checking:

    postgres=# \c
    You are now connected to database "postgres" as user "postgres".
    postgres=#

2. System-wide solution

You can type this to check for the path where the global pg_service.conf file has to be located:

$ pg_config --sysconfdir
/etc/postgresql-common

You can copy your pg_service.conf file in this directory (but without the dot . here). It has to be own by root.

General hints

1) File precedence

As stated in the doc;

"The user file takes precedence over of the system-wide file."

2) Password

In the case of a peer authentication, both solutions should work with a password stored in your .pgpass file. In that case, you don't need to write it in the .pg_service.conf file.

edited Apr 02 '20 at 20:36

swiss_knight

5,787
8
50
92

answered Apr 02 '20 at 16:16

pifor

7,419
2
8
16

Are you sure the psql command and its output is part of the file? It looks strange to me... – swiss_knight Apr 02 '20 at 16:35
I've fixed the code output: the psql command and its output are not part of .pg_service.conf. – pifor Apr 02 '20 at 16:36
I'm still having the same issue when changing to `user=postgres` in the service file. Launching `psql postgresql://?service=service_name` raise the same error; `psql: definition of service "service_name" not found.` – swiss_knight Apr 02 '20 at 16:41
1

Did you make sure that the service file is located in $HOME and named`.pg_service.conf` (with the initial dot) ? In my setup this work whether I'm connected with Linux 'postgres' user or with 'pierre' user. – pifor Apr 02 '20 at 16:48
You are perfectly right, I missed it! It's working now. Notice the file has to be prefixed with a dot `.` inside your home directory, but no dot is required if it's a system-wide file. – swiss_knight Apr 02 '20 at 17:16