Home Verhalen Uitleg Archief

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.

postgresql.conf

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 = '*'

Permissions

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/*

pg_hba.conf

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    12.34.56.78/32 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
psql=#
SQL commands:

psql=# ALTER USER testuser WITH ENCRYPTED PASSWORD '';
psql=# GRANT ALL PRIVILEGES ON DATABASE testdb TO testuser ;

Test the connection

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!

Gerelateerd

2018-01-31 2 minuten leestijd Sebastian ssl tls postgresql ssl tls postgresql