Gnucash is a great tool to keep the budget of the family, and from the version 2.4.0 supports connection to a database, SQLite, MySQL or PostgreSQL for data storage. Using a local database, however, tie to a specific computer for its use. Instead I want to be able to open a GnuCash session from any computer and connect to a single, remote, database.
Enable remote connections to the database from the whole net is very unsafe, but a SSH tunnel gives me a lightweight and reliable alternative .
To implement this i’ve used:
On server side:
On client side:
All these software can run on either Linux, Windows and Mac, which offers great flexibility. I have a Linux server, at home, connected to internet, so for me the whole thing has been very simple, but i suppsoe that even a not too expensive hosting offer sufficient support to implement this.
The configuration of the SSH server does not require anything special, just make sure that your firewall is properly configured to allow access to port 22, or whatever you have set. If you want more security i suggest to use authentication via keys, as described here.
The next step is to create the database and the user who will use it. We will create a SSH tunnel, so you can also use phpMyAdmin remotely as explained for example here, but the procedure is so simple that I decided to do it from the terminal. After obtaining access to the server give the following commands::
# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4012 Server version: 5.1.49-3 (Debian) Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create database gnucash; Query OK, 1 row affected (0.00 sec) mysql> grant all on gnucash.* to gnucash@localhost identified by 'PASSWORD' with grant option; Query OK, 0 rows affected (0.12 sec) mysql> flush privileges; Query OK, 0 rows affected (0.13 sec) mysql> exit Bye #
This means that, after gaining root access to mysql i create the database gnucash and with the GRANT command i create the user gnucash, i give to him permissions for the use of the database and sets the password for access to the database. In GnuCash documentation I have not found anything about the necessary permissions you have to set for use the program, then check them all, although probably you can limit access and improve safety.
Before starting GnuCash on any client you must create an SSH tunnel, which is quite simply to do on any platform.
OpenSSH Client (Linux e Mac)
From a terminal run:
# ssh -L 3307:localhost:3306 -i /media/USB/private.key firstname.lastname@example.org
the -i option specifies the location of the private key for authentication, if necessary, that I keep onto my USB stick, while the -L option is used to create the tunnel to my server which is located in mysite.com, specifying three parameters:
3307 is the local port to which I’ll have to connect to enter the tunnel
localhost is the address where the MySQL server is available. Since my tunnel “exit” on the same machine the address is localhost.
3306 is the port to use to connect to the MySQL server
In the Putty configuration window enter the following information:
Session: Enter the host name, mysite.com, and port of the SSH server, 22 or another.
Connection-> Data: in login details I can put the username for the SSH connection, so that It’s not asked every time.
Connection-> SSH-> Auth: Here i can specify the location of the file containing the private key for authentication, if necessary.
Connection-> SSH-> Tunnels: in source port i insert the local port access, and in the destination i insert the address of the MySQL server and the destination port with the syntax localhost:3306
Before opening the connection, you can return to Session, and save the parameters of the session to call them again in a future session. I can click now on Open, and after entering the password, the prompt of the terminal will indicate that the tunnel is active.
Now I can run GnuCash and open a new file indicating as format of the data mysql, as the host machine the one from which we opened the tunnel and the entrance port, in our case 127.0.0.1:3307, the database name, gnucash, the name of the MySQL user, gnucash, and the PASSWORD. The program creates all necessary tables automatically depending on the structure of the balance indicated by us.
I used the IP address because on my linux box i got errors using the host name, for reasons unknown to me, if I point to localhost: 3307 I get an error and the connection aborts. On Windows machines it works with both syntaxes.
Now i’m ready to enter the data. Whenever I want to edit them, from anywhere i’m at the moment, i can simply open the tunnel and from GnuCash open the existing file entering the same parameters as above.
If you use a private key is not advisable to scatter it on every machine you work on, it is practical and safe enough to keep it on a USB stick where you can also put Putty, which requires no installation and takes up very little space.
I wish a good budget to all 🙂