Chapter 26: Securing PostgreSQL

Overview

The following steps should be taken to ensure a secure PostgreSQL deployment:

1. Make entries in the pg_hba.conf file as restrictive as possible. PostgreSQL installs with network access disabled by default. Most deployment scenarios require it to be accessible remotely. The pg_hba.conf should be written according to the following considerations:

   • Specify individual hosts as opposed to network ranges (or worse still, all hosts) unless it is a reject rule.

   • Make use of specific reject rules (placed at the top of the list of rules) to always prevent access from certain network ranges to specific databases.

   • Enforce SSL; this is discussed in more detail in Step 2.

   • Use specific username-database name pairs as an extra layer of access control.

   • Do not use weak authentication types such as trust, password, or ident. Use md5 in place of crypt.

2. Enable SSL and use client certificates.

   • SSL should be enforced via the pg_hba.conf rules. You can find additional information in the PostgreSQL documentation, under "Secure TCP/IP Connections with SSL."

   • Alternatively, Stunnel can be used to create a secure tunnel. Stunnel is a small application that acts an SSL wrapper; it is simple to con-figure it to use client certificates. You can download Stunnel from http://www.stunnel.org .

   • You can find a how-to describing using PostgreSQL with Stunnel at http://cfm.gs.washington.edu/~adioso/HOWTO/PostgreSQL/StunnelPostgreSQL.xml .

3. Run on a single user system.

   • PostgreSQL was designed to run on a single user system. Some organizations run it in a hosted environment where third parties contain user accounts (or even administer the system). This should be avoided given the high number of privilege escalation attacks that are reported in setuid applications. Once a malicious user can execute commands as the superuser, the data in the database is compromised. Superusers can access it in any number of ways ”they could trivially modify the pg_hba.conf to allow themselves trusted access to all databases, or they could copy the databases files themselves onto another system.

   • As an additional security measure, database usernames should be different than operating system usernames. This reduces the chance of a brute-force attack succeeding if the attacker is able to gain a list of operating system usernames via information leakage from another service.

   • If the database must be run on a multi-user system, permissions on the Unix domain socket should be set such that only the designated user or group is granted access.

4. Apply best practice hardening to the server and environment.

   • Disable all unnecessary services. This is applicable to both Unix and Windows systems. Many Unix systems used to install and enable multiple network daemons (telnet, FTP, DNS, and so on) by default. Windows systems install with a number of potentially insecure services enabled such as the remote registry service and the computer browser service.

   • You can find Linux hardening information at http://www.sans.org/rr/whitepapers/linux/ . Alternatively, Bastille is a set of interactive hardening scripts. You can download it from http://www.bastille-linux.org/ .

   • You can find Microsoft hardening guides at http://www.microsoft.com/technet/security/topics/hardsys/default.mspx .

   • The server should be kept up to date with security patches. The system administrator should subscribe to mailing lists such as BugTraq ( http://www.securityfocus.com/archive/1 ) and relevant vendor lists.

   • In addition, the environment should contain security measures to segregate access to servers holding sensitive information. Internal and perimeter firewalls should be configured to block access to the PostgreSQL port with individual "allow" rules to permit access from application servers. Firewalls should prevent external access and limit internal access to NetBios and SMB ports; this will mitigate the information leakage attack via UNC paths.

5. Keep up-to-date with database patches.

   • PostgreSQL has had significantly fewer reported vulnerabilities than many of its commercial and open source rivals. Nevertheless, bug fixes are released on a regular basis. Fix information is announced on the pgsql-announce mailing list. It is archived at http://archives.postgresql.org/pgsql-announce/ . Other lists worth monitoring include pgsql- bugs , pgsql-hackers, and pgsql-patches (also archived at the preceding URL). Security vulnera-bilities have been publicly discussed on these lists. This is worth keeping in mind so that necessary countermeasures can be taken prior to the release of a patch.

6. Review client applications for use of "dangerous" functions that construct dynamic queries based on user input.

   • Wherever possible, make use of parameterized queries via use of (libpq) PQexecParams() and PQexecPrepared(). If modifying the application to use these functions is not feasible , PQescapeString() should be used to escape problematic characters originating from user input. The equivalent safe query functions should be used when developing applications in other languages.