[HN Gopher] Parsing the Postgres protocol - logging executed sta...
___________________________________________________________________
Parsing the Postgres protocol - logging executed statements
Author : jascha_eng
Score : 35 points
Date : 2024-03-14 17:18 UTC (5 hours ago)
(HTM) web link (kviklet.dev)
(TXT) w3m dump (kviklet.dev)
| jascha_eng wrote:
| I built a postgres proxy for https://github.com/kviklet/kviklet.
| And documented how I did this because I found the byte level
| parsing quite interesting compared to the usual higher level REST
| API calls that most of my dev work consists of these days.
| Thought it might interest some of you as well!
| jascha_eng wrote:
| I failed the link in the OG post and cant seem to edit or even
| delete it :(
|
| https://kviklet.dev/blog/parsing-the-postgres-protocol/
|
| This is the correct one if someone wants to take a look.
| dang wrote:
| You submitted the right one, but our software follows redirects
| and canonicals and eventually hit https://kviklet.dev/landing-
| page/blog/parsing-the-postgres-p....
|
| I've fixed it above now.
| jascha_eng wrote:
| Oh yeh something is off with my Cloudfront setup. I'll try
| fix it.
|
| Thank you so much!
| conradludgate wrote:
| Cool! At Neon[0], I work full time on our custom postgres
| proxy[1]. It's a very nice protocol to work with, although our
| usecase is quite a bit more complex compared to the ideas
| presented in the post.
|
| Neon databases scale to zero, so the proxy needs to spin up
| databases on the fly. The proxy doesn't do that but it knows if
| the databases is running and asks our control plane to schedule
| it if it isn't. It's a fun service to maintain.
|
| The biggest pain is error handling. Postgres is really bad for
| error messages and codes. The only available code we can use is
| usually protocol violation...
|
| [0]: https://neon.tech/ [1]:
| https://github.com/neondatabase/neon/tree/main/proxy
| bearjaws wrote:
| Yeah I have tinkered with the PG protocol to de-identify data
| as its transmitted and the error handling is pretty bare bones.
| I think its reflected pretty well in how connection errors and
| even query errors come back as "you have an error" from
| Postgres most times lol.
| bearjaws wrote:
| Reminds me of a project I made to de-identify PHI as it was
| streaming across PG.
|
| The documentation on postgres data is very well done, and it's
| pretty straight forward to understand, you can find it here for
| anyone who is interested:
| https://www.postgresql.org/docs/16/protocol-message-formats....
|
| The real pain is encryption now days, it was easy to make
| something in a day that could proxy requests but getting it to
| handle all the different connection errors and SQL errors was a
| nightmare.
| aargh_aargh wrote:
| While I enjoyed the read and protocol parsing is always an
| interesting exercise, I'm still left wondering why a proxy was
| deemed necessary. Why not parse the logs instead?
| jascha_eng wrote:
| You can parse the logs to figure out executed statements. But
| then you only know the Postgres user that executed them.
|
| In my experience there is often shared users, e.g. a readonly
| user or even for maintenance/operational tasks ("I need to fix
| prod real quick") a shared admin user.
|
| The idea of Kviklet as a tool is to not share the password for
| such a user but instead use Single Sign on for authentication.
| Meaning multiple users can use the same DB user but the
| execution of statements is still linked to their individual
| accounts (e.g. Google Account) in the audit-log.
|
| Hope this makes sense?
___________________________________________________________________
(page generated 2024-03-14 23:00 UTC)