A very popular standalone NoSQL database solution came under criticism about their security posture this week. It’s not the kind of publicity a database company – or any company for that matter – relishes. Although the vulnerability seems to have been less a problem with the core database than with insecure default settings, it’s worth remembering that, no matter what database you use, properly securing the database is an essential configuration step. So, it seemed like a good time to write up a few tips on how to properly secure your PostgreSQL deployment.
Tip No. 1: Don’t be dumb! Unless you have a very good reason for doing otherwise, put your Postgres database – and any other network-facing services you run – behind your corporate firewall. Even the best-written software will occasionally have security vulnerabilities that can be exploited merely by connecting to the port it runs on. So public-facing network services should be limited to those which absolutely must allow access from the public Internet. Normally, the database is not one of those.
Tip No. 2: Use the listen_addresses setting to prevent connections from networks that don’t require database access. Depending on your installation method, the default value of listen_addresses may be ‘localhost’, in which case the operating system will reject connection attempts from other servers before they reach PostgreSQL. If you’re using another setting but don’t actually need access to the database from any other server, consider setting listen_addresses back to ‘localhost’. While this is not as good as a firewall, it's pretty close.
Tip No. 3: Use pg_hba.conf to further restrict the ability to connect to the database. Using pg_hba.conf, you can force connections to the database to use SSL encryption, accept or reject connection attempts based on the IP address from which the connection originates or the target user and database. You can also control the type of authentication that must be completed to make the connection, choosing from a rich array of options like LDAP, RADIUS, PAM, Kerberos, SSPI, and GSSAPI. Restricting connections to specific IP blocks can provide a significant amount of additional security. Replication access should be locked down especially tightly, because such logins are often automated (and thus password-less) but provide a very high level of access to the database. It may be useful to lock down superuser access, too.
Tip No. 4: Use SQL-level permissions to control access to specific database resources. Don’t allow users to run as superuser unless absolutely required, and restrict logins – especially automated logins – to the minimum privileges they need to function effectively. Permissions can be granted or denied at the database level, the schema level, or the table level. You can even grant access to some but not all of the data using either column-level permissions. PostgreSQL 9.5 will, additionally, offer row-level security, which has been available in EDB’s database, Postgres Plus Advanced Server, since v9.1 was released in 2012.
EnterpriseDB has developed additional security enhancements for Postgres. In addition to row-level security via the DBMS_RLS package (sometimes called virtual private database), Postgres Plus Advanced Server includes a SQL injection prevention toolkit called SQL/Protect, server side procedural code obfuscation, and extra auditing features.
The security features of PostgreSQL, and of Postgres Plus Advanced Server, are top notch. As with any security features, they will only benefit your organization if properly configured and deployed. We hope that as Postgres continues to add new features that improve its ability to handle NoSQL workloads, it will increasingly be seen not just as a part of the corporate infrastructure, but as a tool that can be set up simply and quickly to enable agile development. We encourage our customers to use Advanced Server in precisely this way – but don’t be dumb: secure your development databases, too!
A study conducted by Forrester Consulting and on behalf of EnterpriseDB found organizations are struggling to manage standalone NoSQL solutions and want one database to support their structured, unstructured and semi-structured data. Go here to download the study, Relational Databases are Evolving to Support New Data Capabilities.
Robert Haas is Chief Architect, Database Server, at EnterpriseDB.