From 26c280fc76404d04740e756a94e5b5ce05606ec1 Mon Sep 17 00:00:00 2001 From: "martin@ashbysoft.com" Date: Fri, 5 Nov 2021 12:45:45 +0000 Subject: Postgres query rewrite --- content/posts/2021-11-05-postgres-query-rewrite.md | 17 +++++++++++++++++ 1 file changed, 17 insertions(+) create mode 100644 content/posts/2021-11-05-postgres-query-rewrite.md diff --git a/content/posts/2021-11-05-postgres-query-rewrite.md b/content/posts/2021-11-05-postgres-query-rewrite.md new file mode 100644 index 0000000..32041be --- /dev/null +++ b/content/posts/2021-11-05-postgres-query-rewrite.md @@ -0,0 +1,17 @@ +--- +title: "Postgres Query Rewrite" +date: 2021-11-05T11:57:12Z +draft: false +--- + +Recently at work, I needed to be able to rewrite some [postgresql](https://www.postgresql.org/) queries which were coming from a closed-source application in order to integrate the app into our own system testing setup. Specifically I needed to replace instances of `current_timestamp` with invocation of `now()` function. + +I found a couple of options to do this: +1. [pgbouncer-rr](https://github.com/awslabs/pgbouncer-rr-patch) is a _patch_ for [pgbouncer](https://www.pgbouncer.org/), initially written by AWS labs, which extends pgbouncer to include rewrite and rerouting capability. +2. [pg_query_rewrite](https://github.com/pierreforstmann/pg_query_rewrite) is an _extension_ for postgres, which supports substitution of an entire matching query with another one. + +Unfortunately I hit some roadblocks with each. `pg_query_rewrite` simply wasn't flexible enough. I needed to do a string substitution on the query, not entire query replacement as I didn't have the exact set of every query the application would issue. Also providers of managed postgresql instances like Google Cloud or AWS won't let you install your own extensions. `pgbouncer-rr` was moderately difficult to configure given that I only wanted a single feature (rewriting) and none of the usual things pgbouncer is used for (connection pooling, load balancing). + +In the end, I found a neat library [pgproto3](https://github.com/jackc/pgproto3) which implements the postgresql wire format and can be used to write both a client and server! I used this to write [an application](https://github.com/patientsknowbest/pg-rewrite-proxy) that can act as a proxy to a postgresql server and will rewrite the queries before forwarding them. The proxy approach seems more flexible than an extension as it can be bundled either on the application server, or on the database server, or somewhere in-between depending on the requirement. + +At the moment this application only supports simple string replacement, but I hope to extend it with [LUA](https://www.lua.org/) support soon for arbitrary query rewriting. -- cgit v1.2.3-ZIG