While connecting to the database, is your server showing Postgres SSL is not enabled on the server message? We’ll fix it for you.

To keep the information in the PostgreSQL database safe, most users prefer to encrypt all connections via SSL.

However, disabling the SSL mode often throw errors.

At Bobcares, we help customers with PostgreSQL server configurations as part of our Server Management Services.

Today, we’ll see how our Database Engineers make a secure connection to the Postgres database.

Why do we need to enable SSL in PostgreSQL?

Let’s start with some basic information about PostgreSQL.  It is a relational database that works as the backbone of may websites. These websites write the data on to the database. Further, to show the results, it executes a query on the databases. Thus, there has to be frequent communication between database and web server.

But why do we need SSL?

It simply secures all your database communication. For instance, if the website contains critical information about your clients, an attacker can easily hack the details. However, when the database connection is secure, it encrypts the data. Thus, it protects login details as well as stored data.

Where do we see Postgres SSL is not enabled on the server error?

We now know the importance of SSL in the PostgreSQL server. For secure connections, it requires SSL settings on both the server and the client-side.

Further, let’s see the scenario in which the error occurs.

Imagine a database connection code initiated with SSL mode turned on. However, if the server doesn’t have it enabled, it ends up in “The SSL is not enabled on the server” error.

By default, database admins prefer secure connections. Moreover, Postgres database drivers like pq mandate default sslmode as required.

An attempt to connect to Postgres database using GO programming language appears as:

db.Prepare error: pq: SSL is not enabled on the server

How we enable secure PostgreSQL connections?

Moving on, let’s see how our Support Engineers enable SSL in the PostgreSQL server.

By default, PostgreSQL comes with SSL support. It listens for both SSL and normal connections on the same port. But the client negotiation happens depending on the type of connection.

root@ip-172-xx-yy-32:/var/lib/postgresql/10/main# netstat -lpan | grep :5432
tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 11191/postgres

Generate cert and key files

To enable the SSL mode, we first generate a server certificate and private key. The exact command includes:

openssl genrsa -des3 -out server.key 1024

openssl rsa -in server.key -out server.key

This generates the server.key file. Now we update the permissions and ownership of the key file.

chmod 400 server.key
chown postgres.postgres server.key

It’s time to generate the certificate file by executing

openssl req -new -key server.key -days 3650 -out server.crt -x509

Then copy the certificate file as root.crt.

cp server.crt root.crt

Modify the configuration files

Next, we modify the PostgreSQL config file at /etc/postgresql/10/main/postgresql.conf and turn on SSL. Also, we specify the certificate file.

The updated conf appears as:

root@ip-172-xx-yy-32:/var/lib/postgresql/10/main# grep "ssl = on" /etc/postgresql/10/main/postgresql.conf
ssl = on
root@ip-172-xx-yy-32:/var/lib/postgresql/10/main# grep "ssl_ca_file" /etc/postgresql/10/main/postgresql.conf
ssl_ca_file = 'root.crt'

Moving on, we modify the authentication method file available at /etc/postgresql/10/main/pg_hba.conf

We add the authentication option clientcert=1 to the appropriate hostssl line in pg_hba.conf. By this method, a certificate will be requested from the client during the SSL connection startup.

# IPv4 remote connections for authenticated users
hostssl all webadmin 0.0.0.0/0 md5 clientcert=1

Restart PostgreSQL server

Finally, we restart the PostgreSQL service.

service postgresql restart

At the client-side

Then, we copy the server certificate, key files, and root cert to the client computer. Thus, all the connections from PostgreSQL clients like pgAdmin will become secure.

The settings on pgAdmin 4 interface look like

Or if the server does not have SSL, an easy fix is to update the connection string to include sslmode=disable. This resolves the error. However, the connection will not be secure and hence not recommended.

[Need help in securing PostgreSQL connections? We are available 24×7]

Conclusion

In short, error Postgres SSL is not enabled on the server happens due to incorrect SSL settings. Today, we saw how our Support Engineers enable SSL connection on the PostgreSQL server.