DB backup size & redundant data (deleted secrets, old users) – Passbolt CE 5.11.0

[2Face]

Hi everyone,

I’m running Passbolt CE 5.11.0 and have set up automated database backups every 24 hours and I save up to 30 days before deleting older backups. While reviewing the backup content, I noticed that it still contains a lot of what seems to be redundant data, for example:

• Secrets that are marked to be deleted

• Old/deleted users

Is this expected behavior? I assumed that once a secret is deleted (or pending deletion) or a user is removed from Passbolt, the corresponding database entries would be purged, maybe at a later time.

My concern is that over time, the backup size will keep growing with “dead” data that never gets cleaned up, making storing backups less practical over long periods .

A few questions for the community:

1. Is there a built-in mechanism in CE 5.11.0 to purge old/deleted records from the DB?

2. Would it be safe to manually prune certain tables (e.g., secrets, users) from the backup before storing it, or does that risk breaking referential integrity on restore?

Thanks in advance for any insights!

[gyaresu]

G’day 2Face,

Yes, this is expected. When you delete a user or a resource in passbolt, the row is flagged as deleted but kept in the database. The encrypted password itself is removed straight away, so what is left behind is just the metadata (a row in users, a row in resources with the username/URL/description blanked out, and so on) plus the audit history.

To answer your questions directly:

Is there a built-in way to purge old data? There is one command, bin/cake passbolt action_logs_purge --retention-in-days N --dry-run. It clears out the noisier entries in action_logs (logins, list views, page loads) older than your chosen retention. It does not touch the related history tables (entities_history, secret_accesses, secrets_history, permissions_history, folders_relations_history), and there is no equivalent for deleted users, groups, resources, GPG keys, or secret revisions.

Is it safe to manually prune the backup? Not really. Those deleted rows are still referenced by foreign keys from a lot of other tables, so a straight DELETE will either fail or leave broken references in the audit log. There is no shipped tool to do it cleanly.

If you want to see where the size is actually going, this query is the easiest way:

SELECT table_name,
       table_rows,
       ROUND((data_length + index_length) / 1024 / 1024, 2) AS size_mb
FROM information_schema.tables
WHERE table_schema = 'passbolt'
ORDER BY (data_length + index_length) DESC;

In most cases the bulk is in the audit history tables, not in deleted users or resources. For backup size specifically, compressing the dump (mariadb-dump --skip-extended-insert | zstd or | gzip) is usually the simplest fix, since this kind of data compresses very well.

Useful commands: https://www.passbolt.com/docs/hosting/useful-commands/#data-maintenance
Anto’s blog post: https://www.passbolt.com/blog/automating-passbolt-maintenance-part-2-prepare-the-first-run-on-large-databases

Let me know if this helps or you’d like a more specific answer.

Cheers
Gareth

[2Face]

Thank you.

I ran the clean up command you motioned and also the maintenance script by Anto.

I ran the SLQ query you gave to check size on the live DB and a backup I took around 4 days ago. The live seem to be larger even after running the clean up and only saving the last 60 days.

Live DB

DB Backup from 4 days ago

Oh well… Total number of rows are lower I guess.

Thanks for the help.

Edit 1: I am using the passbolt cake command to take backups of the DB as per the backup documentation here.

[gyaresu]

G’day Two.

You don’t share the action_logs_purge command that you used.
It looks like you may have run it once with the default batch size (100000).
If you run it a few times, set a cron job, or use increase the batch size (-l), then you can remove all the action logs and save your ~300MB of space.

https://www.passbolt.com/docs/hosting/troubleshooting/purge-action-logs/#purging-action-logs

I hope that helps.

Cheers
Gareth

[2Face]

Good Day,

Sorry I forgot to mention it, the command I used was,

sudo su -s /bin/bash -c “/usr/share/php/passbolt/bin/cake passbolt action_logs_purge -r 60” www-data

After you mentioned the batch size, I ran it with a larger batch size and a smaller retention,

sudo su -s /bin/bash -c “/usr/share/php/passbolt/bin/cake passbolt action_logs_purge -r 30 -l 5000000” www-data

But still looks the same ?

Thank you

[gyaresu]

Hey 2face,

For backups taken with cake passbolt sql_export, the live table size on disk is not what determines backup size. That command wraps mysqldump, which dumps live rows as INSERT statements. So if the purge actually deleted rows, the resulting .sql archive is already smaller, even though the action_logs.ibd file on disk has not shrunk. The 354MB you are seeing on the live table does not carry across into the dump.

The clearest way to verify is to compare the size of the .sql output (or its .gz/.tar.gz form, if you compress it) from before and after a purge run. If that has come down, the purge is doing its job as far as backup archiving is concerned.

To confirm rows were actually deleted, two checks:

• The command itself prints the count on its last line, e.g. 100000 action logs entries were deleted.
• SELECT COUNT(*) FROM action_logs; before and after will drop accordingly.

If you would like to see what is still eligible for purging versus what is being kept on purpose, run a verbose dry-run:

sudo su -s /bin/bash -c "/usr/share/php/passbolt/bin/cake passbolt action_logs_purge --dry-run -v -r 30" www-data

This groups the eligible rows by action and gives you a count. By design the purge only removes a curated allowlist of noisy read-only actions (Index, View, Get, healthchecks, and similar). Anything tied to a resource, folder, or group write, or to a share, is preserved for the audit trail, so the row count will not drop to zero regardless of how aggressive your retention setting is.

For ongoing maintenance, scheduling the purge as a cron job is the recommended pattern, for example monthly:

0 2 1 * * sudo su -s /bin/bash -c "/usr/share/php/passbolt/bin/cake passbolt action_logs_purge -r 90" www-data

I hope this helps.

Cheers
Gareth

[2Face]

Hi gyaresu,

Thank you for the help.
As mentioned I checked the count from action_logs before and after the cleanup, there is a reduction.
Also exported DB from the backup has dropped in size ~20MB compared to a previous backup.
The dry run also confirms that there is nothing that can be purged.

Thank you for your help gyaresu.