Secure PostgreSQL : Patroni, Etcd, Pgbackrest Included

Step by step guide for securing your Postgresql Database Environment

In today's world, keeping data secure is absolutely crucial. As companies depend more and more on databases to handle all sorts of sensitive information, making sure those databases are locked down tight is a top priority.

Ensuring the overall security of PostgreSQL is a multifaceted task that involves implementing various measures to protect the database from potential threats. Many measures may be taken in every layer of the database environment. I will explain some in detail and summarize others in one sentence.

Lets start with the database itself.

Secure PostgreSQL Database:

1. Authentication and Authorization : Use RBAC (role based access control), different schemas for apps, use advanced authentication methods like LDAP, Kerberos, and client certificate authentication.

2. Encryption : Utilize encryption for both data in transit and data at rest. There are some encryption options in PostgreSQL but not at file level. Securing data at rest is generally achieved through Transparent Data Encryption (TDE). TDE is available on EDB Postgres, as of May 2024, but not yet available on the community edition. Percona is also developing an open-source extension, pg_tde, for this purpose, but it still has a long way to go.

     - Securing data at transit is accomplished with using SSL certificates, I will provide a quick implementation. Since my organization has its own certification authority, I do not use self-signed certificates. Please prepare your request according to your server's DNS names and also the DNS name used for the load-balancing solution (such as HAProxy, F5 etc.) that clients are using to connect your patroni cluster.

3. Auditing and Logging: Enable auditing (pgaudit)  and logging features in PostgreSQL to monitor database activity and detect potential security breaches. This includes logging database events, such as login attempts, queries, and modifications, as well as setting up audit trails to track changes to sensitive data.

4. Parameter Hardening: PostgreSQL provides a wide range of configuration parameters that can be adjusted to enhance security. Administrators should review and adjust these parameters according to best practices and security requirements. This includes settings related to authentication, encryption, resource usage and connection settings. For example, use scram-sha-256 for password_enryption method , use a non standard port for connections different than 5432.

5. Patch Management: Keep PostgreSQL up to date with the latest security patches and updates and regularly monitor for new releases and apply patches in a timely manner to ensure the database remains secure.

6. Network Security: Secure the network infrastructure surrounding PostgreSQL database. This includes implementing firewalls, intrusion detection/prevention systems, and network segmentation to limit access to the database server and prevent unauthorized connections.

7. Backup and Disaster Recovery: Implement robust backup strategies and disaster recovery procedures to ensure data availability and integrity in the event of a security incident or system failure. I strongly advise to use pgbackrest as a backup solution.

By addressing these key aspects of PostgreSQL security, organizations can create a strong defense against potential threats and safeguard their valuable data assets. It's important to approach security as an ongoing process, regularly reviewing and updating security measures to adapt to evolving threats and vulnerabilities.

Secure Patroni:

Running a single instance of PostgreSQL may not suffice for high availability and fault tolerance requirements. Hence, organizations often opt for a clustered approach, where multiple instances of PostgreSQL are synchronized to ensure data redundancy and availability. Here comes the Patroni, a powerful tool designed to automate PostgreSQL high availability and failover management. Patroni simplifies the deployment and management of PostgreSQL clusters by orchestrating tasks such as leader election, replication, and automatic failover. 

There are also some important steps should be taken to secure patroni REST APIs. Such as using non-default ports, using SSL and password authentication for unsafe RESTAPI endpoint calls, limiting access to Patroni REST APIs . Here is a short implementation provided below.

Once you've configured Patroni REST APIs to utilize SSL, it's essential to also set up your load balancing solution (such as HAProxy or F5) to communicate with Patroni REST APIs securely over SSL. Below, I've included a sample configuration for your reference. To ensure proper functionality, you'll need to add the 'verify none' and 'check-ssl' attributes to your existing configuration. Note that for the 'check-ssl' attribute to work, HAProxy must be compiled with the OpenSSL library during installation. You can achieve this by executing the following command during installation: 'make TARGET=linux-glibc USE_PCRE=1 USE_OPENSSL=1 USE_ZLIB=1 USE_CRYPT_H=1 USE_LIBCRYPT=1'. If your current installation does not include OpenSSL, you'll need to reinstall HAProxy after compiling it with the 'use_openssl=1' option.

Additionally, there is a notorious Authenticated Remote Code Execution (RCE) vulnerability, which allows remote attackers to execute arbitrary code by modifying the PostgreSQL configuration file through the Patroni HTTP REST API. Caution is required in dealing with this vulnerability. Considering that the local configuration section of the patroni.yml file always takes precedence over global configuration, it's important to note that OS-related parameters (restore_command, archive_command) provided below should be set in the local configuration section of the Patroni configuration against this vulnerability.

Secure Etcd:

To further enhance the reliability and security of the PostgreSQL cluster, etcd is employed as a distributed key-value store. Etcd serves as the centralized configuration store for Patroni, storing critical information such as cluster state, configuration parameters, and leader election metadata. By utilizing etcd, Patroni ensures consistency and coordination across the PostgreSQL cluster, enabling seamless failover and recovery procedures.

Etcd's endpoints also should be configured with SSL and non-default ports should be used. You may configure it as follows:

Secure Pgbackrest:

PGBackrest is a top-notch backup and restore tool for PostgreSQL databases. It's known for its speed, efficiency, and versatility, offering features like parallel processing and incremental backups. PGBackrest operates independently, making it easy to manage backups in diverse environments. It's a must-have for PostgreSQL database administrators looking for reliable data protection.

Securing PGBackrest endpoints with TLS is crucial for protecting sensitive data during backup processes. TLS encryption ensures that communications between PGBackrest and clients are secure, preventing unauthorized access and tampering. By implementing TLS, you safeguard your data integrity, maintain confidentiality, enhance overall security, and comply with regulations. Additionally, enabling TLS can increase the speed of backups. It's worth noting that TLS 3.0 is supported starting from pgBackrest version 2.37 and later.

I utilize a separate dedicated server for the pgBackrest repository. Below are the steps you can follow to enable TLS on pgBackrest.

Having ensured the comprehensive security of the entire PostgreSQL database environment, the DBA can now enjoy a well-deserved peace of mind.

Hope it helps.