PostgreSQL - making connections #2

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.


TCP connections

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, 123.45.67.890, 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:

sudo ifconfig

iptables adjustment

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=>

Success!

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 123.45.67.890
...
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 123.45.67.890
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 demo@123.45.67.890

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.

Summary

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.

Mike

Article Comments:

Cavadas commented Thu Jul 16 19:01:43 UTC 2009:

I have clear password in postgres 8.3 by pgAdmin III? Now i cant connect to BD. The old password return "wrong password", the new password "" (blank) return "You dont provide password".

How can i return to old configuration? I dont touch in pg_hba or pqpass or postgresql.conf files.

Please, resonses to my email, very thx

AlanOQ commented Sun Oct 16 09:01:42 UTC 2011:

Литва новости общества на информационном портале finansy-onlain.ru.

Want to comment?


(not made public)

(optional)

(use plain text or Markdown syntax)