29Mar/138

Using barman without additional postgresql connection

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:

alias barman=~/barmanwrapper

 
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!

Posted by Veit Guna

Tagged as: backup, barman, postgresql

Recent search terms: barman postgres database

  • Brian L

    I believe that you can accomplish the same thing by leaving off the “host” argument in the conninfo parameter in your barman.conf. I was able to do that in my setup where I don’t have access to PostgreSQL except through SSH outside of the box.

    • nightprogrammer

      If you leave out the host parameter, that would mean the postgres connection will be tried via local unix-sockets. Since postgres is installed on the other machine and not on the barman/backup server, that won’t help. The problem here is, that the psql connection isn’t created through the established ssh connection by barman. So normally you need the ssh connection PLUS the postgresql connection from the barman/backup server to the postgresql server. The explained solution here only needs ssh.

  • Gary Burchett

    Thank you. Just the thing I needed for barman. I did need to make a change to the socat command in barmanwrapper. The socket file created by socat (1.7.2.2 running on CentOS 6.4 x86_64) was not being deleted when the socat command was killed. This causes an error in socat the next time barmanwrapper is run. I made the following change:

    socat -L ${localsockfile}.lock “UNIX-LISTEN:$localsockfile,reuseaddr,fork,unlink-early” EXEC:”$sshconnect socat STDIO UNIX-CONNECT:$remotesockfile” &

    This creates a lockfile that prevents socat from running if the lockfile already exists. The lockfile is deleted when the socat command is killed. The unlink-early causes socat to delete the socket file on startup, but after getting the lock.

    To insure that the $localsockdir exists (otherwise socat fails) I also added:

    [ -d $localsockdir ] || /bin/mkdir -p $localsockdir

    This can go inline or in the startsocketfwd function.

    • nightprogrammer

      The script basically worked for me under Ubuntu 10.04.4 LTS x86_64, socat 1.7.1.0-1 except for some days where the backup didn’t succeed. I didn’t find out why yet but maybe it has something todo with the lock settings you mentioned. I’ll try that out and report back.

      Thanks for sharing!

      • nightprogrammer

        I verified the changes and added them to the script. I also figured out, why backup sometimes fails. Since socat is started in background, pgsql starts too quickly before socat can create the local socket file. I added a sleep between the socat invocation and the first pgsql invocation. This fixed it for me. Also added this to the script.

  • max

    Nice post! Much appreciated…
    I’m having problems though…
    Are you running it as root?
    Cause socat needs root permissions, but I need to connect as the “postgres” user…
    Tried changing your script to sudo socat, but it’s not working well…
    What are the permissions on your pg_hba.conf like?
    Feel free to reply to my email is you like… :)
    Danke!

    • nightprogrammer

      Hi.

      I’m running it as the barman user. The wrapper will call socat on the local Unix socket file that is owned by barman. Then it connects via SSH as postgres user and accesses the unix socket there owned by the postgres user. Then it pipes everything through it. No root required. At least on my Debian box. Where does it say it needs root on your machine?

      • max

        I’m running Debian too. But the script would fail if not run as root (postgres or barman user) with an error msg from socat. If run as root, the msg would go away… I have given up since my post anyway since we’ve decided to run barman locally on every machine (company policy)…
        So, by the way, now I have another problem. Any idea on how to run it locally, to just manage the db on localhost? Mainly, what should I do with the ssh line in the conf file (i.e. “ssh_command = blabla”) since it is not needed anymore? Have googled but couldn’t find any such examples… Tried to remove it but have been getting errors… Cheers