When big high-tech companies like Apple, Red Hat and Cisco use PostgreSQL in their data infrastructure, you can be certain that PostgreSQL ranks up there with the bigger relational database managers. PostgreSQL is an enterprise-level open source database that has been in production for over 15 years. The database is designed for high volume data environments and utilizes a multi-version model that, unlike the traditional way of using locks, allows for concurrent transaction updates on data rows providing transaction isolation per database session. This is unique because locks can be obtained for querying at the same time there can be locks for updating the data within the same table. Another great feature to PostgreSQL is that, being open source, there are no licensing costs associated with the deployment of the database. Whether PostgreSQL is installed in development or production environments, companies can control where and how many databases they want to deploy. Couple that with the strong support of the community of PostgreSQL users and you get a reliable and stable product that is evolving faster and smoother than many in the realm of database managers.
But what about properly securing your PostgreSQL database? There are many ways you can go about securing a PostgreSQL database. I'm going to highlight a few tips that I feel are important and essential to preventing unauthorized access into your data environment.
First and foremost, make sure you place the PostgreSQL database behind your corporate firewall. I know, this is rudiment and practically a no-brainer, however, there is the occasional security vulnerability that slips through from time to time. Not even the best written software is perfect. By placing the firewall in front of the database, you are limiting access from the public internet and providing the first layer of security. While you are setting up the database behind your firewall, go ahead and change the default port. PostgreSQL is set up to use port 5432 straight out of the box. When you change the port to something more obscure, you lower the chances that an attacker will find a PostgreSQL database if they execute a network scan.
Second, PostgreSQL goes the extra step when restricting access to their databases. They use a feature called Host Based Authentication (HBA). HBA is a way of explicitly filtering out who can access which database using a specified authentication method. To implement this feature, you modify the pg_hba.conf file indicating values for each of the following:
Host: determines where the client is connecting from.
local: indicates a connection made over Unix domain sockets
host: pertains to a standard connection over a TCP/IP network
hostssl: is the same thing as 'host' just over an SSL secured TCP/IP network.
Database: specifies which database can be connected. Note that the keyword all can be used but is highly discouraged since any database could be connected to under this rule.
User: specifies which user(s) can be used to connect. Note that the keyword all can be used but is highly discouraged since any or all users could be connected to under this rule.
Address: states where or which address can be connected through. It can either be a hostname, an IP range, a domain, or certain reserved keywords such as:
all: matches any address
samehost: matches to any of the same server's IP addresses
samenet: matches any address the server is directly connected to
As stated above, using all is discouraged since any IP address would be able to connect.
Method: way of authenticating the connection. Method has many options to use when it comes to verifying the identity of the database user. Here are some options that I prefer using:
md5: requires the user to supply a md5 hashed password
ldap: authenticates using an LDAP server
radius: authenticates using a RADIUS server
cert: authenticates using SSL client certificates
Included in these options are some that I would recommend not using in production environments:
trust: this option allows connection unconditionally
password: authenticates by allowing the user to enter an unencrypted password
To ensure you are disallowing unauthorized access to your PostgreSQL database I recommend placing the following rule at the bottom of your pg_hba.conf file:
host all all all reject
I know, that above I was suggesting never to use all because it too ambiguous, but in this case, when paired with the reject method it is working in an opposite fashion. Since the pg_hba.conf file is read from the top, having this rule at the bottom of the configuration file will ensure that no other connections are made/authorized that weren't define above it. This is typically referred to as a default deny rule.
Another recommendation, that is slightly similar to using the pg_hba.conf file, is utilizing the listen_addresses parameter. This parameter specifies the TCP/IP address(es) the server can listen for client connections. Unlike using HBA, listen_addresses controls which interfaces accept the connection attempts. Initially the parameter's value defaults to localhost, but I also recommend not using '*', '0.0.0.0', '::' as these are wildcard values for any address, IPv4 addresses, or IPv6 addresses respectively.
Once you've figured out who can and cannot connect, my final recommendation would be to safeguard the control access inside your PostgreSQL database. PostgreSQL has a few super roles that almost guarantee everything but the kitchen sink. It's these super roles that should be constantly reviewed for unauthorized access. For most cases the roles: SUPERUSER, CREATEROLE, and CREATEDB, should be restricted to the dba/admin user. Only in certain instances should any other user/role account be granted these roles. I use user/role because, in PostgreSQL, they are the same thing. There is no differentiation between the two like in other RDBMs, so when creating a user, you are creating a role. This can be confusing if you are new to PostgreSQL, and making sure that unwanted access to certain database objects can become confusing as well.
I would also suggest using the principle of least privilege. This is defined as granting/promoting the minimal access needed for a user inside of a database. Keeping constant vigilance when it comes to reviewing database objects is key when trying to protect your data from unwarranted access. Understanding that permissions can be granted within the database level, schema level, or table/view level within PostgreSQL can also aid in protecting your data.
Row-level security (RLS) is also available providing an additional layer of protection. By enforcing it on certain columns inside a table, you can be in control of which rows that can be returned through queries, or modified using data modification language (DML). To institute row-level security, you start by altering the intended table and enabling RLS. Once it's enabled, you create certain policies that help dictate which columns RLS is applied to.
To learn more about Row-level security, please visit this link: PostgreSQL Row-Level Security
As a database security professional, I encourage you to evaluate your security posture when it comes to databases and ensure that you are protected from outside (and inside) attacks. In addition to this post, Trustwave has tools available that will perform database scanning and provide a current snapshot of your security. By following a few simple procedures, you are saving yourself countless hours of unneeded work and, in the end, saving your employer revenue.
