Secure PostgreSQL connections on your Django project

Last week, an article was published with some interesting numbers about the security of PostgreSQL servers publicly exposed to the internet (You can find it here).

But more than the numbers, what really caught my attention was the fact that most clients and libraries used to access and interact with the databases have insecure defaults:

…most popular SQL clients are more than happy to accept unencrypted connections without a warning. We conducted an informal survey of 22 popular SQL clients and found that only two require encrypted connections by default.

Jonathan Mortensen

The article goes on to explain how clients connect to the database server and what options there are to establish and verify the connections.

So, this week, let’s see how we can set up things in Django to ensure our apps are communicating with the database securely over the network.

Usually, we set up the database connection like this:

DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.postgresql",
        "NAME": "db_name",
        "USER": "db_user",
        "PASSWORD": "db_password",
        "HOST": "127.0.0.1",
        "PORT": "5432",
    }
}

The above information can also be provided using a single “URL” such as postgres://USER:PASSWORD@HOST:PORT/NAME, but in this case, you might need some extra parsing logic or to rely on an external dependency.

Now, based on that article psycopg2 by default prefers to use an encrypted connection but doesn’t require it, or even enforces a valid certificate. How can we change that?

By using the field OPTIONS and then set the sslmode:

DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.postgresql",
        "NAME": "db_name",
        "USER": "db_user",
        "PASSWORD": "db_password",
        "HOST": "127.0.0.1",
        "PORT": "5432",
        "OPTIONS": {
            "sslmode": "<mode here>"
        }
    }
}

The available modes are:

• disable
• allow
• prefer
• require
• verify-ca
• verify-full

The obvious choice for a completely secure connection is verify-full, specially when the traffic goes through a public network.

If you are using a URL, something like this should do the trick: postgres://USER:PASSWORD@HOST:PORT/NAME?sslmode=verify-full.

And that’s it, at least on the client’s side.

If the above is not an option for you, I recommend taking a look at pgproxy. You can find more details here.