How Secure is your Postgres? Part 1: Access

Dave Page July 1, 2020

How Secure is your Postgres?

This is the first of a series of blog posts that will help you examine the security of your Postgres deployment from end to end. The vast majority of the discussion will focus on features, functionality and techniques that apply equally to both PostgreSQL and EDB Postgres Advanced Server (EPAS), however it will also touch on a couple of features that are only available in EPAS. These will be clearly noted. At the time of writing, the latest version of Postgres available is 12.3.

In this first part, we will look at how the server is connected to and accessed. As with any security configuration, follow the principle of least privilege when considering how to configure your system; that is, only allow as much access as is required to implement a working system, and no more.

In the following parts, we'll also look at authentication, roles, data access control, and encryption.

 

Physical Access

It can be extremely difficult to prevent someone with physical access to a server from gaining access to the data, but there are a number of measures that can be taken, both physical and technological.

First and foremost, the physical access should be limited as much as possible, by ensuring the server is located in a secure facility. This may be a privately owned server room, in which case measures can be taken to ensure that only authorized personnel can enter the room and that monitoring such as CCTV is employed. In the case that a co-location facility is used, ensure that the chosen provider has a strictly enforced security policy appropriately designed to prevent unauthorized access, and in facilities that allow users to enter, that locking racks and cages are available to keep other customers away from your hardware.

There's little that can be done in this regard with the major cloud providers, other than to trust that they do implement the high levels of physical security that they claim, however for both them and co-location facilities it is essential to check that they have appropriate documentation attesting to the level of security they provide, such as SOC 2 or 3.

 

Connecting

There are two ways to connect to a Postgres server; via a Unix Domain Socket or a TCP/IP Socket.

Unix Domain Socket

Unix Domain Sockets (UDS) are the default method for connecting to a Postgres database on Unix-like platforms. On Windows they are not available at present, but will be in Postgres v13 and later.

UDS are only accessible from the machine on which they are present (and therefore are not subject to direct remote attacks), and appear as special files on the file system. This means that access to them is subject to the same access controls as other files (though, only write permission is actually needed to use the socket), and can be controlled by managing the permissions and group ownership of the socket through the unix_socket_permissions and unix_socket_group configuration options, as well as the permissions on the directory in which the socket is created. Sockets are always owned by the user that the Postgres server is running as.

To offer even more flexibility, Postgres can create multiple sockets (though by default, only one is created) using the unix_socket_directories configuration option, each of which directories can have different permissions as required to segregate different users or applications and help to apply the principle of least privilege.

If your application is running on the same host as the database server, give serious consideration to allowing access to the server via one or more UDS only.

TCP/IP Socket

If you need to access your Postgres server from a remote system, as is often the case when implementing applications with multiple tiers or services, or just for remote administration using tools such as pgAdmin, you will need to use a TCP/IP network socket.

As is generally the case when it comes to security, we want to minimize the potential attack area for anyone attempting to gain access to the system. How this is done depends on how the server is hosted on the network. If it's inside a corporate network, it may be hosted on multiple VLANs or physical networks, which can be used for different purposes, such as applications, management and storage access for example. The system should only be configured to listen for and accept connections on the networks that are actually required; by default, a source code build of Postgres will listen only on the localhost or loopback address which prevents connections from other machines, however some pre-packaged builds of Postgres override this so you should check your installation. Use the listen_addresses configuration parameter in postgresql.conf to ensure Postgres only listens and accepts connections on the required network addresses, thus preventing access from, say, the storage network.

 

Firewall

Firewalls are an important tool to prevent access to network ports from unauthorized sources. Many also offer logging facilities which can be used as part of a broader initiative to proactively detect intrusion attempts to help mitigate them before they are actually successful.

Local Machine

Most modern operating systems include firewalls, including the Windows Defender Firewall on Windows, and iptables on Linux, plus there are also a number of third party products you might choose.

Typical firewalls will allow you to define inbound and outbound rules that specify the traffic that is allowed. These rules will consist of a number of common parameters;

  • The protocol, e.g. TCP or IPv6
  • The local port, e.g. 5432 (the default port for PostgreSQL)
  • The source address, i.e. where the connection attempt is coming from.

Some firewalls offer additional options to give far greater flexibility; for example, Windows Defender Firewall allows you to specify a program instead of port number.

As always, we want to minimize access to Postgres so it would be quite normal to create a rule for TCP (and/or IPv6) traffic arriving on port 5432 to be rejected (or black-holed) unless it's coming from the address of our application server. The source address can usually be a list of addresses or subnets.

