How to Secure Your PostgreSQL Database - 10 Tips

PostgreSQL may be the world’s most advanced open source database, but its 82 documented security vulnerabilities per the CVE database also make it highly exploitable. Granted, the popular object-relational database is considered superior to others when it comes to out-of-the-box security, but proper measures are still required to protect web applications and underlying data. The following are 10 common ways to secure your PostgreSQL implementation from cyber attacks.

1. Do Not Use Trust Security.

When using Trust security, PostgreSQL assumes that anyone connected to the server is authorized to access the database with the database username specified (i.e., the DB trusts that they are who they say they are). To lock this down, edit your pg_hba.conf to use a non-trust authentication method like MD5. Additionally, remote login access on template1 and PostgreSQL default databases should be revoked.

2. Use Hash-Based Column encryption for values that don't need to be decrypted

Encryption methods such as AES are two-way—they can be decrypted—while hash-based encryption methods such as MD5 are one-way. For values that only need to be check for a match such as passwords, use one-way encryption for an added layer of security in the event that table data is compromised.

3. Use Physical Separation to Isolate Datasets that Need to be Kept Apart

By using pg_hba and RBAC to control access to physically disparate databases, you ensure that data in two tables cannot be accessed/viewed simultaneously. Of course, this will break SQL joins, so only use in appropriate scenarios that require physical access separation during the life of a login session.

4. Lock Down Port-Level Access to the PostgreSQL Database

A limited set of ports should have network access to the database: the database port itself and any necessary management ports. All other ports that allow network access to the database should be locked down.

5. Use pg_hba.conf to Specify Which Hosts Can Use SSL-Encrypted and Unencrypted Connections

This can be accomplished by adding and removing the appropriate entries in the pg_hba.conf file. Generally-speaking, all clients should be forced to connect with SSL by adding the necessary hostssl entries. If using this model, all host entries should be removed (aside from localhost).

6. Prevent Connections from Networks that Don’t Require Database Access.

Setting listen_addresses to localhost will force the OS to reject connection attempts from other servers before they reach PostgreSQL. This will prevent unauthorized access to the database and underlying data structures.

7. Set Appropriate Monitoring and Logging for Database Queries

By turning on tracking for queries executed against your database, you enhance the audit trail for identifying culprits in the event of a mishap or compromise. This can be accomplished by installing the pg_stat_statements extension, which effectively turns on monitoring for all query types (SELECT, INSERT, UPDATE, DELETE).

8. Consider Disabling Remote Access to PostgreSQL

This action alone eliminates a host of substantial attack vectors. Again, this can be set in the pg_hba.conf. If remote access to the database is required, SSH to server housing the database and use a local connection thereafter. Alternatively, you can set up tunnel access to PostgreSQL through SSH, effectively giving client machines access to remote databases as if they were local.

9. Assign a Distinct Role for Each Application

By doing this, you add an extra layer of security for separating users from data. Generate a new user and assign appropriate permissions for each new application utilizing the PostgreSQL database. 

10. Stay on Top of Critical Security Updates and Patches

This is not only a rule of thumb for all software systems in general, but a PCI-DSS requirement if your PostgreSQL-based web application or ecommerce store is storing sensitive credit card information. Checking PostgreSQL's security information page regularly and frequently for critical security updates and patches can effectively satisfy this requirement.

Need to implement these security checks and more with a couple mouse clicks? UpGuard's platform for continuous security monitoring can automatically scan your PostgreSQL database for vulnerabilities with its policy-based integrity validation engine. Try it today—it's free for up to 10 nodes.