Current status of custom FDW?

[beargiles]

What's the current status of creating a FDW? I know you were working on it when we chatted over a year ago and had made some progress but still had a way to go.

Is it something where subgoals would make sense? E.g., supporting a read-only FDW first, then adding support for 'insert' and 'update'?

Is there anything that someone could help with?

For people with no idea what I'm referring to - a "FDW" = "Foreign Data Wrapper". In my side-project I have user-defined types that can hold cryptographic material like digital certificates (aka SSL certs) and encrypted private keys. They need to be loaded somehow - I can create user-defined functions that read them from local files.

However I think a cleaner approach would be creating a custom FDW for them - you would specify the location of the file and it's top-level encryption key when you define the FDW, but could then treat it like any other database when you're looking for encryption keys. This is somewhat more secure than keeping the values within the database itself since a database dump won't include them and the top-level encryption key used with the FDW should have been provided by something outside of the database itself. (An attacker could still get the keys from a filesystem dump but the top-level encryption key won't be included in that dump.)

There's also a modern twist since many sites will now prefer to use something like Hashicorp Vault instead of deploying files to the server instances. In this case the FDW would make making a call to an external REST service, not reading a local file, but the resulting 'table(s)' should look the same.

Here's the required callbacks: https://www.postgresql.org/docs/current/fdw-callbacks.html

[jcflack]

What I remember from looking at it last was that the callbacks involve a bunch of native PG structs to be somehow made available for manipulation in Java.

That makes it something likely to be much more pleasant to implement over the work currently in progress in PR #399. I don't know if you've had a look at that yet.

[beargiles]

Okay. I have the attention span of a squirrel (thanks ADHD!) but I can probably hit a few milestones below:

• dummy package - you can define the FDW but nothing else. (No jar yet.)
• add: you can provide a jar in the FDW definition but nothing beyond a 'success' notice.
• add: additional options available to the java code.
• add: you can retrieve the metadata*
• add: you can retrieve values.

Advanced read-only:

You can push queries onto the FDW. By default PostgreSQL will load everything and then perform any filtering itself. If you can push (supported) queries onto the FDW then it can perform that filtering itself.

I consider this more important than adding read-write since my primary objective is to support extraction of cryptographic credentials from standard external files and the immediately putting them into a corresponding UDT. For obvious reasons I don't want the FDW to return everything.

(In fact an unrestricted query should probably result in an error - convention be damned!)

For most developers the main concern will be efficiency. There's no point in burning cycles and network bandwidth to provide data that will be immediately dropped.

[jcflack]

This'll probably need a good design document that evolves for a while before code is written. At first glance, it appears to me:

• The FDW handler function must be in a compiled language. So PL/Java will need to have a single, fixed one.
• The handler function returns addresses of callbacks, and it is passed no arguments, so the callback addresses it returns can't depend on anything. They, too, will have to be fixed, and it will have to be possible for each callback, when called at runtime, to determine from its arguments which PL/Java FDW it is being called for, so the right Java code can be invoked.
• It might be good, as a start, to run down the whole list of callbacks and make sure each has a viable way to do that (from which argument, and how, will the target FDW be identified?).
• If PostgreSQL makes certain decisions based on whether certain callback addresses are or aren't present in the struct returned by the handler ... and as the handler is passed no arguments, it might be necessary that all PL/Java FDWs claim the same capabilities (in terms of which callbacks are present). Or, one might identify a small finite set of FDW types differing in their capabilities, with PL/Java supplying an appropriately-named handler function for each, and the right handler function would have to be specified in the CREATE FOREIGN DATA WRAPPER command.

[beargiles]

I should have something soon.

I don't know what - but as several people have pointed out to me recently "some progress is better than no progress" (when seen from the outside) so even a dummy FDW that does nothing but provide some canned values would be a big step forward since it's something other people could build on. If nothing else it gets the idea past the initial hurdle of simply having something to modify vs. creating something from scratch in a complex environment.

The "minimal useful functionality" is probably the ability to replace a stored procedure that returns a cursor. Static (or null) values for things like cost, estimated record size, etc.

However this could result in a game changer since a FDW is tied to the server, not session or query, so it will have a single long-running JVM. I don't know if there's still a separate JVM per session (or even per query?) but having a long-lived JVM means you can start doing things like internal caching or speculative execution.

