Able to connect to remote PostgreSQL database using psql, but unable to connect using libpq libpqx-CodePudding

I have a PostgreSQL instance running on Digital Ocean. When using the command line tool psql, I am able to connect to the database fine. I am using the following command:

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"

I have now compiled an executable which makes use of libpq and libpqxx. I have compiled the two libraries using the following arguments (as you can see, ssl is supported).

  # 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

I then pass the exact same connection string to the pqxx::connection constructor as follows:

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");

However, doing so spits out the following exception:

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)

For what it's worth, I'm able to connect to a PostgreSQL database using my libpqxx logic above when it is running locally on the same machine. I for the life of my can't seem to connect to a remote database though using libpqxx. Any ideas?

Edit: I don't have access to the pg_hba.conf on the server since it is a managed database hosted by Digital Ocean. However, on the digital ocean console page, it says that the database is open to connections from all IP Addresses.

Edit 2 I now understand the issue. In my executable, I was first connecting to the template1 database, and then making a query to the database to see if the database name provided by the user existed (with libpqxx you can only make database queries once you have established a db connection, and you require a database name in order to create the connection). This approach was not working with hosted database services such as Digital Ocean as they do not allow you to connect to the template1 database. Once I removed that logic from my code, then I was able to connect directly to the specified database correctly.

CodePudding user response：

I know understand what the issue is:

In my executable, I was first connecting to the template1 database, and then making a query to the database to see if the database name provided by the user existed (with libpqxx you can only make database queries once you have established a db connection, and you require a database name in order to create the connection). This approach was not working with hosted database services such as Digital Ocean as they do not allow you to connect to the template1 database. Once I removed that logic from my code, then I was able to connect directly to the specified database correctly.