Audit Logs - Ecto Database

GitHub - bitcrowd/carbonite: Audit trails for Elixir/PostgreSQL based on triggers · GitHub - came across this sometime ago.

I would consider that path even if you were going to implement the auditing yourself. The functions are part of the public API, so the chances that there will be a breaking change are pretty small.

I don’t want to speak for José and his team, but in Dashbit’s Bytepack, they included an AuditLog struct and plugs, which you may find useful:

https://github.com/dashbitco/bytepack_archive/blob/main/apps/bytepack/lib/bytepack/audit_log.ex

and, Hex.pm also has an implementation that is similar but different:

https://github.com/hexpm/hexpm/blob/main/lib/hexpm/accounts/audit_log.ex

We’ve been using an adapted version of Bytepack’s AuditLog for I think about a year now, and it’s been working great for us. While we don’t query the data unless needed, Hex.pm’s version also has indexes on the JSONB column to speed that up.

I think the first main question you need to ask yourself is: Are you auditing changes to rows in tables, or are you auditing actions performed by users?

The distinction is this: When auditing users, my experience is that you are better off thinking in terms of logical operations (often at the API / controller layer) that users perform, the parameters that they pass in, and include information like ip address, request id, and authorization information. If you are auditing rows on tables, then yeah you’ll maybe want something Repo or trigger based.

In my experience is that most of the time you want an audit log you’re really trying to audit users, isn’t really well handled by the trigger or Repo based approaches. A single user operation will often touch multiple rows in multiple tables, and as your application develops you may need to use things like insert_all or update_all which are more difficult to track from a Repo perspective.

Yeah, for the user-level operation auditing purposes I’d just add an audit trail storing in a separate DB table inside the context functions. Everything that needs to interact with the persistent storage should only touch the context functions anyway.

So you just append a few lines of code in there and you’re set.

Thank your very much Ben,

And those are good questions to address the problem. My answer is a bit of both. We need someone to blame (user actions) and what did they do (changes to the rows). I can propose the final user just to have a limited information about user information but knowing the users they always want more so I am planning for the hole enchilada.

So you are stating a very important issue and that’s what happens when a action/transaction touches multiple tables… still not sure how am I going to address this use case but carbonite seams appealing.

I will review the bytepack solution proposed by @jeroenvisser101 and carbonite suggested by @kartheek .

Guys, do you think this is a common “problem”? do you think Ecto should have an API to address it? What do you think?

Thanks again, best regards.

Joaquin Alcerro

There are some solutions built on top of postgres :

• pgAudit - https://www.pgaudit.org/

The goal of the pgAudit is to provide PostgreSQL users with capability to produce audit logs often required to comply with government, financial, or ISO certifications.

I came across pgAudit in google cloud docs and aws docs.

• Debezium - https://debezium.io/

• Audit Trigger for 9.1+ - Audit trigger 91plus - PostgreSQL wiki

Carbonite mentions Audit Trigger 91 as its inspiration - GitHub - bitcrowd/carbonite: Audit trails for Elixir/PostgreSQL based on triggers · GitHub.

The trigger-based table versioning derives from audit_trigger_91plus, an “example of a generic trigger function” hosted in the PostgreSQL wiki.

Carbonite has a section in their docs about these - Carbonite — Carbonite v0.16.1