As for the callbacks - Holy Ramen Noodles you're right about that - but if you look the dummy implementations or even the PostgreSQL FileFDW implementation you'll see that the FDW only needs to implement around 8 functions for read-only access, and it looks like that will translate to only a few java methods. Basically an implementation of 'scan' and a way to get some metadata. I can look at your existing code to see how you load the jar, call a specific class/method, and create the resulting Tuple.

Re speculative execution. Some of the existing FDW wrap REST calls to external servers. In many cases you don't need to provide any information in the request and it's okay if the data is a little outdated. Think servers that provide routine weather information, tv schedules, etc.

In that case you can dramatically improve performance by performing speculative execution and caching the results. E.g., the FDW might specify that the cache should be updated every 10 minutes -- so the FDW sets up a loop so it makes the REST call and caches the results every 10 minutes. That cache is used when the user queries the FDW tables - they'll get an immediate response instead of waiting for a REST call to complete.

You could certainly write a stored procedure that makes the REST call and updates a PostgreSQL table, and a second stored procedure that reads from that table. (You would do this instead of reading from the table directly for encapsulation, logging access, etc.) But I can't think fo an easy way to perform the periodic updates short of setting up a cron task.

[jcflack]

However this could result in a game changer since a FDW is tied to the server, not session or query, so it will have a single long-running JVM. I don't know if there's still a separate JVM per session (or even per query?)

PL/Java itself is a JVM per session (well, per session where PL/Java is used).

I was not picturing a FDW behaving inherently differently. In what process do you picture such a JVM running, and what would be responsible for starting/stopping that process and the JVM within it?

[beargiles]

This is one reason for the steps I mentioned - it provides a way to explore that question before committing to a full implementation.

Loading and unloading the FDW's JVM is easy - there are _PG_init(void) and _PG_fini(void) hooks. There are several options on finding the JVM binary:

• Reuse the existing per-session implementation, if possible.
• Ideally JAVA_HOME will be available.
• On Linux check whether /etc/alternatives/java is defined. (E.g., on my system it's a symlink to /usr/lib/jvm/java-11-openjdk-amd64/bin/java,
• On Linux check /usr/lib/jvm on Linux systems. That's where java is usually installed (at least on Debian derivations) and there are symlinks using simple names like openjdk-11. The _PG_init(void) could check a short list of compatible JVMs. (E.g., on my system I could use openjdk-11 but probably wouldn't use openjdk-18 due to the changes in the security model.)

So I consider that a solved problem. It needs to implemented but there are several clear paths forward.

However there are many other questions that can best be resolved by testing a minimal working implementation. Prior research is important, of course, but that assumes the people who wrote the documentation anticipated this use case.

Three obvious questions

• is there a way for the FDW (given what it has) to obtain the contents of the session when the methods are called? This would allow the FDW to use the session JVM instead of a local JVM.

• if not - what will be required to pass data directly between the FDW JVM and the per-session JVM if desired?

• long-term and definitely 'opt-in' - is there a way for the FDW to replace the per-session JVM? This would require care in order to maintain isolation, e.g., the FDW would use a child classloader to load the jar + dependencies.

The second question is because I can see scenarios where a per-session JVM might want to use a persistent JVM for some tasks. Caching wouldn't be a good idea - it would break on a clustered database.

A better example would be access to a remote system that requires high security. (Think Kerberos, OAuth2, mutual TLS authentication, etc.) You could implement Kerberos and mutual TLS in the per-session JVM - although a careful security review might be concerned about access to the required credentials. However OAuth2 requires a HTTP/S callback to get the initial token and to renew it and that introduces uncertainty into the per-session JVM. E.g., how long should it wait for a response?

In this case I think a hybrid approach where the FDW is responsible for obtaining and (pro-actively) renewing the credentials and the per-session JVM retrieves it (e.g., via a standard SQL query) would make much more sense.

That said... it's not a big leap from "the per-session JVM makes the network call using credentials" to "the per-session JVM directly passes the information to the FDW and it makes the network call. The credentials are never exposed."

You could handle this using Tuples - the per-session JVM calls "INSERT .... RETURNING ..." so it's already an option once you support a read/write FDW. But that exposes the contents to any auditing tools with access to the actual data flow and I could see the security folks insisting on a direct call if possible.

