[RFC] Plugins

[levkk]

I've been playing around with the idea of "plugins", extendible "things" we can inject at any point in the client/server lifecycle to do stuff. A few ideas I've prototyped so far:

• table_access: block queries made against configured tables; Postgres permissions only allow so much, e.g. we can't block access to the system catalog without breaking the database.
• intercept: capture a client query and return a fake result instead of going to the Postgres server; not entirely sure how this can be used, but seemed like a fun idea to play around with.
• query_logger: log all queries to stdout, great for debugging applications
• prewarmer: run a query on server startup to perform some kind of task that will pre-warm the connection for the client.

These are not prescriptive in any way, and only serve to illustrate the use case behind plugins. PgBouncer had a patch that allowed query rewriting 1, we could introduce that as a plugin as well.

Another interesting discussion topic is how to load/configure these plugins. The way it's done now is plugins are part of the code and are turned on and configured via pgcat.toml. This is not really a plugin system though, since real plugins have to extend the existing code base & be completely optional. Another use case is for projects to add functionality to pgcat that they may not want open sourced (yet, or ever) without having to fork. A plugin could be a great way for someone to introduce a functionality that's specific to their organization/project and dynamically load it, without worrying about maintaining a fork and git conflicts down the line.

What would be great to get out of this RFC is:

• Some kind of interface for plugins, so they can be loaded and used in different parts of the client/server lifecycle, e.g. pre-query, after-query, pre-connect, after-connect, etc. So we need to define a lifecycle "policy" and allow to inject the plugin at any of those points.
• A way to dynamically (or statically) load the plugins at either compile time or runtime, whichever is best from a safety, performance and ergonomic perspectives.
• Anything missing from above.

[liaden]

A plugin could be a great way for someone to introduce a functionality that's specific to their organization/project and dynamically load it, without worrying about maintaining a fork and git conflicts down the line.

Definitely. Ignoring git conflicts and being able to extend pgcat would be a nice feature relative to other postgres poolers. I wonder if it could be useful way to inject functionality between postgrest and postgres.

My preferred dev experience for writing a plugin:

1. Create a rust project.
2. Add pgcat to it as a dependency.
3. Use a macro from pgcat to construct the main function.
4. Write my plugin(s).
5. Add another plugin crate as a dependency to include 3rd party plugins.
6. Compile the binary.
7. Ship the binary + config to use.

Some considerations:

• Runtime or compile time ordering of plugin execution (similar to Rails middleware)?
• Forcefully excluding a pgcat written plugin from the resulting binary? Useful for preventing experimental features.
• Versioning of plugins independent of pgcat?
• Hot reload with new binary? If I am writing one or more plugins, I probably will be deploying more often.
• Compile time approach allows me to get the compiler to yell at me if I do dumb things.
• Run time loading of plugins allows for writing a plugin in a different language. This could be done with the rust FFI anyways even with the compilation approach.

Some possibly useful plugins:

• AWS RDS IAM authentication
• Functionality around row level security or column level security
• Audit trail like functionality (or other functionality that might normally be done as a trigger).
• Query caching
• Wrapping a COPY FROM STDIN query to upload the CSV to an s3 bucket
• Firing a lambda or similar
• After an update, being able to emit an event about the delta of changed rows (e.g. debezium but without having to set a table's replication identity to full).
• Create a "virtual column" like Postgres' generated column without having to do the storage. The virtual column could also be contents of a document on s3 or similar.

There are probably other plugins that would be useful for various postgres forks like TimescaleDB?

[calcsam]

This is awesome. I personally need to intercept writes and validate them with a remote API, and writing a plugin will be much simpler than maintaining a fork.

If I was in your shoes I wouldn't worry about needing to ship a complete lifecycle API out of the gate. You can start with whichever hook you think there's the most need for, especially if you're confident what the API for it should look like.

[calcsam]

Another validation one. I asked a friend who was the CTO of an analytics startup. They stored event data as JSON blobs in Postgres, with some fields as implicit foreign key relationships, but they didn't have a way for the DB to enforce that the IDs being referenced exists in the relevant tables.

[adriangb]

Plugins would be great. Personally I'd be fine making a rust project and adding pgcat as a dependency if pgcat provided a nice way of "insert your code here via callbacks" but still managed everything else so all I have to do is write a couple Rust functions and build the binary. Some use cases I've had are remap usernames/passwords, do more advanced/custom query parsing and rewriting (possibly returning errors, eg. to prohibit EXECUTE), do custom mapping of incoming connections to destination servers.