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:127.0.0.1:5432 username@servername
    

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

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

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

    [my-pg-service]
        host=127.0.0.1
        port=5433
        dbname=mydatabase
        user=pguser
    

    just append the .pgpass file here:

    [my-pg-service-2]
    host=127.0.0.1
    port=5433
    dbname=mydatabase
    user=pguser
    passfile=~/.pgpass

    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:

    #hostname:port:database:username:password
    

    Remote pg database on server servername when using an SSH tunnel (5433)

    127.0.0.1:5433:mydatabase:pguser:8+k3&4d2ihs1=&gp!*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@127.0.0.1:5433/mydatabase
    

    (I only changed localhost to 127.0.0.1).

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

    localhost:5433:mydatabase:pguser:8+k3&4d2ihs1=&gp!*y)62xoh+^^z$&*ino!66jj()(yw@o36
    

    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.

    TYPE DATABASE USER ADDRESS METHOD

    "local" is for Unix domain socket connections only

    local all all trust

    IPv4 local connections:

    host all all 127.0.0.1/32 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 127.0.0.1/32 trust
    host replication all ::1/128 trust

    host all all all md5

    OS: Ubuntu 21.10
    PG: 14

    Doc:
    https://www.postgresql.org/docs/14/libpq-pgservice.html
    https://www.postgresql.org/docs/14/libpq-connect.html#LIBPQ-PARAMKEYWORDS
    https://www.postgresql.org/docs/14/libpq-pgpass.html



  • 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