Search code examples
postgresqlperldbi

Perl/Postgresql: sslmode value "require" invalid when SSL support is not compiled

I'm trying to connect to a Postgresql 12.1 DB instance using Perl 5.26.1 on SLES12 machine, but running into errors. My test script is straightforward.

#!/usr/pkgs/perl/5.26.1/bin/perl

use DBI;
my $dbname = "XXX";  
my $host = "XXX";  
my $port = XXX;  
my $username = "XXX";  
my $password = "XXX";

my $dbh = DBI -> connect("dbi:Pg:dbname=$dbname;sslmode=require;host=$host;port=$port",  
                            $username,
                            $password,
                            {AutoCommit => 0, RaiseError => 1}
                         ) or die $DBI::errstr;

When I run this script, I get the following error: sslmode value "require" invalid when SSL support is not compiled in at...

I have set LD_LIBRARY_PATH to point to my local version for the libpq library (built from sources postgresql-13.3). I built this library with --open-ssl support. I confirmed this with pg_config --configure.

$ ./bin/pg_config --configure
 '--prefix' '/myworkarea/tmp/local' '--with-perl' '--with-python' '--with-tcl' '--with-openssl' '--with-ldap' '--with-pam' '--with-libxml' '--with-libxslt'

I can connect to the DB without any issues using pgAdmin. I confirmed it has support for connecting to Postgres via DBD:Pg.

$ perl -MDBI -e 'DBI->installed_versions'
  Perl            : 5.026001    (x86_64-linux)
  OS              : linux       (4.4.49-92.14-default)
  DBI             : 1.639
  DBD::mysql      : 4.043
  DBD::Sybase     : 1.16
  DBD::Sponge     : 12.010003
  DBD::SQLite     : 1.54
  DBD::Proxy      : 0.2004
  DBD::Pg         : 3.7.4
  DBD::Oracle     : 1.80
  DBD::ODBC       : 1.56
  DBD::Multiplex  : 2.11
  DBD::Mock       : 1.45
  DBD::Mem        : 0.001
  DBD::LDAP       : 0.22
  DBD::Gofer      : 0.015327
  DBD::File       : 0.44
  DBD::ExampleP   : 12.014311
  DBD::DBM        : 0.08
  DBD::CSV        : 0.49

But I look at the Pg.so to find the dependencies, but it seems like it is still pointing to the system installed filed instead of my version. How do I fix this?

$ ldd /usr/pkgs/perl/5.26.1/lib64/site_perl/x86_64-linux/auto/DBD/Pg/Pg.so
        linux-vdso.so.1 (0x00007ffff7ffa000)
        libpq.so.5 => /usr/pkgs/postgresql/9.5.0/lib/libpq.so.5 (0x00007ffff797b000)   <-- Not picking up my setting from LD_LIBRARY_PATH
        libm.so.6 => /lib64/libm.so.6 (0x00007ffff767e000)
        libc.so.6 => /lib64/libc.so.6 (0x00007ffff72d9000)
        libpthread.so.0 => /lib64/noelision/libpthread.so.0 (0x00007ffff70bc000)
        /lib64/ld-linux-x86-64.so.2 (0x00007ffff7ddb000)
Solution

  • The PostgreSQL client shared library (libpq) that is linked with DBD::Pg was built without SSL support. That won't work. You have to use a libpq that is built with SSL support.

    This requires doing two things:

    1. Configure PostgreSQL with the --with-openssl switch.
    2. Build the DBD:Pg module to link with the libraries in 1) by setting the POSTGRES_LIB, POSTGRES_HOME environment variables. See the README section on installation.