Changing the app key (re-encrypting the data in the database)

[ndandanov]

Hey James,

Happy New Year!
Thank you for creating such a great piece of software!

I am running Firefly III version 4.6.12 and try to migrate from version 4.6.9.

Description of my issue:

I recently installed Firefly III version 4.6.12 using composer on my home server. I am trying to migrate from version 4.6.9 installed via Docker Compose.

Unfortunately, during the initial setup of the Docker Compose version, I left the default SomeRandomStringOf32CharsExactly key in the .env file.

I would like to migrate the database to the new version with an appropriate key. However, most of the data therein is encrypted as mentioned here.

Is there any script to re-encrypt the data in the database with the new key? Something like this works, but I don't have any experience with PHP to interface it with all encrypted fields in the database. Maybe you have developed such tool for testing purposes?

My database is not too big (a couple hundred records in the biggest tables) as I have been using the test installation for the past two months.

CSV import/export seems to be somewhat incomplete as a straight export cannot be directly imported afterwards. All fields need manual matching, but some of them do not have a matching type in the drop-down menu.

Thanks in advance!

[JC5]

You cannot "rekey" the database, and I'm not going to write a procedure to do so. It's just too much work for a very small use case.

However, not all is lost. You can decrypt the database and encrypt it again, and lose only very little data in the process.

First, keep the old encryption key in your .env file. Open the .env file and change USE_ENCRYPTION to false. Then, run the following command on the command line:

php artisan firefly:use-encryption

This will decrypt most (but not all) data in your database. You will lose access to the following fields, which I think will be easily salvageable:

• The iban field in the accounts table, meaning all accounts will lose their associated IBAN.
• The md5, filename, title, description, notes and mime fields in the attachments table. This effectively destroys all your attachments but they can be saved (see below).
• The data in preferences, meaning you will lose all your preferences.
• The tag attribute in tags, meaning you will lose all your tags.

All other data should be safe: your transactions, budgets, accounts, etc.

To salvage your attachments, run:

php artisan firefly:decrypt-attachment

The error message will explain the required parameters. This command will decrypt the attachments you wish to restore.

---

If you do this, you end up with a database that is almost usable with your new key. You will have to upload the attachments again, recreate any tags and clear the IBAN field from any entry in the table accounts that has one.

Let me know if this works. Remember to make backups!

[JC5]

Oh and of course, all the best in 2018 for you as well, Nikolay!

[ndandanov]

Hi James,

Thank you for your detailed response and sorry for not answering sooner.

I managed to "rekey" the database using a mixture of bash and PHP scripts. I will post the code I used later. The only drawback is that I couldn't rekey any IBANs, but I had none set anyway. Perhaps they are additionally encrypted for security reasons?

Then I instantiated a new installation of Firefly and used the database. At first there were some issues, but a clean install seems to have fixed them.

However, as a side note, I am experiencing a tad slower performance (the dashboard for the current month with <30 transactions loads in 3-6 seconds). This is on a Banana Pi ARM board with a dual-core 1GHz CPU and the latest PHP (7.2.1) compiled locally. All is working behind the latest Apache (2.4.10) for Debian Jessie (oldstable).

Would you suggest a hint on how I could improve performance? I guess it has deteriorated due to the processor architecture, but perhaps I could do something about it. It's also not due to network latency/bandwidth limitations.

I have neither redis nor memcached installed, so maybe installing these would be of help?

Before I was using Firefly in a docker container on my working machine (a dual-core Intel i5 4-th gen CPU). For the test, I mirrored the installation on a Ubuntu 16.04 64-bit VM and it loads faster.

Addition:

This is more like the topic of yet another issue, but is there a way to have multiple accounts linked to the same financial data? Let's suppose that I would like to use Firefly to manage the finances of my household. Then my wife and kids should also be able to access it in order to add their expenses or revenues, shouldn't they?

The only way to achieve this currently is to use a single account, as far as I am aware. Am I missing something? :-)

Thank you in advance!

[JC5]

That's no problem, I'm glad you replied.

The IBAN is never decrypted, so it cannot be easily rekeyed. You could decrypt it, store it somewhere and overwrite it when you have the new key. In pseudocode, it would be something like:

foreach($accounts as $account) {
  $iban = $account->iban;
  setNewKey();
  $account->iban = $iban; // at this point the new app_key would be used
  $account->save();
}

This also applies to the other fields I've mentioned. There is no double encryption or anything.

Firefly III will be slow on your device. I suggest something like memcached but it might not help a lot. Firefly isn't always as optimized as I wish it to be, and the queries can be very large. There are some places where I can still optimize but it's a trade-off between maintainability (of the code base) and optimization.

There is no way to share data over accounts, except for sharing a password. There's some background info on this in the list of often requested features.

[ndandanov]

Dear James,

Thank you for your fast and detailed response!
I guess I ran into issues using my scripts for decrypting the IBAN because it was empty anyway. I will do my best to upload the scripts (as a Gist?) and share them. I am very happy that they did what they were supposed to.

Would you like to elaborate on why Firefly III would be slow on my current device when you have the time? Is it because of the CPU architecture or the CPU frequency, or something else? I am trying to understand how to plan any future device upgrades and migrations. However, I will definitely try to set up memcached. :-)

Sorry for not going through the FAQ again before asking - I forgot that this was discussed there. I think in my case, sharing the password would be sufficient. :-)

