The General Data Protection Regulation (GDPR) is a regulation to strengthen and unify the data protection for people living in the European Union. It will become mandatory in May 2018 for all companies working with personal data. So it’s time to take a look at some security related features that PostgreSQL offers to ensure compliance with the new regulation. In a series of blog posts i will therefore take a close look at these features and their practical use cases.
Let’s start with the connection between the Clients and the PostgreSQL Server. To secure this transport layer, PostgreSQL offers native Support for SSL. By enabling SSL, all data passed between Client and Server is encrypted and secured against eavesdropping.
You might think now why should i care, i have my own completely isolated on-premise Infrastructure and therefore I’m secure against any kind of eavesdropping and sniffing attacks. And you are right. When you are 100% sure that your Network Infrastructure is secure against these kind of attacks you don’t have to read any further. But as soon as data crosses network borders or flow through network devices that are under the control of a third party (hosting or cloud provider) you still have to think about encryption. It is the personal data of your users that would be passed unencrypted through the network and it is your job to protect it. It doesnt‘ matter if you use Postgres SSL Feature like described in this Post or an encryption like VPN or SSH Tunnel for it, but you have to protect personal data, this is what GDPR is all about.
But let’s come back to Postgres.
To enable SSL in Postgres you need OpenSSL installed on the Database Server as well as on the client side of the connection. When installing a packaged version, SSL support is most probably already enabled. If you build it from source you need to supply the option –with-openssl to configure.
In addition you will need a certificate and a key file, either self-signed or signed by a CA.
If you are on a Debian or Ubuntu Box and installed Postgres from the Repository you already have a self-signed Certificate that you can use at least for testing. Otherwise you can create a both with this command from the Postgres docs.
openssl req -new -x509 -days 365 -nodes -text -out server.crt -keyout server.key -subj "/CN=yourdomain.com"
With a certificate and key in place you are now able to activate SSL in your postgresql.conf.
# turn ssl on
ssl = on
And tell Postgres were to find these files.
# path to your certificate
ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'
# path to your keyfile (need not to be protected by a passphrase!)
ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'
You can either specify a full or relative path to your $PGDATA Directory (/var/lib/postgresql//main is the default on Debian). The default location for these two files are $PGDATA/server.crt and $PGDATA/server.key. After these changes restart you Postgres instance and you should be able to connect to your database using SSL on the same as for non-encrypted connections. The default is TCP-Port 5432 another port maybe defined by the port Setting in postgresql.conf.
Let’s see what happens when we connect with psql.
$ psql -h localhost -U postgres
psql (10.1)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
Postgres is so gentle to present us with the SSL specify informations, like protocol and cipher direct after the login. You can receive the information not only by login in, but also by using \conninfo psql command.
postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "localhost" at port "5432".
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
If you use another client than psql the view pg_stat_ssl might become handy for you.
postgres=# select * from pg_stat_ssl where ssl;
pid | ssl | version | cipher | bits | compression | clientdn
------+-----+---------+-----------------------------+------+-------------+----------
3051 | t | TLSv1.2 | ECDHE-RSA-AES256-GCM-SHA384 | 256 | f |
The output contains one line per SSL-enabled connection with all SSL infos as well, but it only shows the active connections. If you want to have a long term view of the used ciphers or protocols you can activate connection logging.
log_connections = on
With this setting enabled, ALL connections (not only SSL-enabled) will be logged. A log entry for a SSL-enabled connection will look like this.
connection authorized: user=postgres database=postgres SSL enabled (protocol=TLSv1.2, cipher=ECDHE-RSA-AES256-GCM-SHA384, compression=off)
Upon now it’s up to the client if he talks secure to your server or not. You may want to enforce SSL, for this you have to modify the pg_hba.conf. In a default installation you will find a line like this:
host all all 127.0.0.1/32 md5
The first keyword host means, that encrytped and unencrypted connections are allowed. By changing it to hostssl you enforce SSL.
hostssl all all 127.0.0.1/32 md5
After reloading, Postgres will no longer accept non-SSL-connections.
You may have wondered why psql has already used an encrypted connection in the example above. The reason is simple, psql’s default sslmode is prefer which means: try a SSL connection, when it fails use an unencrypted connection instead. Specifying the SSL connection mode allows us to test if the server is now really accepting only secured connections.
$ psql "host=192.168.1.193 user=postgres sslmode=disable"
An alternative is to specify the setting in the environment variable PGSSLMODE.
$ PGSSLMODE=disable psql -h 192.168.1.193 -U postgres
With SSL disabled on client side, both commands should fail with an error similar to the following one. The important part is ‚SSL off‘ at the end of the message.
psql: FATAL: no pg_hba.conf entry for host "192.168.1.113", user "postgres", database "postgres", SSL off
So far so good, let’s come to the ciphers used by Postgres. The default ciphers string is:
ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL'
With these settings on a Debian 8 system, Postgres supports the SSL Protocol Versions: TLSv1, TLSv1.1 and TLSv1.2 but not the unsecure SSLv2 and v3. You can enhance the configuration to not allow Triple DES and Ciphers with less than 128 bits by specifying a cipher list like this.
ssl_ciphers = 'HIGH:!MEDIUM:!aNULL'
Generally any combination of ciphers supported by Openssl can be used in this configuration. Checkout the manpage of ciphers ‚man 1 ciphers‘ for details. Before activating this in production you should definitely test if all your client-applications can handle these cipher suites. To find yout what they use right now, the logging of SSL connections like described above can be useful.
For those who want to know exactly which ciphers are supported by a Postgres installation, you can do it with the tool ’sslyze‘ from: https://github.com/nabla-c0d3/sslyze. Here’s an example:
$ python3 -m sslyze --starttls=postgres --tlsv1 --tlsv1_1 --tlsv1_2 --hide_rejected_ciphers 192.168.1.193:5432
* TLSV1 Cipher Suites:
Preferred:
TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA ECDH-256 bits 256 bits
Accepted:
TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA ECDH-256 bits 256 bits
TLS_DHE_RSA_WITH_CAMELLIA_256_CBC_SHA DH-2048 bits 256 bits
TLS_DHE_RSA_WITH_AES_256_CBC_SHA DH-2048 bits 256 bits
TLS_RSA_WITH_AES_256_CBC_SHA - 256 bits
TLS_RSA_WITH_CAMELLIA_256_CBC_SHA - 256 bits
TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA ECDH-256 bits 128 bits
TLS_DHE_RSA_WITH_AES_128_CBC_SHA DH-2048 bits 128 bits
TLS_DHE_RSA_WITH_CAMELLIA_128_CBC_SHA DH-2048 bits 128 bits
TLS_RSA_WITH_CAMELLIA_128_CBC_SHA - 128 bits
TLS_RSA_WITH_AES_128_CBC_SHA - 128 bits
* TLSV1_1 Cipher Suites:
Preferred:
TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA ECDH-256 bits 256 bits
Accepted:
TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA ECDH-256 bits 256 bits
TLS_DHE_RSA_WITH_CAMELLIA_256_CBC_SHA DH-2048 bits 256 bits
TLS_DHE_RSA_WITH_AES_256_CBC_SHA DH-2048 bits 256 bits
TLS_RSA_WITH_CAMELLIA_256_CBC_SHA - 256 bits
TLS_RSA_WITH_AES_256_CBC_SHA - 256 bits
TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA ECDH-256 bits 128 bits
TLS_DHE_RSA_WITH_AES_128_CBC_SHA DH-2048 bits 128 bits
TLS_DHE_RSA_WITH_CAMELLIA_128_CBC_SHA DH-2048 bits 128 bits
TLS_RSA_WITH_AES_128_CBC_SHA - 128 bits
TLS_RSA_WITH_CAMELLIA_128_CBC_SHA - 128 bits
* TLSV1_2 Cipher Suites:
Preferred:
TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 ECDH-256 bits 256 bits
Accepted:
TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA ECDH-256 bits 256 bits
TLS_RSA_WITH_AES_256_CBC_SHA - 256 bits
TLS_DHE_RSA_WITH_CAMELLIA_256_CBC_SHA DH-2048 bits 256 bits
TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 ECDH-256 bits 256 bits
TLS_RSA_WITH_AES_256_GCM_SHA384 - 256 bits
TLS_DHE_RSA_WITH_AES_256_GCM_SHA384 DH-2048 bits 256 bits
TLS_DHE_RSA_WITH_AES_256_CBC_SHA DH-2048 bits 256 bits
TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384 ECDH-256 bits 256 bits
TLS_RSA_WITH_CAMELLIA_256_CBC_SHA - 256 bits
TLS_RSA_WITH_AES_256_CBC_SHA256 - 256 bits
TLS_DHE_RSA_WITH_AES_256_CBC_SHA256 DH-2048 bits 256 bits
TLS_DHE_RSA_WITH_AES_128_GCM_SHA256 DH-2048 bits 128 bits
TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA ECDH-256 bits 128 bits
TLS_DHE_RSA_WITH_AES_128_CBC_SHA DH-2048 bits 128 bits
TLS_RSA_WITH_AES_128_CBC_SHA - 128 bits
TLS_DHE_RSA_WITH_AES_128_CBC_SHA256 DH-2048 bits 128 bits
TLS_RSA_WITH_CAMELLIA_128_CBC_SHA - 128 bits
TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 ECDH-256 bits 128 bits
TLS_RSA_WITH_AES_128_GCM_SHA256 - 128 bits
TLS_RSA_WITH_AES_128_CBC_SHA256 - 128 bits
TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256 ECDH-256 bits 128 bits
TLS_DHE_RSA_WITH_CAMELLIA_128_CBC_SHA DH-2048 bits 128 bits
You get a list of all accepted ciphers ordered by protocol. The output of unsupported ciphers is suppressed by –hide_rejected_ciphers. You can also specify -sslv3 and -sslv2 to check an installation for unsecure protocols. The starttls=postgres is important when you want to test a Postgres instance, otherwise the mode ist regular which is used to test the HTTPS Encryption of Webservers.
SSL Encryption is important but it comes with a price tag. The additional security will be payed by a performance penalty you should be aware of.
A simple pgbench test reveals the differences. The file select_1.sql after -f contains the queries executed by pgbench.
In this case it is, you might guess it simple select 1.
SSL disabled:
$ pgbench "host=192.168.1.193 user=postgres sslmode=disable" -n -c 10 -t 1000 -f select_1.sql
transaction type: test.sql
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 1
number of transactions per client: 1000
number of transactions actually processed: 10000/10000
latency average = 1.066 ms
tps = 9376.500240 (including connections establishing)
tps = 9410.406065 (excluding connections establishing)
SSL enabled:
$ pgbench "host=192.168.1.193 user=postgres sslmode=require" -n -c 10 -t 1000 -f select_1.sql
transaction type: test.sql
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 1
number of transactions per client: 1000
number of transactions actually processed: 10000/10000
latency average = 1.294 ms
tps = 7726.476549 (including connections establishing)
tps = 7779.993606 (excluding connections establishing)
Pgbench simulates 10 Clients, each one firing up 1000 transactions. In this simplified test scenario the cost of SSL is a reduction of Transactions per second (tps) of about 18%. So you have to keep this in mind before activating this additional layer of security in production or consider a connection pooler like pgbouncer.
Further infos can be found here:
