Postgres: allow dynamic IPs in pg_hba.conf

If you don't want to open access to PostgreSQL to any external IP, but occasionally do need access from a particular address, you can edit pg_hba.conf. I needed this often enough that I wrote a script to make the procedure easier. What i now do is:

• Open an ssh connection to the server
• Start the pg_open script

The script takes care of getting your current IP from the ssh connection, adding it to pg_hba.conf and telling postgres to reload pg_hba.conf

Since the end of pg_hba.conf is usually to deny all access, the line to allow your IP needs te be before that. So you need some specific text in the file to let the script know where to put your IP, and that specific text must be configured in the script.

Here is the pg_open script:

#!/usr/bin/perl

# open postgres access to current ssh client

my $conf_file = '/etc/postgresql/pg_hba.conf';
my $reload_cmd = '/etc/init.d/postgresql reload';
# the following 2 markers must exist in the file
my $mark_start = '#---- Temporary access for dynamic IP ----';
my $mark_end   = '#---- End Temporary access for dynamic IP ----';

my $VERSION = 0.1;

use strict;

my ($ip, $confblock_new);

## find the IP, using the SSH_CLIENT environment variable
if ( $ENV{SSH_CLIENT} =~ /((?:\d{1,3}\.){3}\d{1,3})/ ) {
 $ip = $1;
 $confblock_new = "\n$mark_start\n"
                . "#Allow current SSH connection (" . scalar(localtime) . " user=$ENV{USER})\n"
                . join("\t", "host", "all", "all", $ip, "255.255.255.255", "trust")
                . "\n$mark_end\n";
}
else {
 die "Could not find ip of current SSH user (\$SSH_CLIENT=$ENV{SSH_CLIENT})\n";
}

## make a backup of the file
system("cp", $conf_file, "$conf_file.bak") == 0
 or die "Could not create backup of $conf_file ($!)\n";

## Get file, and replace the relevant block
open(F, "$conf_file")
 or die "Cannot read $conf_file: $!\n";
undef $/;
my $conf = <F>;
close F;

$conf =~ s/\n$mark_start\n.*?\n$mark_end\n/$confblock_new/s
 or die "Could not find markers in file\n";

## Write new temp file then rename it to the right file name
open(NEW, ">$conf_file.new")
 or die "Could not create new file $conf_file.new ($!)\n";
print NEW $conf
 or die "Could not write new file $conf_file.new ($!)\n";
close NEW;

rename("$conf_file.new", $conf_file)
 or die "Could not rename $conf_file.new to $conf_file ($!)\n";

## Finally signal postmaster to reload the new file
system($reload_cmd) == 0
 or die "Could not reload postgresql ($!)\n";

The limitations are that, as it is, it will only allow one such connection at a time. Also, there should be a timeout or some cron job which removes the allowed IP after some time.