In some cases, one want to feed or populate some PostgreSQL database tables using data fetched from the web, e.g. through a RESTful API (serving JSON features).
I've recently discovered foreign data wrappers and I'm not yet 100% used to them, but they seem to be a very interesting option to replace some custom Python scripts that retrieve data from the web, process it and use a Python data wrapper (i.e. psycopg2, or others) to populate some of my PostgreSQL database tables with that data.
After some research, I've successfully used the ogr_fdw (see also here) for fetching data from an OGC API features endpoint as well as the http extension for retrieving JSON data from a standard public API. Both those APIs are public, so they don't need any type of authentication.
I'm now naturally wondering if it's possible to build a view or populate a PostgreSQL table using a foreign data wrapper which retrieves data from a private RESTful API, i.e. which has an authentication procedure?
If yes, what kind of extension should I use and how? I couldn't find a tutorial or any detailed information at the moment.
The authentications I want to succeed with are the web based ones, i.e.: HTTP Basic Auth, API keys or OAuth2.
