postgresql - Psycopg2 Python SSL Support is not compiled in

Question

I am trying to connect to my postgres database using psycopg2 with sslmode='required' param; however, I get the following error

psycopg2.OperationalError: sslmode value "require" invalid when SSL support is not compiled in

Heres a couple details about my system

• Mac OS X El Capitan
• Python 2.7
• Installed psycopg2 via pip
• Installed python via homebrew

Here is what I tried to do to fix the problem

• brew uninstall python
• which python still shows python living in /usr/local/bin/python, tried to uninstall this but couldnt. And heard that this is the python that the OS uses and should not be uninstalled anyways
• brew install python --with-brewed-openssl --build-from-source
• pip uninstall psycopg2
• pip install psycopg2

After doing all of this, the exception still happens. I am running this python script via #!/usr/bin/env python Not sure if it matters, but that is a different directory than the one that which python shows

Answer 1

Since you're installing via pip, you should be using the most recent version of psycopg2 (2.6.1). After a little digging through the code, it seems that the exception is being thrown in connection_int.c, which directly calls the postgresql-c-libraries to set up the db-connection. The call happens like so:

self->pgconn = pgconn = PQconnectStart(self->dsn);

Dprintf("conn_connect: new postgresql connection at %p", pgconn);

if (pgconn == NULL)
{
    Dprintf("conn_connect: PQconnectStart(%s) FAILED", self->dsn);
    PyErr_SetString(OperationalError, "PQconnectStart() failed");
    return -1;
}
else if (PQstatus(pgconn) == CONNECTION_BAD)
{
    Dprintf("conn_connect: PQconnectdb(%s) returned BAD", self->dsn);
    PyErr_SetString(OperationalError, PQerrorMessage(pgconn));
    return -1;
}

The keywords which were specified in your connect statement to psycopg2.connect() are being handled to that function and errors are returned as OperationalError exception.

The error is actually being generated directly in the postgresql-lib - you may want to check which version you are using, how it was built and, if possible, upgrade it to a version with SSL support or rebuilt it from source with SSL enabled.

The postgresql-docs also state that missing SSL support will raise an error, if the sslmode is set to require, verify-ca or verify-full. See here under sslmode for reference.

The postgres-website lists several ways to install postgres from binary packages, so you might choose one which suits your needs. I'm not familiar with OSX, so I don't have a recommendation what's best.

This question may also be helpful.

You also need to reinstall the psycopg2-module, be sure to use the newly installed lib when rebuilding it. Refer to the linked question (in short, you will need to place the path to pg_config which is included in your new installation to $PATH when running pip install psycopg2).