In the first part of the article, we discussed how to achieve minimum security by adding a username and password to Patroni REST API and switching from HTTP traffic to HTTPS. In this article, we will be covering how to secure REST APIs using certificate authentication.
Certificate authentication is the only option if we want to prevent everyone else from accessing the “Safe” endpoints, which otherwise don’t require any credentials.
Enabling the client certificate verification
If we plan to use certificate authentication, the “password authentication:” section mentioned in part one is no longer required. Instead, we can just specify:
1 | verify_client: required |
The overall restapi section of the patroni configuration will look like this:
1 2 3 4 5 6 7 8 9 | … restapi: listen: 0.0.0.0:8008 connect_address: pg1:8008 certfile: /etc/patroni/pg1.crt keyfile: /etc/patroni/pg1.key cafile: /etc/patroni/ca.crt verify_client: required … |
Now the client certificate will be verified by the REST API server of Patroni. So the patronictl should make the REST request with the appropriate certificate, which can be verified by the server.
The information required for the patronictl needs to be specified under ctl :. For example:
1 2 3 4 5 | ctl: insecure: false certfile: /etc/patroni/pg1.crt keyfile: /etc/patroni/pg1.key cacert: /etc/patroni/ca.crt |
Once the client certificate verification is enabled, every client should make an HTTPS request with a valid certificate to get a proper response from Patroni REST API:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | curl -k https://pg1:8008/cluster --cacert ~/ca.crt --cert ~/app.crt --key ~/app.key | jq . % Total % Received % Xferd Average Speed Time Time Time Current Dload Upload Total Spent Left Speed 100 302 0 302 0 0 2202 0 --:--:-- --:--:-- --:--:-- 2220 { "members": [ { "name": "pg0", "role": "leader", "state": "running", "api_url": "https://pg0:8008/patroni", "host": "pg0", "port": 5432, "timeline": 46 }, { "name": "pg1", "role": "replica", "state": "running", "api_url": "https://pg1:8008/patroni", "host": "pg1", "port": 5432, "timeline": 46, "lag": 0 } ] } |
HAProxy with certificates
HAProxy could be the most widely used connection router with Patroni.
Unlike password authentication, certificate authentication is not transparent to HAPproxy. Every REST API communication expects valid certificates. So the HAproxy (or any router) needs to go with proper certificates for its health checks.
As per the HAProxy documentation, it should be using a combined certificate which includes the certificate and its key. A combined certificate can be prepared by concatenating the files.
1 | cat app.crt app.key > combined.crt |
Now we can specify this certificate and CA certificate for the checks in the haproxy configuration file (haproxy.cfg). The same needs to be specified for each of the candidate patroni nodes as follows:
1 2 3 4 | … server pg0 pg0:5432 maxconn 100 check check-ssl verify none port 8008 crt /etc/haproxy/combined.crt ca-file /etc/haproxy/ca.pem server pg1 pg1:5432 maxconn 100 check check-ssl verify none port 8008 crt /etc/haproxy/combined.crt ca-file /etc/haproxy/ca.pem … |
A complete sample HAproxy configuration file is available on GitHub for easy reference.
Important points to remember
1. Safe API without certificate verification
We have the option to allow “Safe” API without certificate verification. This can be done by changing the “verify_client” to “optional” instead of “required”.
1 2 3 4 5 | … restapi: … verify_client: optional … |
2. Why is certificate authentication needed at all?
Generally, it will be a bit overkill because Patroni’s API cannot leak any data from the database. It could be sufficient to protect it by username and password. One of the reasons why certificate authentication may be considered is that the “Safe” endpoints (refer to the previous post) will be available without authentication, which can reveal the current topology of the cluster.
Moreover, some organizations insist on blanket “certificate authentication” everywhere.
3. How to change the certificates?
The SSL certificate will have an expiry, and every organization using the certificates needs to have a fool-proof mechanism to renew the certificate periodically before the one in use expires. In older versions of Patroni, the certificate changes require Patroni service restarts. This can be achieved after the switchover. However, a switchover will affect the existing connections to the primary.
New versions of Patroni (Patroni version 2.1.0 or later) can reload the certificates without restarting the Patroni service. Reference PR
With this improvement, we just need to signal the Patroni service with SIGHUP.
It will be a good idea to add SIGHUP to Patroni Service (systemd) so that any certificate change on the node can be triggered by a service reload which sends SIGHUP to Patroni. A sample file is available on GitHub.
4. A word of caution
The more complexity, the more the problems. Patroni being the High Availability framework, additional complexities can potentially lead to unavailability. For example, a certificate expiry can lead to failure in REST API communication, which, internally, Patroni and patronictl themselves are using. So I would recommend assessing the risk and additional complexity before implementing. The simpler, the better.
Unless your organization has a good system and procedures for managing the certificates and keys, staying away from complete certificate authentication can be beneficial.
Excelent. But some cuestions. For context, in the example above, it mentions that a certificate is used for Login using a framework. In this case, i think there is only one certificate for all the aplication. Is true?. How to force each user to use certificate to authenticate using their own?. This approach makes all users responsable for control of the certificate and access to the system, and is resposability of the application maintain most aspects for security. And More, How to enable this behavior in the database Mysql or Postgresql without framework and with the framework?
Thaks for share.
Hi Javier,
Thank you for letting me know that you find the article useful.
The idea is to have every entity have its own certificates. For example, each node of the cluster or application server can have its own certificate, which can be verified by the Patroni. The easiest approach is to have the same CA sign all the certificates.
When it comes to user authentication at the database level, the CN of the signed certificate should match the username.