search

ambarltd / pgt-proxy

Intermediary server to easily and securely connect TLS enabled PG clients to TLS enabled PG servers.
MIT License
17 stars 1 forks source link

Connection using ssl=require returns "invalid peer certificate: UnknownIssuer" #4

Closed willianantunes closed 1 day ago

willianantunes commented 5 days ago

When I try to connect using a SQL client such a DataGript with SSL mode require, the proxy returns an error. Here's the entire log:

2024-11-23T16:45:29.942484Z  INFO ThreadId(01) pgt_proxy: 39: Hello from PGT Proxy. Starting up!
2024-11-23T16:45:29.942508Z  INFO ThreadId(01) pgt_proxy: 41: Fetching Server Config.
2024-11-23T16:45:29.943365Z  INFO ThreadId(01) server_config{cert_path="./self_issued_cert.pem" private_key_path="./self_issued_key.pem"}: pgt_proxy::tls_server_config: 19: Loaded TLS files for inbound connections.
2024-11-23T16:45:29.943382Z  INFO ThreadId(01) pgt_proxy: 46: Fetched Server Config.
2024-11-23T16:45:29.943388Z  INFO ThreadId(01) pgt_proxy: 47: Fetching Client Config.
2024-11-23T16:45:29.943517Z  INFO ThreadId(01) pgt_proxy::tls_client_config: 37: Loaded TLS files for outbound connections. ca_roots_directory="/etc/pgt_proxy/client_tls/custom_cas"
2024-11-23T16:45:29.943526Z  INFO ThreadId(01) pgt_proxy: 49: Fetched Client Config.
2024-11-23T16:45:29.943568Z  INFO ThreadId(01) pgt_proxy: 52: Listening port="9000"
2024-11-23T16:45:38.216240Z  INFO ThreadId(08) handle_inbound_request{addr="dev-acme.writer.postgres.database.azure.com" port="5432" request_id="4eebfeec-9fd9-439c-8d01-af741e8894ff"}: pgt_proxy: 114: Accepting inbound connection from PG client. Proceeding to handshake. request_id="4eebfeec-9fd9-439c-8d01-af741e8894ff"
2024-11-23T16:45:38.454412Z  INFO ThreadId(02) handle_inbound_request{addr="dev-acme.writer.postgres.database.azure.com" port="5432" request_id="4eebfeec-9fd9-439c-8d01-af741e8894ff"}: pgt_proxy: 120: Inbound TLS OK. Proceeding to outbound connection to PG server. request_id="4eebfeec-9fd9-439c-8d01-af741e8894ff"
2024-11-23T16:45:40.069483Z ERROR ThreadId(02) pgt_proxy: 77: Task failed with an error. e=invalid peer certificate: UnknownIssuer request_id=4eebfeec-9fd9-439c-8d01-af741e8894ff

If I connect directly to the address dev-acme.writer.postgres.database.azure.com it works normally. What it could be? Everything seems all right. The SSL mode require implicitly trusts the server's SSL certificate and doesn't try to validate that the certificate was signed by a CA.

This is the Docker Compose:

services:
  pgt-proxy:
    image: ambarltd/pgt-proxy:v1.2
    entrypoint: "bash -c"
    working_dir: "/srv"
    ports:
      - "7000:9000"
    volumes:
      - ./pgt-proxy-startup.sh:/srv/pgt-proxy-startup.sh
    environment:
      ORIGIN_SERVER_ADDRESS: "dev-acme.writer.postgres.database.azure.com"
    command: "./pgt-proxy-startup.sh"
    depends_on:
      db:
        condition: service_healthy

The content of pgt-proxy-startup.sh file:

#!/usr/bin/env bash

set -e

echo "Generating self-signed certificate for the proxy server"
openssl req -x509 -out self_issued_cert.pem -keyout self_issued_key.pem \
  -days 365 \
  -newkey rsa:2048 -nodes -sha256 \
  -subj '/CN=localhost' -extensions EXT -config <( \
   printf "[dn]\nCN=localhost\n[req]\ndistinguished_name = dn\n[EXT]\nsubjectAltName=DNS:localhost\nkeyUsage=digitalSignature\nextendedKeyUsage=serverAuth") && \
   chmod 644 self_issued_cert.pem && \
   chmod 600 self_issued_key.pem

ORIGIN_SERVER_ADDRESS=${ORIGIN_SERVER_ADDRESS:-db}
ORIGIN_SERVER_PORT=${ORIGIN_SERVER_PORT:-5432}
echo "You set the origin server address to $ORIGIN_SERVER_ADDRESS and port to $ORIGIN_SERVER_PORT"

