[HN Gopher] Postgres Audit Tables Saved Us from Taking Down Prod...
___________________________________________________________________
Postgres Audit Tables Saved Us from Taking Down Production
Author : thunderbong
Score : 52 points
Date : 2023-08-12 14:50 UTC (8 hours ago)
(HTM) web link (www.heap.io)
(TXT) w3m dump (www.heap.io)
| pyuser583 wrote:
| Don't databases natively use audit tables to prevent transaction
| errors? Shouldn't there be a native way to persist them?
| [deleted]
| adrr wrote:
| Transaction Log isn't easy to reverse to raw SQL, it's a binary
| log. I also don't think it has meta information like timestamp
| or source.
| paulryanrogers wrote:
| Some can use logical logs which can be decoded by 3P
| software. Though I agree it would be noisy and require some
| tooling/filtering to be manageable.
| matdehaast wrote:
| Heap used to bring out so many great engineering deep dives. I do
| miss them publishing more
| hinkley wrote:
| Changing priorities or did someone leave who was pushing them
| to publish?
| phyzome wrote:
| From experience at other companies, I feel like it's usually
| just this One Person who goes around and bugs people to
| actually sit down and write up the interesting things they
| have to say. When they leave, the posts stop.
| simonw wrote:
| There's a bug on that page - the text "The SQL to restore the
| deleted metadata looked a little like this:" is followed by a
| blank space.
|
| I dug around in the HTML source and figured out it's supposed to
| show content from this Gist: https://gist.github.com/matt-
| heap/8cbe373e5c06c76fe65b9781f9... INSERT INTO
| pg_dist_shard SELECT logicalrelid, shardid, nodename FROM
| dist_shard_audit WHERE action = 'DELETE' AND log_time >
| now() - '1 minutes'::interval;
| simonw wrote:
| I implemented a similar audit table pattern to the one described
| in this post for SQLite as a Python library here:
|
| sqlite-history: https://github.com/simonw/sqlite-history
| pixel_tracing wrote:
| I'd like to push back on these suggestions a bit.
|
| 1. Why use Postgres distributed cluster vs say an incremental
| store that supports real time data like Materialize? A streaming
| database sounds like the right use case for your requirements no?
| Under 1 min real time latency? Is Postgres distributed able to do
| it efficiently? (Never tried Postgres dist.)
|
| 2. Why use typescript at all? Pick a language that actually
| enforces class validation and enforces type validation baked into
| the language itself (like Rust or Go)? Sounds like programmer
| error is root cause of issue that should be looked into as a
| mitigation step (add real class validation at the minimum)
|
| 3. Regarding audit tables, are you also keeping audit tables for
| user and events tables too? That would seem... excessive and now
| duplicated data (especially billions of rows). Doesn't the
| database come with audit tables baked into it?
| habitue wrote:
| 2. Typescript is the language they're using. Generally people
| don't change their entire codebase to a different language
| because they run across a bug that would have been
| hypothetically solved by a different language. Additionally,
| this is a validation / coercion issue. It is a risk at the
| boundary of any two systems, you have to translate network
| bytes into a type your type system understands. If you
| translate it wrong, the type system is helpless to save you.
|
| 1. A streaming database isn't necessarily what they need here,
| postgres is plenty fast for most use cases. I'd move to a
| specialized tool like materialize if they've squeezed all of
| the juice out of postgres.
|
| 3. Postgres doesn't have audit tables built in, though there
| are multiple tools and plugins for postgres that can hook into
| things and do it for you. They went with a custom trigger
| solution, maybe that was sufficient for their use case.
| jtchang wrote:
| > Although we use Typescript to mitigate these types of bugs,
| when we're working with external systems, it's easy to find
| yourself in a situation where the types are lying to you.
|
| What does this mean? If you use typescript doesn't it prevent
| these types of bugs?
| Waterluvian wrote:
| They were just accepting incoming data as-is and typescript
| won't do a thing for you.
|
| Ideally you validate incoming data using jsonschema or whatnot.
| delusional wrote:
| In the static analysis space this is a fundamental issue. We
| can't really usefully reason about the actual system, the state
| space simply becomes too large. So you have to reduce your
| problems to some axiomatic abstraction. In type systems we
| mostly cut those axioms at the system boundary. This is mostly
| because type systems are often incompatible and therefore can't
| automatically be analyzed across the boundary.
|
| Imagine some pipe. You can put in some bytes and get some bytes
| out again. As far as you know, you have to put in 16 bit
| integers, and you get 16 bit integers out. You put that in your
| type system: int16_t communicate(int16_t in);
|
| Now what you don't know is that if you provide the remote
| system with the integer 0, it will send back a 0, followed by
| the string "hello world". Your type system can't catch that
| because you've never told it that it can happen. The axioms the
| static analysis was built on didn't match reality, and
| therefore the analysis itself isn't sound.
| jackweirdy wrote:
| If a property is a string in your typescript interface but a
| number in your external service (api, DB, whatever) the
| typescript compiler would not know
| koolba wrote:
| Or worse if you blindly deserialize JSON and cast it as
| whatever you _want_ it to be without validation...
| awoimbee wrote:
| Typescript does compile time checking only. Almost all
| TypeScript programs I've seen just decode JSON received and
| slap an `as SomethingType`. TypeScript is not strict, so it's
| easy to write but you can still get type errors.
| thewix wrote:
| This is why I use codec libraries like zod or io-ts (purify
| has them built in also) at my boundaries.
| hinkley wrote:
| For instance I am currently trying to file a patch for a TS
| project. It is advertised as an npm module, which means if
| you pass a string to a function expecting a number it will
| happily do string concatenation on it instead of addition.
| There are no internal checks because TS, but that only works
| if you call it from TS. Otherwise authors have a false sense
| of security and make bad decisions.
| geraldwhen wrote:
| That's what's class-validator is for. You can ensure your
| apis are doing sensible things.
| itpragmatik wrote:
| When I read the heading I thought these are out of the box audit
| tables provided by Postgres. But the article then explains these
| are custom tables created by author - so this could very well be
| applicable to MySQL or any other databases
| time4tea wrote:
| aka, we don't do any input validation. Oops.
| DevKoala wrote:
| Essentially.
___________________________________________________________________
(page generated 2023-08-12 23:01 UTC)