使用psql可连远程PostgreSQL，libpq+libpqxx连接失败求助

问题场景 #

在Digital Ocean上部署PostgreSQL实例后，使用psql命令可正常连接：

psql "host=db-postgresql-nyc1-70444-do-user-4921290-0.b.db.ondigitalocean.com port=25060 dbname=defaultdb user=doadmin password=MY_PASSWORD sslmode=require"

自行编译了启用SSL支持的libpq（PostgreSQL 12.2）和libpqxx（7.0.7），编译脚本如下：

# Unix like system
test -e postgresql-12.2.tar.gz || wget https://ftp.postgresql.org/pub/source/v12.2/postgresql-12.2.tar.gz
test -e postgresql-12.2 || tar -xzvf postgresql-12.2.tar.gz
cd postgresql-12.2

test -e build_amd64 && rm -rf build_amd64
mkdir build_amd64
cd build_amd64
../configure --without-readline CFLAGS="-O3 -fpic" CXXFLAGS="-fpic" CPPFLAGS="-fpic" --prefix=$PWD/packaged --with-includes=$(pwd)/../../openssl-OpenSSL_1_1_1k/build_amd64/packaged/include/ --with-openssl --with-libraries=$(pwd)/../../openssl-OpenSSL_1_1_1k/build_amd64/packaged/lib/

# multithreaded make
if [ "$(uname)" == "Darwin" ]; then
    sysctl -n hw.physicalcpu | xargs -I % make -j%
elif [ "$(expr substr $(uname -s) 1 5)" == "Linux" ]; then
    nproc | xargs -I % make -j%
fi

make install
ar dv packaged/lib/libpq.a legacy-pqsignal.o

test -e 7.0.7.tar.gz || wget https://github.com/jtv/libpqxx/archive/7.0.7.tar.gz
test -e libpqxx-7.0.7 || tar -xzvf 7.0.7.tar.gz
cd libpqxx-7.0.7

test -e build_amd64 && rm -rf build_amd64
mkdir build_amd64
cd build_amd64

# Unix like system
LIBNAME=libpq.so
if [[ "$OSTYPE" == "darwin"* ]]; then
  LIBNAME=libpq.dylib
fi
cmake -DPostgreSQL_TYPE_INCLUDE_DIR=${PWD}/../../postgresql-12.2/build_amd64/packaged/include \
-DPostgreSQL_LIBRARY=${PWD}/../../postgresql-12.2/build_amd64/packaged/lib/${LIBNAME} \
-DPostgreSQL_INCLUDE_DIR=${PWD}/../../postgresql-12.2/build_amd64/packaged/include \
-DCMAKE_POSITION_INDEPENDENT_CODE=ON -D CMAKE_BUILD_TYPE=Release -DBUILD_TEST=OFF  ..

# multithreaded make
if [ "$(uname)" == "Darwin" ]; then
    sysctl -n hw.physicalcpu | xargs -I % make -j%
elif [ "$(expr substr $(uname -s) 1 5)" == "Linux" ]; then
    nproc | xargs -I % make -j%
fi

make DESTDIR=./packaged install
# On centos, it is installed to lib64 instead of lib, so create a symbolic link for consistency
test -e packaged/usr/local/lib || ln -s ./lib64/ ./packaged/usr/local/lib

使用与psql完全相同的连接字符串初始化pqxx::connection时，程序抛出异常：

m_connectionPtr = std::make_unique<pqxx::connection>("host=db-postgresql-nyc1-70444-do-user-4921290-0.b.db.ondigitalocean.com port=25060 dbname=defaultdb user=doadmin password=MY_PASSWORD sslmode=require");

异常信息：

terminate called after throwing an instance of 'pqxx::broken_connection'
  what():  FATAL:  pg_hba.conf rejects connection for host "181.224.248.206", user "doadmin", database "template1", SSL on

Aborted (core dumped)

程序可正常连接本地PostgreSQL，但无法连接Digital Ocean的远程实例，且DO控制台显示数据库允许所有IP访问。

问题原因 #

libpqxx内部存在默认逻辑：在连接用户指定的数据库前，会先尝试连接template1数据库，用于检查目标数据库是否存在。但Digital Ocean托管的PostgreSQL实例禁止外部用户连接template1数据库，导致该预检查请求被服务器的pg_hba.conf拦截，触发连接异常。

解决方案 #

移除libpqxx中连接template1的预检查逻辑，修改后程序即可正常连接指定的目标数据库。

内容的提问来源于stack exchange，提问作者cyrusbehr