6 ms·
Show HN: I built this Postgres logger
Hey HN,
Some of you were really interested in Postgres logging with pgAudit in my previous post here: https://news.ycombinator.com/item?id=37082827 https://news.ycombinator.com/item?id=37082827
So I built this logger: https://rocketgraph.io/logger-demo https://rocketgraph.io/logger-demo
using pgAudit to show you what can be done with Postgres auditing. It offers some powerful features like "get me all the CREATE queries that ran in the past hour". These are generated by AWS RDS Instance running on my Rocketgraph account. Then they are forwarded to Cloudwatch for complex querying. In the future we can connect these logs to slack so you can get slack alerts when a developer accidentally DROPs a table.
If you like my work, please check it out here: https://github.com/RocketsGraphQL/rgraph https://github.com/RocketsGraphQL/rgraph
And if you want this logging on your own Postgres instance. Use https://rocketgraph.io/ https://rocketgraph.io/
And setup a project. pgAudit is automatically installed.
- vermon 3y agopgAudit is pretty cool, but it's a bit of a hassle for us currently because it keeps logging SELECT FOR UPDATE's even though SELECT is not allowed for the auditor, so if you implement stuff like simple queues with continuous polling it fills up the audit log with junk pretty fast. I opened a PR though so hopefully it gets fixed at some point.
- kvaranasi_ 3y agoInteresting. I did not know that. I have to dig into the source code then. Do you know any alternate logging tools that don't have this issue?
- vermon 3y agoI think pgAudit it still the best and it's not a major issue. You can try my PR that fixes this issue https://github.com/pgaudit/pgaudit/pull/219 https://github.com/pgaudit/pgaudit/pull/219 it should work and it should handle the other types of SELECT's that need update permissions but are not actually updating anything https://pglocks.org/?pglock=RowShareLock https://pglocks.org/?pglock=RowShareLock
- kvaranasi_ 3y agoOk, looking into it.
- danpalmer 3y agoFYI, be careful, see my other comments in reply but I think this is working correctly, and due to the fact that a `SELECT ... FOR UPDATE` is a write.
- kvaranasi_ 3y agoYes, I have been reading the conversation. That was so much to learn. I did not know that `SELECT ... FOR UPDATE` is "sort of" a write. I googled and found out this SO link: https://stackoverflow.com/questions/18879584/postgres-select-for-update-in-functions https://stackoverflow.com/questions/18879584/postgres-select... that corroborates what you have said.
- danpalmer 3y agoThis sounds like a desirable feature. SELECT FOR UPDATE results in a write where SELECT does not, and distinguishing between reads and writes is definitely something I'd want to be able to do here. Unless I've misunderstood the problem – I'm not sure I fully understand your PR. Perhaps being able to exclude those as a separate category would be good?
- vermon 3y agoWhat does a write in this context mean? I still need to execute the actual update clause to change the relevant data. EDIT: Since you control object auditing by granting/revoking permissions to the relevant relations I don't think it's possible to have another category there since Postgres itself doesn't differentiate between UPDATE and SELECT FOR UPDATE on permission level
- danpalmer 3y agoIt means that the `FOR UPDATE` part in `SELECT ... FOR UPDATE` causes a disk write, because it locks that row. It bumps the MVCC metadata, the transaction ID on the tuple I think(?) in order to prevent other transactions from modifying it. While the data hasn't actually changed, the availability of that data has changed, so I think it's reasonable to consider that a write from the database perspective. Although FWIW, I do completely understand your use-case of trying to quieten down logs for a queue!
- vermon 3y agoMake sense. I guess it could also be a flag for the audit module where you can toggle if SELECT permission for auditing includes the SELECT clauses that need RowShareLock as well or not.
- danpalmer 3y agoYes! I don't think that should be a default because I think it's "safer" and more correct to consider locks writes, but having it available for pragmatic use-cases like this would be nice.
- rsyring 3y ago> In the future we can connect these logs to slack so you can get slack alerts when a developer accidentally DROPs a table. What can you do about getting me a slack notice BEFORE a developer accidentally drops a table? ;)
- kvaranasi_ 3y agoHaha, we need Neuralink chips then.
- alFReD-NSH 3y agoDon't give them the DROP permission and have them go through a (automated) process to elevate their privilege when they need it.
- kvaranasi_ 3y agoHey wait, you have a point though. We can put our custom locks onto tables that doesn't let anyone delete a particular table. Let me think over it. Saving this comment for later. Thanks for the idea man.
- dewey 3y agoUhm, why would you not use PG permissions for that? That's what they are for.
- kvaranasi_ 3y agoI am assuming that the developer has necessary permissions. If he/she accidentally deletes the table, then that's a problem.
- dewey 3y agoThat means that the process is wrong. it sounds like they are doing day to day work with credentials that can drop tables, which is dangerous and should probably be done through code-reviewed migration tooling.
- e12e 3y agoThis looks great:) > Use https://rocketgraph.io/ https://rocketgraph.io/ And setup a project. What will the price and terms be?
- kvaranasi_ 3y ago1 week free trail. And for HN users I can give more time for the free trail. It'll be $50/mo. But it is worth a lot more. But hey, you can sign up and create a project without your credit card. Once free trail ends, you can pay with stripe. Let me know how you like it.
- deleted 3y ago[deleted]
- bdcravens 3y agoThis is just a front-end to CloudWatch Log Insights, right?
- kvaranasi_ 3y agoSort of. I created this page to just show what can be done with pgAudit. But what I do at Rocketgraph is more. I provide Authentication, Postgres and GraphQl console. Postgres logger is an extension for demo purposes. In the future I plan to add slack alerts when a developer DROP's a database accidentally. And much more. Configuring AWS RDS is a pain. I wrote about it here: https://blog.rocketgraph.io/posts/install-pgaudit https://blog.rocketgraph.io/posts/install-pgaudit That's why I automated the process. Now every project comes with Postgres and pgAudit configured, Authentication, GraphQL and front-end SDKs right out of the box so it will be easier to develop web applications.