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
