Tuesday, December 12, 2006

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.

Labels:

5 Comments:

Blogger MarkE said...

Salut,

A one line awk could be used by cron to do something similar, if you have an dyndns address.

awk '/#my.dynamic.ip/{ print $0 ; while ( "dig my.dynamic.ip" | getline ){ if (i $0 ~ /;; ANSWER/){ "dig my.dynamic.ip" | getline; print "hostssl all all "$5"/32 md5" } } ; close("dig my.dynamic.ip") ; getline; next; }{ print $0 }' pg_hba.conf > new_hba.conf

For an exmple pg_hba.conf of:

# "local" is for Unix domain socket connections only
local all all md5
# IPv4 local connections:
#my.dynamic.ip
99.99.99.99
hostssl all all 192.168.1.0/24 md5
host all all 127.0.0.1/32 md5
# IPv6 local connections:
host all all ::1/128 md5

Bon Nuit.

12 October, 2010 01:34  
Blogger MarkE said...

Ooops, Take out the Typo i.

awk '/#my.dynamic.ip/{ print $0 ; while ( "dig my.dynamic.ip" | getline ){ if ( $0 ~ /;; ANSWER/){ "dig my.dynamic.ip" | getline; print "hostssl all all "$5"/32 md5" } } ; close("dig my.dynamic.ip") ; getline; next; }{ print $0 }' pg_hba.conf > new_hba.conf

12 October, 2010 01:39  
Blogger Unknown said...

Super happy elephant! Ping pong it works! Thankee

18 January, 2012 15:24  
Anonymous Anonymous said...

Why would you do that if you already have a SSH connection? Just port forward through ssh...

09 March, 2017 16:21  
Blogger Milivoj. said...

Yes, SSH forwarding would also work. 11 years ago, I didn't think of that. And I hadn't configured a dynamic DNS yet, so couldn't use the alternitave suggested by MarkE. Without a dyndns setup, the SSH port forwarding would be

ssh -fnN -L 5433:$MYSERVER:5432 -i ~/.ssh/identity $MYSERVER

then

psql -h localhost -p 5433 ...

and to kill it afterwards:

kill $(ps x | awk "/$MYSERVER:543[2]/ {print \$1}")

11 March, 2017 19:37  

Post a Comment

<< Home