RDS supports `postgres_fdw` on pretty much all versions of vanilla PG and Aurora PG. This should be sufficient to implement what you described.
Though if you wanted to go one step further you could use `postgres_fdw` to connect to a bunch of stateless PG boxes running OSS PG and have those load non-supported FDWs in order to support all sorts of backends like `mysql_fdw` and friends. Adds a proxy hop but makes it possible to do all sorts of very cool things. Hit me up if you want to talk more PG/Zanzibar things.
Though if you wanted to go one step further you could use `postgres_fdw` to connect to a bunch of stateless PG boxes running OSS PG and have those load non-supported FDWs in order to support all sorts of backends like `mysql_fdw` and friends. Adds a proxy hop but makes it possible to do all sorts of very cool things. Hit me up if you want to talk more PG/Zanzibar things.
PS: I wrote this to get an idea of Zanzibar but I haven't used it in anger yet: https://github.com/josephglanville/zanzibar-pg