Following part 1, this article explains how to configure our postgres server for less restricted local and remote TCP connections.
For a full treatment, refer to Chapter 21 in the official documentation.
Our postgres server is perfectly capable of local and remote TCP connections, but we'll want to make further adjustments to 'pg_hba.conf' and to the main configuration file 'postgresql.conf'.
postgresql.conf - listen_addresses
We'll start with 'postgresql.conf':
sudo nano /etc/postgresql/8.3/main/postgresql.conf
Find this part:
# - Connection Settings - #listen_addresses = 'localhost' # what IP address(es) to listen on; # comma-separated list of addresses; # defaults to 'localhost', '*' = all # (change requires restart) port = 5432 # (change requires restart)
By default postgres is listening on the slice's localhost interface (127.0.0.1), port 5432. We won't change the listening port, but for the purposes of this article we're going to add the slice's public and private IPs:
# - Connection Settings - listen_addresses = 'localhost, 18.104.22.1680, 10.300.300.300' # what IP address(es) to listen on;
Make sure to remove the '#' character in front of 'listen_addresses' and restart postgres to apply the changes.
If you don't know your slice's IP addresses, you can find them listed in the SliceManager, or in the output of:
We'll need to open port 5432 in our iptables firewall:
sudo nano /etc/iptables.up.rules
Add a rule for TCP port 5432:
... # Allows connections to the PostgreSQL process -A INPUT -p tcp --dport 5432 -j ACCEPT # Allows HTTP and HTTPS connections from anywhere (the normal ports for websites) -A INPUT -p tcp --dport 80 -j ACCEPT -A INPUT -p tcp --dport 443 -j ACCEPT ...
After saving changes, we'll flush and then reload the rules:
sudo iptables -F ... sudo iptables-restore < /etc/iptables.up.rules
See the initial setup tutorial for basic help with iptables configuration.
pg_hba.conf - host records
Now we'll return to 'pg_hba.conf'. Find this section:
# IPv4 local connections: host all all 127.0.0.1/32 md5
The "host" keyword indicates this line — called a "record" — relates to TCP connections.
NOTE: host records specify FROM what hosts connections are allowed, not their destination IPs. Remember, in 'postgresql.conf' we've already specified on what interfaces postgres should listen.
The current record allows TCP connections to "all" databases, from "all" roles, FROM the slice's localhost interface, using the "md5" authentication method.
localhost TCP - a succesful connection
Cool, let's test it. Logged into my demo slice as a Linux user named "mike" I'll attempt a loopback TCP connection with psql. The '-h' flag can be used to specify numeric addresses or hostnames:
mike@demo:~$ psql -U demorole1 -d demodb1 -h localhost Password for user demorole1: Welcome to psql 8.3.6, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256) demodb1=>
Note the message indicating my connection is SSL encrypted. SSL enabled versions of the postgres server and psql client were installed by the aptitude package manager on our Ubuntu Hardy slice. See Section 17.8 in the official PostgreSQL documentation for more details.
That's great news for security conscious slice admins (which you should be). Our postgres server and psql client support unencrypted TCP connections too — we'll consider that more carefully later in this article.
Empty passwords and the postgres role
So if our postgres server is already configured to listen on the localhost interface and accept password authenticated TCP connections FROM localhost, why haven't we been connecting that way from the start? Could we not connect like this as the postgres role, bypassing the need to login to the slice as the postgres Linux user?
A simple experiment with psql will clear this up. We'll just press enter when prompted for a password, as the postgres role was configured with a blank password during installation:
mike@demo:~$ psql -U postgres -d demodb1 -h localhost ... Password for user postgres: ... psql: fe_sendauth: no password supplied
It was in the local connections article we learned the postgres Linux user and postgres database role were created with empty passwords. Recall that Linux PAM won't allow console logins for Linux users with empty passwords, and that SSH won't accept empty passwords either (by default).
Similarly, our postgres server won't accept empty passwords for connections requiring password authentication.
That may seem an obscure point, but knowing about this restriction will help prevent confusion and frustration if we meet with connection errors.
Host records for remote connections
Before revisiting 'pg_hba.conf' let's try another experiment.
Having installed psql (SSL enabled) on my local computer, I'll attempt to connect to the slice's postgres server:
mike@mylocal:~$ psql -U demorole1 -d demodb1 -h 22.214.171.1240 ... psql: FATAL: no pg_hba.conf entry for host "700.700.700.280", user "demorole1", database "demodb1", SSL off
There's no host record for my local computer's IP address (700.700.700.280) in 'pg_hba.conf' so I was unable to connect.
We'll need to add a host record for each IP address (or address range) from which we want our postgres server to accept connections:
# IPv4 local connections: host all all 127.0.0.1/32 md5 hostssl all all 700.700.700.280/32 md5 hostssl all all 10.300.300.305/32 md5
I've added a record for my local computer's IP and the private (non-routable) IP of another Slicehost slice I operate (10.300.300.305).
Two things to note:
— The connection type for the new records is specified as "hostssl".
The "host" connection type will accept plain and encrypted connections. That's fine for localhost, but a security risk for remote connections. The "hostssl" connection type will ONLY accept SSL encrypted connections, a better option for remote hosts as our data won't pass naked between computers, in view of potentially prying eyes.
— The IP addresses are appended with a CIDR mask.
The CIDR mask is not optional and connections to postgres will fail if it's not included. The '/32' mask indicates a single IP and is a reliable default value if you're not sure what mask to use.
We'll save our changes and then restart postgres:
sudo /etc/init.d/postgresql-8.3 restart
Remote TCP - a succesful connection
Now I'll try again to connect from my local computer:
mike@mylocal:~$ psql -U demorole1 -d demodb1 -h 126.96.36.1990 Password for user demorole1: Welcome to psql 8.3.6, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256) demodb1=>
Wonderful. A connection from my other Slicehost slice will likewise succeed.
Allowing all remote hosts
It's possible to create a record in 'ph_hba.conf' that will allow connections from any remote host:
hostssl all all 0.0.0.0/0 md5
This setting may be useful on occasion, but it's NOT recommended for a production setup. It's generally better to create host records for specific IP addresses or ranges which we trust — like the private IP of another Slicehost slice, or a business office's pool of public IPs.
Remote clients with dynamic IPs
Some computers, like Slicehost slices, have static (fixed, unchanging) IPs. But a great many have dynamic IPs, such as personal computers on residential cable and DSL networks.
Given the advice above, concerning 'pg_hba.conf' host records, how can we accomadate database clients that have dynamic IPs?
One way is to use SSH local port forwarding on the client. In a terminal on my local computer I'll establish a port forwarding SSH connection to my demo slice:
ssh -p 30000 -L 1234:localhost:5432 -N email@example.com
The '-p' flag specifies on which port the slice's SSH server is listening. The default value is 22, but in our initial setup tutorial it was changed to 30000, and you may have specified a different number in '/etc/sshd_config'.
The '-L' flag specifies that port 1234 on my local computer's localhost interface should be forwarded to port 5432 (the postgres port) of the slice's localhost interface. Note that the value '1234' can be substituted with any integer between 1025 and 65536, so long as it doesn't conflict with the listening port of another process on the local computer.
The '-N' flag tells the SSH client not to execute a command on the slice, just establish a connection — so we won't be greeted with a bash prompt.
Such port forwarding connections must remain open the whole time they are used to communicate between the local computer and the slice's postgres server. In other words, don't close this terminal window, leave it open and re-run the SSH command if it times out or drops off for some reason.
In a separate terminal window on my local computer, I can now connect to the slice's postgres server through the "encrypted tunnel" established by my SSH connection. The '-p' flag is used to specify a non-default port for the postgres server:
mike@mylocal:~$ psql -U demorole1 -d demodb1 -h localhost -p 1234 Password for user demorole1: Welcome to psql 8.3.6, the PostgreSQL interactive terminal. ... SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256) demodb1=>
It works! Note that from psql's perspective, I'm connecting TO the local computer. From the postgres server's perspective, I'm connecting FROM the slice's localhost interface.
This is great because it doesn't depend on there being a host record in 'pg_hba.conf' for the local computer's IP address. In other words, even if the local computer's IP address is dynamic and changes frequently, SSH port forwarded connections to the postgres server will still work properly.
We've seen how 'postgresql.conf' and 'pg_hba.conf' can be adjusted to suit our needs for TCP connectivity.
We've also explored how clients with dynamic IPs can be connected to postgres without frequent adjustments of 'pg_hba.conf'.
However, opening our postgres server to TCP connections from remote hosts is not without security risks. Some of those risks and concerns have been hinted at above; we'll give them further consideration in the next article.