The PostgreSQL database, like MySQL (which we looked at in a previous tip), is a popular choice for applications, and particularly Web-based applications. As with many other
This tip takes you through simple steps to secure a PostgreSQL installation. It focuses on infrastructure security rather than application security and assumes that you design and code your applications securely.
Before we look at specific steps, there are some standard high-level items that you need to consider:
- Keep your PostgreSQL version up-to-date. This tip is relevant for version 8 and up. If you have earlier versions, I recommend that you upgrade.
- Don't neglect your host security. You should ensure the host your database is running on is up-to-date, secure and well managed. There are several guides available to help you with this.
- Ensure your network design is appropriate and secure. The three-tier model of Web, application, database -- each separated by firewalls -- is the recommended deployment model for Web applications. Your database servers are best located behind the appropriate firewall infrastructure. At minimum, you should avoid locating them directly on the Internet.
Now let's look at three simple steps to enhance the security of PostgreSQL:
1. root is not a user for your applications
One of the key security controls for PostgreSQL, and indeed many other applications, is to ensure the database server daemon, called postmaster, is being run as an appropriate user. For most distributions, including Red Hat Enterprise Linux and SUSE, this should be done automatically for you. On these distributions PostgreSQL should run as a normal user, usually the Postgre user. You can confirm that the daemon is running as an appropriate user by checking the process.
# ps - aux | grep 'postmaster' postgres 12934 0.0 1.2 20896 3156 ? S 02:16 0:00 /usr/bin/postmaster -p 5432 -D /var/lib/pgsql/data
If it isn't being run as a nonroot user, you should create an appropriate user. You can then change your init script or start command to start PostgreSQL with this user. You can use su to then run PostgreSQL as the appropriate user.
To extend this, you can also run PostgreSQL under SELinux or AppArmor. For example, the SELinux "targeted" policy on Red Hat Enterprise Linux includes support for running PostgreSQL.2. Limit connections to PostgreSQL
PostgreSQL can be bound to both network and file system sockets. This means you can connect applications to your database over the network or locally via a socket. If our applications allow, we can limit the attack surface of the database by limiting how applications connect to it.
There are three modes for PostgreSQL connectivity:
- Bound to an interface, interfaces or all interfaces
- Bound to localhost
- Bound to a file system socket
Generally, the smallest attack surface is to first bind to a file system socket, then a localhost network socket and finally a network socket. Obviously, some applications on remote hosts will require network connectivity and we'll look at how to lock this down in part 3, below.
Each of these modes are configured in the postgresql.conf configuration file, usually located in your data directory. In Red Hat, for example, it is in the /var/lib/pgsql/data directory.
Control of network bindings is handled by the listen_addresses configuration option (you can also change the port number if you'd like). You can specify an address, addresses or blank to not bind to the network at all.
You can also only bind the network to the localhost.
listen_addresses = 'localhost'
If you do bind to an external interface, you will need to ensure that port 5432 is open on your firewall. It is a good idea to lock down incoming connections to all hosts except those hosts who need to access the database. For example:
# iptables -A INPUT -p tcp -s 10.10.0.10 -d 10.10.0.20 --dport 5432 -m state --state NEW,ESTABLISHED -j ACCEPT # iptables -A INPUT -p tcp –I lo --dport 5432 -m state --state NEW,ESTABLISHED -j ACCEPT # iptables –A INPUT –p tcp –dport 5432 –m state NEW,ESTABLISHED –j DROP
The first rule tells iptables to allow connections from the 10.10.0.10 host to the 10.10.0.20 host (our database host) on port 5432 - PostgreSQL's default port. The second rule allows access from the localhost. The last rule drops any other PostgreSQL connections. You would then also need to create appropriate outgoing rules.
Further securing network connections can be achieved by enabling SSL support for PostgreSQL and only allowing SSL-secured connections to the database.
If you wish to disable network binding altogether, you can specify a blank value for the listen_address configuration option.
listen_addresses = ''
If you disable network access, then you will need to enable a local file system socket for access. You can do so using the following three options:
unix_socket_directory = '/var/lib/pgsql/pgsocket' unix_socket_group = 'postgres' unix_socket_permissions = 0700
The first option is "unix_socket_directory." This specifies the location of the filesystem socket. The second option, "unix_socket_group" should own the socket. It defaults to the group that is used to start PostgreSQL.
The last option, "unix_socket_permissions", controls the permissions of the socket.
For sockets, only write permissions matter. Also, the PostgreSQL socket permissions generally defaults to 0777, which allows anyone to write to the socket. You can limit this further by restricting write permissions to only the group or user, using permissions of 0770 or 0700 respectively.3. Control client authentication and access
After configuring how clients connect to PostgreSQL, we need to specify which clients can connect to PostgreSQL. This is done using host-based client access control and is configured in the pg_hba.conf configuration file. The pg_hba.conf configuration file is usually located in the PostgreSQL data directory, for example /var/lib/pgsql.PostgreSQL has a wide collection of authentication and access mechanisms including password authentication, PAM, LDAP and Kerberos and others. With the pg_hba.conf file you can control connections to local or network sockets, where they connect from and what authentication methods are allowed. The authentication is also sufficiently granular to control which databases can be accessed. Let's look at some simple examples:
# Type DB User Network Mechanism local all all pam host sameuser all 127.0.0.1/32 pam host www www 10.10.0.10/32 md5
Each configuration line in the pg_hba.conf file is made up a number of fields. The first field tells PostgreSQL what type of connection we are securing. The two most common types are "local" for local file socket connections and "host" for network connections. You can specify multiple authentication mechanisms for each type.
The DB field specifies which database you can connect to and includes two special options values: "all" and "sameuser". The "all" option indicates that incoming users on this connection type can connect to all databases. The "sameuser" option will only allow an incoming user to connect to a database with the same name as the connecting user.
So the second line in our example above would allow the user "ben" to connect to a database called "ben" but not to a database named "jerry". You can also specify multiple databases by separating each with a comma.
The user field controls which user ids are allowed to connect. Again, we can use the "all" value for all user ids to connect or the name of a specific user. You can also specify multiple users by separating each with a comma.
For network connections -- though obviously not local sockets -- we can specify which hosts and network can connect in the form of an IP address and subnet. In the second line above we limit connections to the loopback address and in the third line to the IP address 10.10.0.10.
Lastly, we specify the mechanism used for authentication. We've used two common mechanisms in our example.
The first two lines make use of PAM. When an authentication query is received on our local socket or via the loopback address, PostgreSQL will query PAM and authenticate. On a Red Hat Enterprise Linux host, this means checking the default /etc/pam.d/postgresql service.
The second mechanism we used, md5, indicates that any connections from the 10.10.0.10 IP address must be from the user www to the database www. This mechanism expects an MD5-encrypted password.
With all authentication mechanisms you should use all the available capabilities to limit access - only allow specific users to access the databases they require, and if using network connectivity from the smallest number of hosts.
You can find more about PostgreSQL client authentication at http://www.postgresql.org/docs/8.3/interactive/client-authentication.html.Further steps…
- Think about using SSL to secure connections.
- Look at using AppArmor or SELinux to secure PostgreSQL. To this end,
- Information about the current state of PostgreSQL security is available at http://www.postgresql.org/support/security.html.
- Finally, also available for PostgreSQL is a security enhanced version called SE-PostgreSQL. It extends PostgreSQL to provide more finely grained and consistent mandatory access controls.
About the author:James Turnbull works for the National Australia Bank as a Security Architect. He is also the author of Hardening Linux, which focuses on hardening Linux hosts including the base operating system, file systems, firewalling, connections, logging, testing your security and securing a number of common applications including e-mail, FTP and DNS.
James has previously worked as an Executive Manager of IT Security at the Commonwealth Bank of Australia, the CIO of a medical research foundation doing Web-based clinical trials, managing the architecture group of an outsourcing company and in a number of IT roles in gaming, telecommunications and government.
This was first published in June 2008