Search code examples
postgresqlpsqlpgadminlibpq

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

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:

pgAdmin III error box

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".

Solution

  • 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.