Using barman for incremental postgresql backups can really make life easier. But one thing bothered me while setting it up: why is an additional postgresql database connection from the backup server needed although there's already an SSH account configured? I decided to take a deeper look into that problem.
Normally on Debian/Ubuntu you have a postgres system user that can connect to the local database, simply by invoking psql - without a password. You don't even know the password - since it's autogenerated by the system.
So first, to play nicely with barman, you would have to set a password for the postgres database user or create another superuser, and allow connections from other hosts instead of unix sockets only. Then you would have to let postgres listen to the external network interface, not just localhost or unix sockets, so the backup machine can connect. Maybe you have to configure your firewall as well to let these ports through. And last, you have to think about how these connections can be secured, with SSH or SSL for example.
But why all of that? After setting up barman like described in the documentation, we have a working SSH connection from the backup to the database server. So why not simply using this instead of setting up another potential security hole?
Well, asking this kind of question on the barman mailinglist didn't give me an answer to this. So I started to think, how to solve this by using the already existing SSH connection.
I simply created a barman wrapper script, that uses SSH and socat to forward psql requests to the local unix socket, to the database machine through the existing SSH connection. To avoid always calling the wrapper script instead of barman, I simply created an alias in barman's .profile/.bashrc that sets barman to the wrapper script. So now I can invoke barman on the command line that transparently uses the barman wrapper, that opens the SSH connection to forward all psql calls to the database machine, starts barman as usual, and closes the connection again after barman has finished its work. Sweet, huh :)?
The advantages are clear: now, you only need the already created SSH connection from backend to the database. No changes to your postgres accounts, postgres listen ports, firewall settings and no need to think about securing psql connections to the database. But enough talk, let's get our hands dirty ;).
First, you need to download the barmanwrapper, place it in the barman home dir and make it executable by invoking:
chmod 777 barmanwrapper
After that, modify the following parameters of this script to fit your needs:
# the ssh connection to use sshconnect="ssh postgres@pg" # the local location where the psql socket should be created localsockdir=/var/lib/barman/run localsockfile=$localsockdir/.s.PGSQL.5432 # the remote location where the postgresql unix socket resides remotesockfile=/var/run/postgresql/.s.PGSQL.5432
Then, you need to install socat - if not already on your system. When using Debian/Ubuntu it's easy as:
apt-get install socat
Next, edit barman's .profile/.bashrc and insert the barman alias:
This will replace the original barman command with the wrapper for the barman user.
Finally change the conninfo parameter in the barman.conf:
conninfo = host=/var/lib/barman/run user=postgres
This tells the psql to connect to the local unix-socket using the postgres user. As mentioned before, the call to the unix-socket will then be transparently forwarded to the database server as it would have been invoked directly there.
If you have any other scripts that invoke barman, you have to replace these calls directly with the barmanwrapper since aliases won't be propagated to shell scripts.
If everything went well, and you've performed a relogin to execute the .profile/.bashrc script, calling barman should work as before, but now using the original postgres account on the database server.
Was this script useful for you? Did you solve this problem in another way? Please let me know!