2

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://[email protected]: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 official postgis docker image (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

2
  • You shouldn't share a password here
    – Sahap Asci
    Feb 16, 2022 at 14:52
  • Don't worry, it's a random string picked on the internet.
    – s.k
    Feb 16, 2022 at 14:53

2 Answers 2

2

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, PostgreSQL cannot find the file, because it does not understand the tilde (which is a shell feature, and you are not using a shell), so it prompts you for the password.

1
  • 1
    Ah perfect, it didn't work either with passfile=${HOME}/.pgpass but it did when using the full path: passfile=/home/username/.pgpass!
    – s.k
    Feb 16, 2022 at 15:40
2

The service file does not understand the tilde to represent your home directory. It takes it as a literal character in the filename. So that file doesn't exist, you have essentially unset that file. (without a warning, for some reason).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.