PostgreSQL - making connections #3

Following part 2, this article addresses some security-related concerns affecting postgres servers open to remote TCP connections.


Motivation

As we worked through the previous article, we briefly considered several points regarding the security of our postgres server. We'll highlight them here, and address a few related concerns.

TCP connectivity to a postgres server from remote hosts is a common requirement in the design of many web applications, but we want to make sure our databases are not exposed or weakened unnecessarily — the Internet is full of hackers and other unsavoury individuals who might like to steal or damage our data, or hijack our database server for nefarious purposes.

Port change

Port 5432, the default postgres listening port, is well known and relatively common — hackers and bots will check whether it's open when scanning an Internet host, such as a slice.

One way to add a bit of extra protection to our postgres server is to change the port on which it's listening. We can choose any integer between 1025 and 65536 that doesn't conflict with another process listening on our slice, '43210' for example:

# - Connection Settings -

#listen_addresses = 'localhost, 123.45.67.890, 10.300.300.300'  # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost', '*' = all
                                        # (change requires restart)
port = 43210                            # (change requires restart)

It's not a necessary change, just a simple protective measure we can take. If we do change it, our iptables rules will need updating and we'll have to specify the custom port in a web application's settings and for any port forwarded connections.

For the remainder of this article and in subsequent articles, we'll stick with the default value (5432) for simplicity's sake.

Port exposure

We previously learned that our postgres server was configured by default to listen only on the slice's localhost interface (127.0.0.1).

For the purposes of the tutorial, we re-configured it to listen on both the slice's public and private IPs. Further, we loaded an iptables rule that opened port 5432 on both IPs. While this is convenient for exploration and testing of remote connections, doing so is not without long-term risks.

If our postgres server is listening on the public IP, and iptables is allowing inbound connections, that means it's a potential target for brute force attacks or attempts at known exploits (if any are known or discovered). And if it's listening on the private IP, an attack could come from another host on the Slicehost network — the latter is less likely than the former, but still a real possibility.

We want to make choices for our postgres setup that minimize its exposure right from the start.

Listen on an interface only as needed

The primary thing we can do to limit our security risks is to specify a listening interface in 'postgresql.conf' only when it's truly required for our application.

Hosts which are remote to the the Slicehost network — such as your local computer — will rarely need a persistent connection. SSH port forwarded connections will suffice, making it unnecessary to have postgres listen on the slice's public IP.

As was explained previously, SSH port forwarded connections appear to originate from the slice's localhost interface while connecting to the same, from the perspective of the postgres server. Review the section in the previous article titled "Remote clients with dynamic IPs" for a more detailed explanation and example.

So we can remove the public IP from the 'listen_addresses' list in 'postgresql.conf'. What about the slice's private IP? If our database server is split off from the slice(s) running our web application — a common setup — postgres will need to listen on that interface.

Let's modify 'postgresql.conf' accordingly:

sudo nano /etc/postgresql/8.3/main/postgresql.conf
# - Connection Settings -

#listen_addresses = 'localhost, 10.300.300.300'  # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost', '*' = all
                                        # (change requires restart)
port = 5432                             # (change requires restart)

In this example, we used 10.300.300.300 as the Slice internal IP - you would need to enter your Slice private IP.

We'll need to restart postgres to apply the changes.

Note that if postgres is running on the same slice as our application's other processes, and we don't otherwise plan on connecting to our databases from another slice, we should also remove the private IP from 'postgresql.conf', leaving only localhost specified for 'listen_addresses'. In fact, that's the default setup.

Tighten iptables

We should now modify the iptables rule we loaded in the previous article, such that port 5432 is open to inbound traffic destined only for our slice's private IP. For a Slicehost slice, the private IP is always bound to interface 'eth1', while the default public IP is bound to 'eth0'. Extra public IPs are bound to 'eth0:x' (x = 1, 2, 3 ... ). We can use the '-i' flag to specify an interface for our iptables rule:

sudo nano /etc/iptables.up.rules
# Allows connections to the PostgreSQL process
-A INPUT -i eth1 -p tcp --dport 5432 -j ACCEPT

The reasoning here: don't keep ports open unnecessarily. After saving the changes, we'll need to flush and reload our rules:

sudo iptables -F
...
sudo iptables-restore < /etc/iptables.up.rules

We should simply remove the iptables rule for the postgres port if the database server will be listening only on localhost.

pg_hba.conf - careful choices

There are a few principles to keep in mind when adding and pruning host records in 'pg_hba.conf', especially for production servers:

— Specify hosts only as necessary.

If we have four additional slices and only one of them will be connecting to the postgres server, we should make a host record only for that one slice's IP.

Most especially, if for convenience you ever set a record allowing connections from all hosts, don't run with it for long, and never on a production server. That would be asking for trouble.

If we're developing a setup that will eventually go into production, it's a good idea to occasionally review 'pg_hba.conf' and prune any host records that have turned out to be unnecessary.

— Use hostssl whenever possible.

We learned in the previous article that host records with the "host" connection type will allow both plain and SSL-encrypted connections. The "hostssl" connection type requires the postgres server and client to use SSL encryption.

The "host" type is okay for localhost's record — in fact we may have processes running on the slice which aren't SSL-enabled, yet need to communicate with postgres.

But for remote TCP connections, we should use "hostssl" whenever possible, whether the database client is on the Slicehost network or somewhere else on the Internet. Databases quite often store sensitive information we don't want traversing cyberspace naked before the peeping eyes of unscrupulous parties who would like to abuse it for their gain.

If we find that the database client software running on our remote host (another slice, some other server, or a local computer) doesn't support SSL-enabled connections, it's best to search for instructions on how to re-install or compile a version which does. If an SSL-enabled version doesn't exist, we can consider using SSH port forwarding to make the connection. Stunnel is another possibility in that situation.

Summary

Most anyone running a PostgreSQL server will make remote TCP connections to it.

The configuration files are flexible enough to leave the database server wide open to Internet connections. It's better though to specify only the necessary interfaces and hosts in its white lists — postgresql.conf, pg_hba.conf.

Encrypted connections are also possible, and should generally be required for all remote clients.

Further, the slice's iptables firewall should be adjusted to pass traffic on an interface and port only as needed.

It's your server and your data — configure PostgreSQL wisely.

Mike

Article Comments:

empty commented Sat Jun 18 02:16:28 UTC 2011:

If you are connecting from a server not on slicehost you can use the public interface, but set an explicit source address in the iptables. That way the port will only be listening for that server.

Want to comment?


(not made public)

(optional)

(use plain text or Markdown syntax)