Why is psql not asking for db password when using pg_service.conf

  • I've opened an SSH tunnel to connect to a remote server as follows:

    $ ssh -f -N -L 5433: username@servername

    This tunnel has been precisely opened as follows (as shown by ps aux | grep ssh😞

    ssh -f -N -L 5433: username@servername

    I do have a ~/.pg_service.conf with:


    just append the .pgpass file here:


    When running:

    $ psql service=my-pg-service

    it currently (and surprisingly) connects without asking for the database password! (may it be it's stored in a sort of cache because I already used it prior to that command?)

    But when using psql service=my-pg-service-2 it actually asks for the database password.
    I hoped it would behave the opposite way!

    my ~/.pgpass (chmod 0600) file looks like:


    Remote pg database on server servername when using an SSH tunnel (5433)!*y)62xoh+^^z$&*ino!66jj()(yw@o36

    Please note that this command is also asking for the database password:

    $ psql -d postgres://pguser@localhost:5433/mydatabase

    But this one is not:

    $ psql -d postgres://pguser@

    (I only changed localhost to

    And this is precisely because there is no such line starting with localhost in the .pgpass file:


    If I add this line, psql connects without prompting for a password in both previous cases, but I still have the issue with the pg service, e.g. when specifying passfile=~/.pgpass it asks for a password, and when not, it doesn't.

    Can someone please explain this behaviour and what I did wrong?
    May this be linked to the server side configuration which is too broad?

    Just in case, the database is dockerized on the remote server. It uses the https://registry.hub.docker.com/r/postgis/postgis/ (13:3.2) which presents these default characteristics:

    $ docker run \
      --rm \
      --name postgis \
      -e POSTGRES_DB=postgres \
      -e POSTGRES_USER=postgres \
      -e POSTGRES_PASSWORD=changeme \
      -d postgis/postgis:13-3.2

    $ docker exec -it postgis bash -c "tail -n 21 /var/lib/postgresql/data/pg_hba.conf"

    CAUTION: Configuring the system for local "trust" authentication

    allows any local user to connect as any PostgreSQL user, including

    the database superuser. If you do not trust all your local users,

    use another authentication method.


    "local" is for Unix domain socket connections only

    local all all trust

    IPv4 local connections:

    host all all trust

    IPv6 local connections:

    host all all ::1/128 trust

    Allow replication connections from localhost, by a user with the

    replication privilege.

    local replication all trust
    host replication all trust
    host replication all ::1/128 trust

    host all all all md5

    OS: Ubuntu 21.10
    PG: 14


  • That is easy to explain.

    If you don't specify passfile, the password is taken from .pgpass in your home directory, so you are not prompted for a password.

    If you specify passfile=~/.pgpass, it cannot find the file, because it does not understand the tilde, and prompts you for the password.

Log in to reply

Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2