I've been working on meeting compliance standards for on-prem databases like PostgreSQL, and one requirement is to provide audit logs. However, enabling the slow query log for every single query isn't recommended for production systems, and I've found that pgAudit can create too much I/O load.
To tackle this, I'm developing a straightforward proxy that handles authentication and sets up connections, while asynchronously parsing and logging all queries without taxing the database's performance. So far, it's running well, but I still need to conduct load tests and check how it behaves under heavy queries or slow database responses. I also created a similar setup for MySQL and am considering open-sourcing this proxy.
I'm curious about whether this kind of solution would interest others or if it raises red flags for professionals in compliance or database management.
5 Answers
Have you checked out other tracing tools? Something like Coralogix could help with logging and tracing without the overhead of a proxy. This might let you monitor transactions effectively without compromising the database performance too much.
If you decide to go with a proxy, make sure it's super reliable. Proxies can introduce latency and become bottlenecks. For comprehensive auditing, consider triggering processes within the database itself, like using database triggers to log changes to data rather than sending all that info through a proxy.
It's crucial to clearly define what you need to audit before using a proxy. Audit logs vary widely depending on whether you're looking at session activity, query executions, or schema changes. Some organizations only log failed login attempts, while others create separate audit logs through logical replication. A proxy based approach might be too complicated and could create a point of failure in your database interactions.
I think pgAudit might be the best route given your compliance needs. It’s designed for high standards, even if it comes with an I/O cost. Plus, there are other tools to complement pgAudit that you might find beneficial.
I would stick with established tools instead of building your own proxy. Using something like nginx is usually better for security and maintenance, unless there's a specific need that can't be met by existing solutions.
Related Questions
How To: Running Codex CLI on Windows with Azure OpenAI
Set Wordpress Featured Image Using Javascript
How To Fix PHP Random Being The Same
Why no WebP Support with Wordpress
Replace Wordpress Cron With Linux Cron
Customize Yoast Canonical URL Programmatically