Python3: Connect to Remote Postgres Database with SSL

Question

I am in the process of setting up a remote PostgreSQL database. The server is running CentOS 7 and PostgreSQL-9.5. Currently, I am testing whether users can query the database. To this end, I have the following:

import psycopg2

host    = 'server1'
dbname  = 'test_db'
user    = 'test-user'

sslcert = 'test-db.crt'
sslmode = 'verify-full' 
sslkey  = 'test-db.key'


dsn = 'host={0} dbname={1} user={2} sslcert={3} sslmode={4} sslkey={5}'.format(host, dbname, user, sslcert, sslmode, sslkey)

conn = psycopg2.connect(dsn)

The connection times out with the following error:

psycopg2.OperationalError: could not connect to server: Connection timed out (0x0000274C/10060)
    Is the server running on host "server1" (xx.xx.xx.xx) and accepting
    TCP/IP connections on port 5432?

I have tried several things (given below). I'm trying to pin down on which side the problem exists: the Python end or the database configuration:

• Is the Python syntax correct?
• Where can I find documentation concerning the DSN arguments, such as sslmode, sslcert, and sslkey?
• Is there a different package better suited for this kind of connection?
• What other questions should I be asking?

---

I have checked the following:

1. 'server1' was entered correctly and the IP address returned by Python corresponds
2. All other arguments are spelled correctly and refer to the correct object
3. Postgres is currently running (service postgres-9.5 status shows "active")
4. Postgres is listening on port 5432 (netstat -na | grep tcp shows "LISTEN" on port 5432)
5. SSL is running for my table (psql -U username -W -d test-db -h host returns SSL connection (protocol: TLSAv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
6. user=test-user has been added to postgres as a Superuser

My understanding is that psycopg2 is the appropriate package to use nowadays. I have scoured the documentation and don't find much information regarding SSL connections. I found this SO post which talks about SSL connections using psycog2, but I can't match some of the syntax to the documentation.

In the Python script, I have tried the following in all 4 combinations:

1. Use sslmode='require'
2. Use absolute paths to test-db.crt and test-db.key

Answer 1

It appears that you have presented yourself with a False Dilemma. The problem does not lie solely between Python and the database configuration. There exist other entities in between which may cause a disconnect.

---

• Is the Python syntax correct?

Yes. The syntax is described in the psycopg2.connect() documentation. It has the form:

psycopg2.connect(dsn=None, connection_factory=None, cursor_factory=None, async=False, **kwargs)

where the DSN (Data Source Name) can be given as a single string or as separate arguments:

conn = psycopg2.connect(dsn="dbname=test user=postgres password=secret")

conn = psycopg2.connect(dbname="test", user="postgres", password="secret")

---

• Where can I find documentation concerning the DSN arguments, such as sslmode, sslcert, and sslkey?

Note that as DSN arguments, they are not part of the psycopg2 module. They are defined by the database, in this case Postgres. They can be found in the chapter on Database Connection Control Functions, under the Parameter Key Words section.

---

• What other questions should I be asking?

Perhaps,

Is there anything between the host (the PostgresSQL server) and the client (the local Python instance) which could prevent communication?

One answer to this would be "the firewall." This turned out to be the problem. Postgres was listening and Python was reaching out. But the door was closed.