Kind regards and all the best!

[ndandanov]

Hi again, James,

I successfully installed memcached and linked it with PHP via libmemcached.
This is the result:

memcached

memcached support	enabled
Version	3.0.0b1
libmemcached version	1.0.18
SASL support	no
Session support	yes
igbinary support	no
json support	no
msgpack support	no

Directive	Local Value	Master Value
memcached.compression_factor	1.3	1.3
memcached.compression_threshold	2000	2000
memcached.compression_type	fastlz	fastlz
memcached.default_binary_protocol	0	0
memcached.default_connect_timeout	0	0
memcached.default_consistent_hash	0	0
memcached.serializer	php	php
memcached.sess_binary_protocol	1	1
memcached.sess_connect_timeout	0	0
memcached.sess_consistent_hash	1	1
memcached.sess_lock_expire	0	0
memcached.sess_lock_max_wait	not set	not set
memcached.sess_lock_retries	5	5
memcached.sess_lock_wait	not set	not set
memcached.sess_lock_wait_max	2000	2000
memcached.sess_lock_wait_min	1000	1000
memcached.sess_locking	1	1
memcached.sess_number_of_replicas	0	0
memcached.sess_persistent	0	0
memcached.sess_prefix	memc.sess.	memc.sess.
memcached.sess_randomize_replica_read	0	0
memcached.sess_remove_failed_servers	0	0
memcached.sess_sasl_password	no value	no value
memcached.sess_sasl_username	no value	no value
memcached.sess_server_failure_limit	0	0
memcached.store_retry_count	2	2

However, I cannot notice any performance improvement.

I did not install igbinary or msgpack (more info here) which could speed up the application. Could you please check with your setup if you have these installed and advise if they would be actually helpful?

Thanks in advance!

[JC5]

I'm afraid there's not much to be done. The biggest bottleneck in Firefly III is the queries it executes upon the database. These are quite large, and not every view is very efficient. The code is efficient and clean, but the number of queries that are executed is very large.

So I cannot suggest anything, at the moment. Make sure that LOG_LEVEL is at warning (saves on disk IO). Make sure the cache_driver is memcached, and the session_driver is also memcached.

For the rest, it is up to me to optimize the views.

[ndandanov]

Hey, James,

Thank you for the advice!
In my .env file I have APP_LOG_LEVEL=notice, which I thought was less verbose than warning.

I applied the changes regarding the cache and session drivers. How can I reload the application so that it takes them into account? I thought that PHP should "see" the changes on the next requests without doing any reloads or restarts of apache.

In the current state, loading the dashboard takes around 6.2 seconds. I guess I can also take a look at the mysql database - perhaps I can tweak it or use mariadb instead.

I read somewhere that a careful choice of the storage engine could increase performance - currently my tables use InnoDB, but some benchmarks claim that myISAM is much faster and has smaller memory footprint.

I guess that in all cases, SQLite would be worse than a "dedicated" DB so I shouldn't migrate to it.

[JC5]

You're welcome! And thanks for the feedback.

Those setting should be applied immediately. SQLite will be worse, myISAM might help but be careful converting your database just like that.

[ndandanov]

Dear James,

Thank you for the helpful hints!
I played around with various parameters and tools in order to optimize my constrained Pi web server.

I started with a loading time of the dashboard of around 10 seconds using Google Chrome and across the network. During the loading, I could see that the apache2 process would take 60-80% of the CPU's both cores.

First, I tried to optimize the MySQL database by running tuning scripts (MySQLTuner and MySQL Tuning Primer). This did not provide much difference in speed.

Then, I created a test database with MyISAM storage engine instead of InnoDB. This required a slight change in one of the UNIQUE KEYs create statement due to a maximum key length of 1000 Bytes for MyISAM.

Unfortunately, no joy with this, too.

Then, I decided to optimize PHP as far as I could. I started with installing and configuring memcached a few days ago, but this wouldn't speed things up. Then I compiled and added the PHP APC User Cache (APCu). After configuring it, I didn't feel and measure any difference in speed.

The most effective means to increase performance was to turn on and configure the PHP OpCache, which stores precompiled script bytecode in the memory. This reduced the dashboard loading time from ~10 seconds to ~3 seconds.
I achieved similar performance when using Firefly III in Docker or in a VM on my PC (no PHP optimizations there, however).

So far, I am happy. After testing extensively, I will let you know if there are any shortcomings to this method.

Addition: By the way, when does Firefly send e-mails? So far I have received some only on error. Does it also send on certain in-app events, such as for paying bills or related to a budget?
Thanks!

[ndandanov]

I added the scripts for database re-encryption here:
https://github.com/ndandanov/firefly-iii-reencrypt-database

In case you need any help or assistance, please do not hesitate to contact me.

[JC5]

Wow, this is amazing, nice work!

I'm surprised OpCache changed so much, I had really expected the database to be the big bottleneck.

I'm going to extend the FAQ and link to your scripts! 👍

[JC5]

Oh and to answer your question: it currently only sends a test email, a registration email and errors. More app events may be handled by the Laravel Broadcast code in the future (which could in turn send emails, but it's a slightly different way of working).

[ndandanov]

Thank you very much for your support, James!
I will now happily enjoy my installation and let you know if I find anything else.

Keep up the great work!

[JC5]

Can do!