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.
# 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) # TYPE DATABASE USER ADDRESS METHOD hostssl all all 126.96.36.199/32 md5
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 psql=#
psql=# ALTER USER testuser WITH ENCRYPTED PASSWORD ''; psql=# GRANT ALL PRIVILEGES ON DATABASE testdb TO testuser ;
Now, test the connection:
sebastian@desktop:~$ psql -h postgresql.server.net --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. testdb=>
Here you can see that you can connect with SSL (Protocol TLSv1.2).
That’s it! Feedback is welcome!