Unable to use `pgcli service={service}`

Comienza comparando el manejo de pgcli service={service} con el comando URI de PostgreSQL que funciona, utilizando las entradas de .pg_service.conf y la configuración de conexión de PostgreSQL en postgresql.conf y pg_hba.conf. Reproduce el fallo con los comandos indicados y verifica que las conexiones basadas en service utilicen el mismo certificado de cliente, clave, CA y configuración SSL que la forma URI.

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