# CAS_FOLDER=/etc/pgt_proxy/client_tls/aws_rds/
# CAS_FOLDER=/etc/pgt_proxy/client_tls/firefox/
CAS_FOLDER=/etc/pgt_proxy/client_tls/custom_cas
echo "Retrieving the origin server's CA certificate"
mkdir -p $CAS_FOLDER
echo -n | openssl s_client -starttls postgres -connect $ORIGIN_SERVER_ADDRESS:$ORIGIN_SERVER_PORT | sed -ne '/-BEGIN CERTIFICATE-/,/-END CERTIFICATE-/p' > $CAS_FOLDER/origin-ca.pem
chmod 644 $CAS_FOLDER/*
ls $CAS_FOLDER

echo "Starting the proxy server..."
/etc/pgt_proxy/run --server-private-key-path ./self_issued_key.pem \
--server-certificate-path ./self_issued_cert.pem \
--server-port 9000 \
--client-connection-host-or-ip $ORIGIN_SERVER_ADDRESS \
--client-connection-port $ORIGIN_SERVER_PORT \
--client-tls-validation-host $ORIGIN_SERVER_ADDRESS \
--client-ca-roots-path $CAS_FOLDER

Update: If I change the server certs to what is provided in the image, it does not work also:

2024-11-25T01:39:50.576942Z  INFO ThreadId(01) pgt_proxy: 39: Hello from PGT Proxy. Starting up!
2024-11-25T01:39:50.576965Z  INFO ThreadId(01) pgt_proxy: 41: Fetching Server Config.
Error: invalid peer certificate: Other(OtherError(UnsupportedCertVersion))
willianantunes commented 3 days ago

This seems to be the same issue. 🤔

willianantunes commented 1 day ago

I made it work thanks to the development-environment and Secure connectivity with TLS and SSL in Azure Database for PostgreSQL - Flexible Server documentation. This is the final script:

#!/usr/bin/env bash

set -e

# https://docs.openssl.org/master/man5/x509v3_config/#extended-key-usage
# https://docs.openssl.org/master/man5/x509v3_config/#key-usage
# https://superuser.com/a/1248085
echo "Generating self-signed certificate for the proxy server"
openssl req -x509 -out self_issued_cert.pem -keyout self_issued_key.pem \
  -days 365 \
  -newkey rsa:2048 -nodes -sha256 \
  -subj '/CN=localhost' -extensions EXT -config <( \
   printf "[dn]\nCN=localhost\n[req]\ndistinguished_name = dn\n[EXT]\nsubjectAltName=DNS:localhost\nkeyUsage=digitalSignature\nextendedKeyUsage=serverAuth") && \
   chmod 644 self_issued_cert.pem && \
   chmod 600 self_issued_key.pem

ORIGIN_SERVER_ADDRESS=${ORIGIN_SERVER_ADDRESS:-db}
ORIGIN_SERVER_PORT=${ORIGIN_SERVER_PORT:-5432}
echo "You set the origin server address to $ORIGIN_SERVER_ADDRESS and port to $ORIGIN_SERVER_PORT"

# CAS_FOLDER=/etc/pgt_proxy/client_tls/aws_rds/
# CAS_FOLDER=/etc/pgt_proxy/client_tls/firefox/
CAS_FOLDER=/etc/pgt_proxy/client_tls/custom_cas
mkdir -p $CAS_FOLDER

# https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/concepts-networking-ssl-tls#download-root-ca-certificates-and-update-application-clients-in-certificate-pinning-scenarios
# openssl s_client -starttls postgres -showcerts -connect $ORIGIN_SERVER_ADDRESS:$ORIGIN_SERVER_PORT
echo "Retrieving the origin server's CA certificate"
ORIGIN_CERTS=$(openssl s_client -starttls postgres -showcerts -connect $ORIGIN_SERVER_ADDRESS:$ORIGIN_SERVER_PORT < /dev/null 2>/dev/null | sed -ne '/-BEGIN CERTIFICATE-/,/-END CERTIFICATE-/p')
echo "$ORIGIN_CERTS" | awk 'BEGIN {c=0;} /-BEGIN CERTIFICATE-/{c++} {print > "ca-" c ".pem"}'
cp ca-*.pem $CAS_FOLDER
chmod 600 $CAS_FOLDER/*
ls -la $CAS_FOLDER

echo "Starting the proxy server..."
/etc/pgt_proxy/run --server-private-key-path ./self_issued_key.pem \
--server-certificate-path ./self_issued_cert.pem \
--server-port 9000 \
--client-connection-host-or-ip $ORIGIN_SERVER_ADDRESS \
--client-connection-port $ORIGIN_SERVER_PORT \
--client-tls-validation-host $ORIGIN_SERVER_ADDRESS \
--client-ca-roots-path $CAS_FOLDER \
--log TRACE

Guys, I'm really grateful to you for releasing this amazing open-source project. Thank you @galeaspablo, @xandkar, and @lazamar.

willianantunes commented 9 hours ago

Blog post explaining my context and a sample project that uses PGT-Proxy.