pgaudit instead of supa_audit?
has anyone tried using pgaudit instead of supa_audit?
23 Replies
Same, I would like to know as well.
As I understand pgaudit would log to logs instead of inserting into database like supa-audit.
But I’m curious about the pros and cons
But with supabase 7 day logs, I don’t think it’s reliable long term
yea looks like pgaudit is for raw logs, supa-audit is for application-level logs.
my revision of supa-audit is here https://gist.github.com/joshxyzhimself/22a3cc49becacb9b09ca92d812bc025b, might still got room for improvements.
i got no idea how to use indices like the following though:
https://supabase.com/blog/audit says it's for performance but i got no idea how to query indices yet lol, is it just the same as querying regular tables?
aight i might need to study it https://blog.devart.com/postgresql-indexes.html
What's wrong with the current supa-audit? What are you trying to improve?
hmm to me it looks like supa audit redundantly stores new record and old record. on INSERT and UPDATE I can just store new. on DELETE I can just store old.
That's a good point. Perhaps, they just wanted a easier comparison, than finding what was the previous value (in many rows prior)
After re-reading your sentence, I finally understood what you meant. I use this Issue to learn about the use cases and syntax: https://github.com/supabase/supa_audit/issues/14
GitHub
Docs for common use cases · Issue #14 · supabase/supa_audit
Hey, I find this extension really useful 🙏 I am not sure about how some of the use cases should be approached with such a table. Getting the history of a particular row If I I want to get the histo...
@NanoBit thanks man, checking it!!
Hi. I just enable the pgaudit extension but don't see any AUDIT logs being written to the Postgres logs. Any ideas how to see the pgaudit entries?
There's zero documentation on what to do after you enable the extension.
Yea agreed. Part of that is why I chose supa-audit instead. It's easier to understand for me. I read the pgaudit docs many times, and it sounds like it should just "work" when enabled/installed.
https://github.com/pgaudit/pgaudit
GitHub
GitHub - pgaudit/pgaudit: PostgreSQL Audit Extension
PostgreSQL Audit Extension. Contribute to pgaudit/pgaudit development by creating an account on GitHub.
Thanks @NanoBit for confirming. Are you using supa-audit for DDL changes?
Sorry, I'm not familiar what DDL means?
Data Definition Language for create/modify/removing database objects
like ALTER table ADD COLUMN blah
Ah, you mean for table structure right?
Yup.
No, I used Prisma for migrations. Supabase has their own migration tool also
Supa-audit is only for data changes
Wow, it tracks all inserts and updates?
GitHub
GitHub - supabase/supa_audit: Generic Table Auditing
Generic Table Auditing. Contribute to supabase/supa_audit development by creating an account on GitHub.
Depending on which table you want it to track.
INSERT, UPDATE, DELETE, TRUNCATE
only those 4
very simple also
that is pretty nice.
Also a caveat with pgaudit (which I mentioned above), even Pro account can only keep last 7 days logs
which is not enough for my case
The pros of pgaudit though to my understanding is faster db IO compared to supaaudit
Yeah, pgaudit is adding additional inserts. Did you install that through the SUPAUTILS extension?
Or can you just create the extension with
Ok, looks like SUPAUTILS is something else. I had to install it by running the SQL from github. Let's see if this works. Thanks, @NanoBit
I thought this was possible as well, but since local dev doesn't have it, I had to add it via SQL file