Opened 4 years ago

Closed 5 months ago

#62 closed enhancement (fixed)

Support SSL connections

Reported by: cito Owned by:
Priority: major Milestone: 5.1
Component: C Module Version: 4.1
Keywords: security network Cc:

Description

PyGreSQL should support SSL connections to the database.

This means it should be possible to pass the sslmode and other parameters mentioned here: http://www.postgresql.org/docs/9.4/static/libpq-connect.html

Change History (4)

comment:1 Changed 4 years ago by cito

  • Milestone changed from 5.0 to 5.1

This is important, bu must be done properly. So postponing to 5.1 to get 5.0 out.

comment:2 Changed 3 years ago by cito

As Patrick McPhee points out on the mailing list, this is working already via connection strings or connection URIs (passed as the dbname parameter), like

db = pq.DB('dbname=livedb host=insecurehost sslmode=require')

or

db = pg.DB('postgresql://testhost/testdb?sslmode=verify-ca')

This should also work similarly for pgdb connections.

See https://www.postgresql.org/docs/9.6/static/libpq-connect.html#LIBPQ-CONNSTRING for details on how/why this works.

So do we really need to add the ssl parameters as keyword args to the connenct() function? Or will this suffice? In any case, we should document it properly and close this ticket only when that is done.

comment:3 Changed 3 years ago by cito

Note by Patrick:

In fact it also works with DB API 2, but it's the database argument, which is not first in the list, so you need something like this:

db = pgdb.connect(database='host=myinsecurehost port=12345
sslmode=require dbname=postgres password=password001')

although these also work:

db = pgdb.connect(':host=myinsecurehost port=12345 sslmode=require
dbname=postgres password=password001:::')
db = pgdb.connect(None, None, 'password001', 'myinsecurehost:12345',
'sslmode=verify-ca dbname=postgres')
db.cursor().execute('show server_version').fetchall()
[['9.5.5']]

That server requires me to send an x509 certificate as well as a password. If I try to connect from a host where I don't have that certificate, I get an error:

pg.InternalError: FATAL:  connection requires a valid client certificate

So, client certificate sending works. And trying to connect to a db
which doesn't have ssl set up results in

pg.InternalError: server does not support SSL, but SSL was required

comment:4 Changed 5 months ago by cito

  • Resolution set to fixed
  • Status changed from new to closed

With DB API 2, you can also send the parameters as keyword parameters - they will be automatically added to the database argument.

I have now documented how to send parameters with the classic module as well.

We could support keyword parameters in a similar way as in DB API 2, by converting them to connection strings. But the classic module uses the connect function from the C module, so this would be more difficult, or could only be done in the DB wrapper.

Alternatively, we could use PQconnectdbParams instead of PQsetdbLogin for both modules and then send the additional parameters using that function. I'll make a new ticket out of that.

Btw, in PyGres 5.1 the connection now has the information from PQsslAttribute as ssl_attributes and PQsslInUse as ssl_in_use attribute.

Note: See TracTickets for help on using tickets.