How do I apply this to my database? about supa_audit

[bvkimball]

@olirice I am curious how you get same/similar functionality with pgAudit, i understand the flexibility of pgAudit to log based on a role and grants can be very granular.

I think a lot of people (or just me) have come to this project looking to implement table history for their application users. For this use case pgAudit doesn't really work because i can't query the log (can I?). For the use-case of User Edit History would this repo or approach be recommended or is there another option?

[olirice]

I think a lot of people (or just me) have come to this project looking to implement table history for their application users

that's fair

because i can't query the log (can I?)

correct, not without querying logs

For the use-case of User Edit History would this repo or approach be recommended or is there another option?

yes, for the situation you described this repo would be a good option!

[tristdrum]

Hey @olirice!

Epic stuff! I've been going over the articles, code and issues for the better part of the morning.

I'm transitioning a live project to Supabase soon (hosted by Supabase) and want to use this for a particular table. I have the project set-up and can't seem to install this on the schema natively (as mentioned above).

Two quick questions on this issue:

1. I'm wondering if it will soon be available (as a built-in) at https://supabase.com/dashboard/project/<reference_id_here>/database/extensions and if I should wait for that?
2. I'm hoping to not have to run the SQL manually, so that I get all the future gains as the extension itself does. What would you recommend I do?

Thank you!

[olirice]

can't seem to install this on the schema natively

what error are you running into?

---

Our official recommendation for auditing is to use pgaudit

This repo is fully functional but mostly intended as a companion for the blog post

I'm hoping to not have to run the SQL manually

We don't offer supa_audit on the hosted platform but so executing the SQL in your editor if via migrations is the only way to get it running.

Since its a SQL only extension you can manually install it by executing this file without the 2 highlighted lines. Any future updates will continue to have a supa-audit--0.3.1--x.x.x.sql upgrade script that you can similarly copy/paste

[olirice]

Self Hosted

supa_audit is set up as a native extension postgres extension. If you can connect to the machine running postgres, you can install by cloning the repo and running

make install

then create extension supa_audit in the DB

---

Managed Platform

If you're on a managed platform (RDS, Google Cloud SQL etc) you won't have enough access to install it that way. But, since the extension is all SQL, you can copy the contents of the supa_audit--0.2.1.sql and execute it in your DB.

If you execute the SQL, it will not be necessary to run the create extension command.

Be sure to make a note of the version number at the time you install so you can apply the upgrade patches as they become available e.g. supa_audit--0.2.1--0.2.2.sql

[dstroot]

Thanks! I am on a managed platform and was hoping to avoid manually applying the migrations. I was thinking there could be two approaches to the *.sql. a) A migration from prior for people already running supa_audit, and b) a current "full install" .sql that you just run, and then from that point just apply migrations as they come out.

Super cool extension BTW!

[olirice]

b) a current "full install" .sql

This would be the supa_audit--0.2.1.sql file. Its a complete install assuming you're on a new DB

a) A migration from prior for people already running supa_audit

That would the migrations in the form supa_audit--<old_version>--<new_version>.sql

---

For example: we currently provide supa_audit--0.2.1.sql, for new installs and supa_audit--0.1.0--0.2.0.sql + supa_audit--0.2.0--0.2.1.sql for people who had previously installed the extension and now want to upgrade to the latest version.

[reisr3]

It's weird that this very helpful extension isn't available via the Extensions area in hosted Supabase. I ran the SQL manually but I'd much prefer installation via your platform.

[olirice]

We make lots of prototypes at supabase!
Many of them are functional but not a generic enough fit for our user base to add them to the platform. In the case of auditing, we wanted to keep the community on pgaudit, which is much more flexible/scalable. That said, I'm glad you see value in this project, it was fun to create