Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Show HN: I built this Postgres logger

rocketgraph.io

Post reply on HN
Print view View original

1–10 of 49 posts

Show HN: I built this Postgres logger

#1

Post by kvaranasi_ »

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

So I built this logger: 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

And if you want this logging on your own Postgres instance. Use https://rocketgraph.io/ And setup a project. pgAudit is automatically installed.

Show HN: I built this Postgres logger
rocketgraph.io

Re: Show HN: I built this Postgres logger

#2

Post by vermon »

pgAudit 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.

Re: Show HN: I built this Postgres logger

#3

Post by kvaranasi_ »

vermon wrote: post #2

pgAudit 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.

Interesting. 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?

Re: Show HN: I built this Postgres logger

#5

Post by kvaranasi_ »

rsyring wrote: post #4

> 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? ;)

Haha, we need Neuralink chips then.

Re: Show HN: I built this Postgres logger

#6

Post by alFReD-NSH »

rsyring wrote: post #4

> 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? ;)

Don't give them the DROP permission and have them go through a (automated) process to elevate their privilege when they need it.

Re: Show HN: I built this Postgres logger

#7

Post by kvaranasi_ »

rsyring wrote: post #4

> 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? ;)

Hey 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.

Re: Show HN: I built this Postgres logger

#8

Post by vermon »

kvaranasi_ wrote: post #3
vermon wrote: post #2

pgAudit 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.

Interesting. 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?

I 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 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

Re: Show HN: I built this Postgres logger

#9

Post by palijer »

rsyring wrote: post #4

> 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? ;)

Haha, exactly, but if you have developers who can drop production tables without the org knowing in advance and reviewing the change, you already have a big problem.

Re: Show HN: I built this Postgres logger

#10

Post by palijer »

rsyring wrote: post #4

> 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? ;)

Haha, exactly, but if you have developers who can drop production tables without the org knowing in advance and reviewing the change, you already have a big problem.

All our db operations like this go through our regular code review process for the db maintainers team, and instead of dropping tables, we just rename them with a prefix "to_be_dropped" as a scream test, then actually drop them a month later or so.

Recovery is much simpler for renaming than dropping.

Post reply on HN