If your server has any Foreign Data Wrappers or similar extensions installed, it may also be desirable to create outbound rules to prevent them being used to connect to anything other than a predefined set of servers.

Whilst configuring Windows Defender Firewall is quite straightforward, configuring iptables is much more difficult. Linux distributions such as Redhat and Ubuntu offer management tools to make this easier, and there are also other Open Source tools available such as Ferm and Shorewall.

Minimize access to your server as much as possible through the use of a firewall.

Cloud Provider

Most cloud providers recommend against using firewalls in virtual instances, suggesting instead that users make use of the firewalls built into the platform. This typically makes management much easier, allowing rule sets to be created that can be reused and attached to multiple servers, and allowing management through their web and command line interfaces and REST APIs.

Firewalls at the cloud providers are implemented as part of their network infrastructure, and generally work in much the same way as the host firewalls described in the previous section; i.e. specify the source addresses, protocol and destination port for traffic to allow.

Most cloud providers also offer Virtual Private Clouds (VPC), in which a number of servers can coexist in a single virtual environment with its own private network or networks. This type of configuration has typically become the default and makes it very easy to deploy a multi-tiered system on the public cloud, whilst keeping the non-public tiers segregated from the internet in general. The use of multiple subnets within a VPC can make it easy to further segregate servers, keeping the public tiers in a "DMZ" subnet, with only minimal access to the database servers that are in a private subnet with no direct internet connection.

Minimize access to your server as much as possible through the use of a firewall.

 

Transport Encryption

If traffic to the database server is flowing across the network, it is good practice (arguably essential practice) to encrypt that traffic. Postgres uses OpenSSL to provide transport security - though work has been underway for some time to add support for Microsoft Secure Channel or Schannel and Apple Secure Transport - through the use of TLS (previously SSL).

To encrypt connections in Postgres you will need at least a server certificate and key, ideally protected with a passphrase that can be securely entered at server startup either manually or using a script that can retrieve the passphrase on behalf of the server, as specified using the ssl_passphrase_command configuration parameter. Passphrases are not supported on Windows, at least as of Postgres 12. The server certificate and key are specified using the ssl_cert_file and ssl_key_file respectively.

If you have an existing Certification Authority (CA) in use you can use certificates provided from that with Postgres. The configuration parameters ssl_ca_file and ssl_crl_file allow you to provide the CA (and intermediate) certificates and the certificate revocation list to the server. This gives you the flexibility to revoke certificates in response to security incidents, and have the server reject client certificates or the client reject server certificates. It also allows you to configure the client and server to reject each other if the identity of either cannot be verified through the chain of trust to prevent as-yet undetected spoofing. Part two of this blog series will go into more detail on the use of certificates for client authentication.

It's important to ensure that your use of TLS is secure as well. There are a number of configuration parameters that can be set to ensure that you're not using ciphers or other options that may no longer be considered secure. It is recommended that you check and appropriately configure the following configuration parameters in your postgresql.conf configuration file:

  • ssl_ciphers
  • ssl_ecdh_curve
  • ssl_dh_params_file
  • ssl_min_protocol_version

No recommendation is made in this article on what those parameters should be set to as inevitably they will change over time. You should periodically check to ensure you're using options that continue to be regarded as secure, and update them when appropriate.

If traffic to your server flows over the network, ensure it's encrypted using the strongest possible ciphers and other options.

 

Conclusion

In the first part of this five-part series of blog posts we looked at a number of factors related to server access that can affect the security of your Postgres servers and should be considered as part of any deployment or review:

  • Physical access
  • Server access via Unix Domain Sockets and the network
  • Firewalls
  • Transport encryption

Stay tuned for Part 2, in which we will look at client authentication; how we authenticate users and control whether or not they can connect to the server successfully through the pg_hba.conf configuration file.

Want to learn more? Check out EDB Postgres Advanced Server, which extends PostgreSQL with the security and performance features that enterprises need.
 

Dave Page

Dave has been actively involved in the PostgreSQL Project since 1998, as the lead developer of pgAdmin, maintainer of the PostgreSQL installers and one of the projects resident Windows hackers. He also serves on the project's web and sysadmin teams and is a member of the PostgreSQL Core Team. Dave is employed by EnterpriseDB where he works as a software architect and developer on EnterpriseDB products in addition to his community PostgreSQL work.