PostgreSQL is one of the most advanced and popular open source databases in the world. However, that doesn’t mean it’s immune to hacking and malware. There are dozens of known and well documented PostgreSQL security vulnerabilities.
So whereas it’s superior to most as far as out-of-the-box security features are concerned, it can still be exploited relatively easily if all the known loopholes aren’t sealed. You need to apply the right security measures if you want to protect both the underlying data and the applications that generate it. Here are a number of ways you can do just that.
Server Configuration
Performance Optimization Tools Can Lift You Up
Some of the parameters on the file postgresql.conf can be modified to further enhance security. On the listen_address parameter, you can specify the IP addresses that are permitted to connect to the server as opposed to leaving a general value (such as ‘*’ or ‘::’) that effectively allows any IP address to make a successful connection.
You can also change the listening port from the default 5432 in order to make it more difficult for a hacker to map out a clear path of attack. To mitigate against an active DDoS attack, you could modify the settings on certain parameters such as temp_file_limit, max_prepare_transactions, temp_buffer, maintenance_work_mem and work_mem.
User Access Control
5 Reasons to Opt for Linux Web Hosting
The core principle of user access control is to grant users only the lowest possible rights that they need in order to perform their roles. This rule may sound fairly straightforward but can get quite complex especially for large organizations. Not only do you have to assign new users the rights they need but you have to take into consideration departing employees, job changes and temporary roles.
One of the best tested approaches to access control is employing the user, group and role strategy. In postgresql, all user permission categories are considered roles. But for effective user access management, you can create three types of roles: user roles, group roles and role roles.
Role roles manipulate objects, group roles comprise various role roles, while user roles are assigned at least one group role. This kind of arrangement creates a granularity that simplifies the process of granting and revoking privileges.
Super User Protection
The super user account should be the single most protected account on your database. The password you create for it should adhere to all the fundamental rules of password management. It should be comprised of lower and uppercase characters, numbers and special characters. A strong super user password will make hacking difficult and bolster the safety of the database.
Leverage external authentication tools such as PAM or LDAP that enforce password expiry and reuse policies as well as enabling account locking after several failed logins.
Logging
Microsoft Previews HD Insight Big Data Framework
Postgresql comes pre-equipped with config parameters that control the when, where and what of system logging. You can enable temp file sizes, long running queries and session connection or disconnection. Logging gives a detailed view of your database workload and makes it easier to pick up unusual behavior.
For more advanced insights, enable pg_stat_statements which allows you to track executions data for the SQL statements launched by the server. There are security tools that you can feed all this data into to generate a whitelist that will show what queries aren’t responding as they should.
DataSunrise Security Tool
Data Disasters We Can Learn From
As we mentioned in the previous post, there are security tools that you can use to analyze key data to identify potential points of security vulnerability. One of these is worth special mention. PostgreSQL Database Security by DataSunrise is relatively easy to install and setup.
As long as you configure your security, auditing and masking rules properly, a lot of the monitoring by DataSunrise will be automated. It detects attacks, notifies administrators and provides a treasure trove of forensic evidence in the event of a data breach.
Detailed logs show SQL query codes, results of query execution, changes made to data and modifications to database configuration.
Installation is just the first step toward getting your PostgreSQL database server ready for production. These security tips will go a long way in ensuring your data remains secure and safe.
