Recently, Singapore faced the ‘most serious breach of personal data’. In this attack a total of 1.5 million SingHealth patients’ non-medical personal data was stolen and 160,000 outpatient dispensed medicines’ records were compromised. There were few government officials among those who were targeted. Similar incidents have been heard from across the globe.
As a part of my role, I interact with customers for key open source technologies including PostgreSQL – a relational database technology. Security is one of the key focus areas in my pre-sales and post-sales discussions but now discussions around security of data and database technology has become even more prominent.  Customers that include banks, insurance companies, telecom service providers, government agencies are increasingly asking how to avoid such incidents and tighten the security in their own environments. These customers are interested to know how secure is PostgreSQL and what more could be done to tighten security of their data.
I thought of sharing some best practices in tightening security of PostgreSQL database. If you look at the some of the standard features of PostgreSQL you will realise that “By default, PostgreSQL is possibly the most security-aware database”. There are also some important practices that users should pay attention to secure their database environment.
The purpose of this article is not to highlight any shortcomings in the security measures taken by the team managing SingHealth database nor is it an attempt to highlight any features lacking in the choice of database used for SingHealth. The article primarily outlines how one can avoid/prevent a similar attack if one is using PostgreSQL.
Risk of not upgrading is higher than risk of upgrading
Very often, I have been asked by customers about the frequency of releasing patches by the PostgreSQL community and EnterpriseDB. Somehow, the lower frequency of security patches release is considered a very strong indicator of how mature the technology is and how enterprise ready the vendor is. I find this extremely naïve on the part of database admin community and security analysts. They have been conditioned to accept the release cycle and bug fix frequency imposed by commercial vendors as an industry practice. And it is simply not a correct or best practice. It is actually very mature on part of PostgreSQL community or for that matter any open source community or technology vendor to acknowledge serious security bugs well in time. Also since PostgreSQL is open source, the source code is out there for review by hundreds and thousands of coders and reviewers to point out a flaw. Whereas it is not unheard of commercial vendors to refute the right to reverse engineer their commercial/packaged product for review purpose.
As with any other product, you should apply patches to database as soon as you can. PostgreSQL community has a very robust and structured process of creating and releasing patches. By leveraging Streaming Replication in PostgreSQL, you can have a setup with 1 or more standby and perform rolling upgrade to minimize downtime. You can further automate this process by combining a clustering tool like EnterpriseDB Failover Manager and automation tool like Ansible and achieve virtually zero downtime during upgrades.
Principle of least privilege
Don’t grant DBA access to users used in application configuration. Allow only minimum privilege and access to only specific tables for users which are used by other applications. Give only the privileges which are needed e.g. application owner/users don’t need privileges to create table. PostgreSQL community is continuously evolving the security model and adding new type of roles and access to align with least privilege model.
Secure the connection
Applications that are external facing should consider using TLS/SSL communication for all outgoing communication. Additionally, in a lot of instances data can be stolen by setting up a network sniffer within your own intranet. So, it is a good practice to have TLS/SSL communication between database and database clients e.g. application. There are different levels of strictness that Postgres offers, verify-ca is the most strict one and covers against most common attacks.
Also as a basic security measure, you should not expose database service on all available interfaces and should configure it to listen on a non-default port.
Authentication
You should implement strong authentication mechanism e.g. SCRAM. You should also consider using password profile and block a login after few failed attempts (which potentially means someone is trying to brute force their way into your system). EnterpriseDB provides password profiles in EDB Postgres Advanced Server to manage passwords and user login. PostgreSQL community users should consider using LDAP authentication and manage password profiles in LDAP/Active Directory.
Restrict the connections
You should take care to ensure that only authorized servers and desktops (e.g. those that belongs to the DBA) can connect to your database server. This can be ensured at network firewall level or at host firewall level. Additionally with PostgreSQL, by default all external connections are blocked. Unless you consciously allow a specific IP, database and username combination, all incoming connections are blocked by Postgres’ host based authentication. You can define the authentication rules in pg_hba.conf to control who can connect and who cannot. This is a great tool for DBAs. In large enterprises and government agencies, a DBA would not have access to host or network firewall rule. But they can still manage the restrictions at their end by means of pg_hba configuration. This is an out of the box feature which is available in PostgreSQL and makes it enterprise ready. An additional level of security can be added with SQL/Protect provided by EnterpriseDB which allows you to restrict SQL statements which are not authorized e.g. DDL statements, SQL Tautology and Unbounded DML.
To conclude I would say, a lot of people carry the notion that PostgreSQL is open source and hence is less secure. They tend to believe that PostgreSQL by default is less secure than a proprietary database like Oracle or SQL Server. As we have discussed in this article PostgreSQL has all the security features which usually any enterprise grade database would need. These security features are further augmented by additional add-ons offered by the likes of EnterpriseDB, to secure your data and restrict access to it. Today, EnterpriseDB Postgres and PostgreSQL is trusted by financial institutes like MasterCard, IAG, Rabobank and ABN Amro. All these institutes deal with financial data and personal information. Our team had worked with NTUC Link to setup Postgres Advanced Server as the backend of their Campaign Management system. NTUC Link has one of the largest databases of Personal Information in Singapore and they chose to trust Postgres to store it. That is a great testimony to security features available in Postgres. If you are evaluating alternatives to Oracle or SQL Server, do feel free to drop a note about your requirements and what kind of help you would need to success@ashnik.com. We have a team of skilled and certified PostgreSQL professionals and have worked with some of the largest banks, insurance firms and telco in the region.