My thoughts about IT and infosec.

PostgreSQL and SSL/TLS

Encryption of data in transfer is not only important for web servers and mail servers. You can also use SSL/TLS on database servers like PostgreSQL. This will make the usage of an SQL server on the internet a little bit more safe.

As a regular PostgreSQL user I sometimes have to lookup some settings to make PostgreSQL available over an SSL/TLS connection. Now it’s time to share some notes about this.

To allow the usage of TLS/SSL on the PostgreSQL database server, we have to edit some configuration files.


First edit the postgresql.conf file.

vim /etc/postgresql/9.6/main/postgresql.conf

Here we add the following configuration:

# - Security and Authentication -
ssl = on
ssl_ciphers = 'AES128+EECDH:AES128+EDH'
ssl_prefer_server_ciphers = on
ssl_cert_file = '/etc/postgresql/cert/cert.crt'
ssl_key_file = '/etc/postgresql/cert/private.key'
ssl_ca_file = '/etc/postgresql/cert/cacert.crt'
password_encryption = on

If you want to access the PostgreSQL server from a remote server, you also have to allow PostgreSQL to listen on the IP addresses on the network interface.

listen_addresses = '*'


Be sure that you set the right file system permissions on the certificate files.

chown postgres:postgres -R /etc/postgresql/cert
chmod -R 700 /etc/postgresql/cert
chmod -R 600 /etc/postgresql/cert/*


When you are connecting from a remote system, you also want to add an extra line in the pg_hba.conf configuration file like this:

# My custom hosts (change your IP address here)
hostssl   all        all md5

Create a user and database

Now you can login from a remote host using SSL/TLS. Let me explain quickly how to create a user, database and a password to test the connection:

Create a PostgreSQL user:

sudo -u postgres createuser testuser

Create a PostgreSQL database:

sudo -u postgres createdb testdb

Connect to PostgreSQL, create a password and give the test user the right privileges to the database.

sudo -u postgres psql
SQL commands:

psql=# GRANT ALL PRIVILEGES ON DATABASE testdb TO testuser ;

Test the connection

Now, test the connection:

sebastian@desktop:~$ psql -h --u testuser -d testdb -W
Password for usertestuser: 
psql (9.6.6)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES128-GCM-SHA256, bits: 128, compression: off)
Type "help" for help.


Here you can see that you can connect with SSL (Protocol TLSv1.2).

That’s it! Feedback is welcome!

2018-01-31 17:32
#ssl #tls #postgresql