(Hmm.... vague memories of the Java 1.0 java.rpc classes.... That might be the way to provide direct access despite using separate JVMs.)

[jcflack]

Loading and unloading the FDW's JVM is easy - there are _PG_init(void) and _PG_fini(void) hooks. There are several options on finding the JVM binary:

Sure. In what OS process, though, do you envision this happening?

PL/Java does it in the backend process serving your session.

[beargiles]

After that essay... I should be clear that my current focus is much more limited. Think "FileFDW" but with two extensions:

• it can perform arbitrary manipulation of the data before providing the results in the expected manner.

• it can use java to perform that manipulation.

The FDW implementation will be a skeleton with a single required option - the location of the jar file. (Ideally something already uploaded via sqlj, but initially it will be an external jar.

The jar will need to implement one or more interfaces - TBD.

The FDW skeleton can either use the standard METADATA-INF entry to locate the implementation, or it could scan the jar for all classes that implement it.

That's enough for my immediate needs, and I suspect enough for other people.

Everything else is "hmm, that could be useful since I've encountered a similar task at work" but definitely far beyond the scope of what I'm looking at at them moment. However these are important questions to ask in order to avoid implementations that fix the immediate problem but can't be extended.

[beargiles]

The more I think about it the more I'm convinced that there's some way to get the session at the appropriate time, e.g., when the scan is initialized. It's just too useful to not provide.

In the meanwhile, or if we want to stick with a single JVM, the _PG_init() function can make a fork-exec call. The parent process writes the PID to the usual location, with some mechanism that supports multiple instances. (Future task....). This is a good idea even if the JVM launcher performs its own fork-exec since it gives us a place to hang additional resources that can be controlled via IPC.

_PG_fini() retrieves the PID and calls kill() so the JVM can perform a clean shutdown.

[jcflack]

The more I think about it the more I'm convinced that there's some way to get the session at the appropriate time, e.g., when the scan is initialized.

I'm becoming a little concerned: there's enough vagueness in what you've written there that I am not sure what your mental model is of how PostgreSQL itself works.

In the meanwhile, or if we want to stick with a single JVM, the _PG_init() function can make a fork-exec call.

PL/Java starts up from _PG_init() in the backend process serving your session. So that's already a JVM per session (that uses PL/Java). Doing a fork there to create another process and another JVM in it would lead to two processes and two JVMs per session, which I don't think anyone is pining for, and I don't think would be at all necessary for the example uses you've indicated.

I don't think there's anything about a `FileFDW-with-some-Java-manipulations that can't be done happily within the same JVM that PL/Java is already going to start for your session, and I would urge focusing on a solution that will do that, as doing anything else will be a significant complexity jump.

The jar will need to implement one or more interfaces - TBD.

I would also suggest front-loading the effort to at least sketch out those interfaces. They're likely to need some iteration, and some difficulties may be identified early that way.

a single required option - the location of the jar file. (Ideally something already uploaded via sqlj, but initially it will be an external jar.

Because PL/Java already supplies facilities for loading jars, defining class paths, and associating those with PostgreSQL schemas, I would suggest instead that the options name a PostgreSQL schema (thereby, its class path) and the name of a class that implements the required interface.

That way, you can put your FDW implementation all in one jar, or have it and supporting libraries in separate jars, or include it in the jars of your other PL/Java functionality, and you don't have to reinvent any wheels that PL/Java already has.

[jcflack]

... also, while a schema and class name would be required in all cases, it will be important for additional options to be accepted. The Java interface needs to include a validation method to make sure any additional options and values supplied make sense to that FDW.

[beargiles]

Two things finally occurred to me at the same time.

First, there's no reason the first FDW needs to be able to interact with java. We can start with something simple and entirely written in C just to have a working example so we have a baseline. Once we have a good handle on it we can then start incorporating JNI calls.

Second, I've been thinking about a 'bytecode' UDT for a while. Long story, no need to go into it here.

Candiate FDW

This suggests a candidate FDW: jar_fdw.

It should be simple to implement:

1. Copy the existing file_fdw
2. Add zip file support
3. Expose the information required by the ClassLoader

From the javadoc we only require two methods:

     class NetworkClassLoader extends ClassLoader {
         String host;
         int port;

         public Class findClass(String name) {
             byte[] b = loadClassData(name);
             return defineClass(name, b, 0, b.length);
         }

         private byte[] loadClassData(String name) {
             // load the class data from the connection
              . . .
         }
     }

The latter is trivla - it's a select.

Implementing a SecureClassLoader only requires adding a CodeSource object. This contains the URL (required) and an optional array of either CodeSIgner or Certificate. We can and should use the URL of the jar file.

Implementation

There are three candidate implementations of the zip functionality.

• develop our own using zlib.
  • eliminates the need for additional dependencies
  • more secure since we have full control of the code
  • easy to use standard TOC for 'push down' searches
  • (or create our own if the latter is missing)
  • I have experience implementing the zlp archive in C
• use the libzip library (read and write)
• use the libzzip library (read-only)

We only need 'read only' access - that means there's no benefit for using the libzip library unless we know it is more secure.

Note - limited initial implementation

Note: the following topics are mostly ideas for future enhancements. The initial work needs to be focused on POC and may include temporary measures like using a third-party zip implementation even if the ultimate goal is to write our own.

Classloader concerns

We could create a separate FDW for each jar but it makes a lot more sense for the FDW to allow the user to provide multiple files since the only thing that matters is the classpath. Each row can contain a short foreign key into a jar file table.

"Backwards compatibility"

A FDW can access the host database. This means the FDW can transparently access either local files or the existing tables.

Usage

Using a FDW requires two steps.

You need to perform three steps before you can access a FDW:

• define the SERVER (you can have multiple instances)
• define the USER
• define the FOREIGN TABLE

All three steps allow you to provide configuration options. For now we can restrict the list of jars to the server but keep the door open for adding it to other levels for more sensitive jars. Of course all of the standard database PERMISSIONS still apply - but those permissions can't reach the ClassLoader.

Maven

This is much more advanced - but a natural place to look at integrating java later. It's also a possible solution when running in a hosted environment where we have limited to no access to the local filesystem a priori. (We should still be able to use a temp directory for caching, etc.)

For java devs the maven coordinates make much more sense than the location of the jar file. It's also enough information to find the jar file if we know the location of the local repo and it follows the standard layout.

It's also a natural component of the CodeSource UDT, although at the cost of being an unsupported schema.

The C-based implementation would be limited to local files or at most explicit URLs to a download site. However a java-based implementation can use the standard maven archive classes to find and download the jars from anywhere.

[beargiles]

A quick update....

I feel a bit like that Bugs Bunny cartoon set in opposing forts where Yosemite Sam keeps opening a door to find another door behind hit, while Bugs is nailing new doors on the end of the stack as fast as YS is opening them.

For the FDW I started from the PostgreSQL documentation, then realized I should reduce the problem to one that's entirely in C, reviewed some existing classes before discovering a 'blackhole_fdw' implementation that provides a working skeleton. It doesn't do anything but you can build it and install it. You just get no records with SELECT and INSERT, UPDATE, and DELETE have no effect. But it's working blank slate....

... however it hasn't been updated since PostgreSQL 10 or so (so it's missing a number of methods that (probably) don't need, and

... more importantly it has a null implementation. That doesn't mean it doesn't initialize and return structures, just that there's nothing but nulls and integers. Not even named fields. It's not hard to look at the functions called to add this ourselves but it takes a bit of time.

What I had hoped for was a skeleton implementation that was replaced the actual queries with placeholders (e.g., something accessing an internal cache) while fully implementing the glue. We would still need to implement the actual access to an external resource, provide the proper schema, etc.

At the moment I've forked the project and am adding this myself. I'll probably check for other skeleton implementations in a few days... but it depends on how quickly I make progress.

Once this is done it will be easy to add the jarfile code - one of the libraries I mentioned above uses memory-mapped files and that should be fast enough. (I'll document a few ideas if we need to improve the performance.)

This is all a bit ironic since this effort started by revisiting a separate project that would require several FDWs (in C) and I thought pljava would be a simpler version of those FDWs since a read-only implementation would still be good enough to get started. Now I have to open another door, one with no guarantees that there isn't yet another door behind it.