Unable to use `pgcli service={service}`

[fmerinocasallo]

Description

I am running a PostgreSQL server in a Docker container and using Secure TCP/IP Connections with SSL.

In my postgresql.conf file, I include the following lines:

ssl = on
ssl_ca_file = '/run/secrets/ca.crt'
ssl_cert_file = '/run/secrets/server.crt'
ssl_key_file = '/run/secrets/server.key'
# This setting is on by default but it’s always a good idea to
# be explicit when it comes to security.
ssl_prefer_server_ciphers = on
# TLS 1.3 will give the strongest security and is advised when
# controlling both server and clients.
ssl_min_protocol_version = 'TLSv1.3'

I have a .pg_service.conf file where I define a service named {service}:

[{service}]
host={host}
port={port}
user={user}
dbname={dbname}
sslmode=verify-full
sslrootcert=/path/to/ca.crt
sslcert=/path/to/user.crt
sslkey=/path/to/user.key

The command psql service={service} prompts for the password of the user included in the service definition and successfully connects to the specified database afterwards.

However, the command pgcli service={service} returns the following error message:

connection failed: FATAL:  connection requires a valid client certificate
connection to server at "{host}", port {port} failed: FATAL:  no pg_hba.conf entry for host "{host}", user "{user}", database "{dbname}", no encryption

In the PostgreSQL server logs I see the following entries:

postgres  | 2024-08-06 10:50:35.387 GMT [117]: [1-1] user={user},db={dbname} FATAL:  connection requires a valid client certificate
postgres  | 2024-08-06 10:50:35.390 GMT [118]: [1-1] user={user},db={dbname} FATAL:  no pg_hba.conf entry for host "{host}", user "{user}", database "{dbname}", no encryption

Note that the pg_hba.conf I am using contains the following line:

hostssl {dbname}     {user} {host}/32   scram-sha-256   clientcert=verify-full

Surprisingly, the command pgcli "postgresql://{user}@{host}/{dbname}?port={port}&sslmode=verify-full&sslkey=/path/to/user.key&sslcert=/path/to/user.crt&sslrootcert=/path/to/ca.crt" successfully connects to the specified database after prompting for the user password. Same thing happens if I replace pgcli with psql.

Note that in the .pg_service.conf file I am using the very same paths to the user.key, user.crt, and ca.crt files.

Your environment

• Debian 12 (bookworm)
• pgcli 4.1.0 - python 3.12.4 (installed with conda/mamba).
• Packages included in my conda/mamba env include, among other packages:
  • ca-certificates 2024.7.4-hbcca054_0
  • certifi 2024.7.4-pyhd8ed1ab_0
  • openssl 3.3.1-h4bc722e_2
  • postgresql 16.3-h8e811e2_0

[dtwiers]

I have a similar issue: I'm on MacOS, trying to access some remote DBs via a pre-existing SSH tunnel, authenticating with a pgpass file. psql service=my_deployed_db just works, but pgcli service=my_deployed_db does not, with similar results to OP.

[DiegoDAF]

Hi @dtwiers,

Regarding the SSH tunnel + pgpass combination, I have an open PR (#1546) that addresses exactly that case. Currently pgcli replaces the original hostname with 127.0.0.1 when using SSH tunnels, which breaks .pgpass lookup since the hostname no longer matches. The fix preserves the original hostname for .pgpass resolution while using hostaddr=127.0.0.1 for the actual connection through the tunnel.

The service= support itself is a separate issue though, pgcli doesn't parse pg_service.conf at all, which is why pgcli service=my_db fails regardless of SSH tunnels.

[dtwiers]

Yeah, I hadn't even looked into pgpass issues - the lack of pgservice parsing was going to keep me from using it due to pure convenience