How do I connect to Postgresql using SSL from SqlAchemy+pg8000?

Question

Connecting to postgres via pg8000 from SqlAlchemy worked fine until I enabled SSL on postgres.

db = create_engine('postgresql+pg8000://user:pass@hostname/dbname', echo=True).connect()

Now it seems to fail with:

File "/Library/Python/2.7/site-packages/pg8000/core.py", line 872, in __init__
raise InterfaceError("communication error", exc_info()[1])
sqlalchemy.exc.InterfaceError: (InterfaceError) ('communication error', error(61, 'Connection refused')) None None

Dixiedixieland

Answer 1

You need to add a connect_args dict:

db = create_engine(
    'postgresql+pg8000://user:pass@hostname/dbname',
    connect_args={'sslmode':'require'},
    echo=True,
).connect()

Mccord

In fact it seems that it is able to defect sslmode by itself, as I was connecting to the wrong host. Still, is very good to know how to enforce-ssl mode. Thanks. – Dixiedixieland

note this can be stated inline more easily: create_engine('postgresql+pg8000://user:pass@hostname/dbname?sslmode=require') – Biskra

Answer 2

The accepted answer no longer works, at least with these versions:

Python                   3.9
pg8000                   1.19.5
SQLAlchemy               1.4.12

The pg8000 docs describe what you have to do. Use

engine = create_engine('postgresql+pg8000://user:password@host/db', 
                       connect_args={'ssl_context': True})

which passes the result of ssl.create_default_context() to the connection creator. If a custom SSL context is required, pass it as the value instead of True:

import ssl

ssl_context = ssl.SSLContext()
# Set attributes as required  ...

engine = create_engine('postgresql+pg8000://user:password@host/db', 
                       connect_args={'ssl_context': ssl_context})

Starstudded