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
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 anywaysbrew 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
The current psycopg2 implementation supports: Python 2 versions from 2.6 to 2.7. Python 3 versions from 3.2 to 3.6.
With SSL support compiled in, the PostgreSQL server can be started with SSL enabled by setting the parameter ssl to on in postgresql. conf. The server will listen for both normal and SSL connections on the same TCP port, and will negotiate with any connecting client on whether to use SSL .
psycopg2-binary and psycopg2 both give us the same code that we interact with. The difference between the two is in how that code is installed in our computer.
Prerequisites. The current psycopg2 implementation supports: Python versions from 3.6 to 3.11. PostgreSQL server versions from 7.4 to 15.
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
).
I had this same error, which turned out to be because I was using the Anaconda version of psycopg2. To fix it, I had adapt VictorF's solution from here and run:
conda uninstall psycopg2
sudo ln -s /Users/YOURUSERNAME/anaconda/lib/libssl.1.0.0.dylib /usr/local/lib
sudo ln -s /Users/YOURUSERNAME/anaconda/lib/libcrypto.1.0.0.dylib /usr/local/lib
pip install psycopg2
Then when you run conda list
you'll see psycopg2 installed with <pip>
in the far right column. After that, I just restarted Python and everything worked.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With