postgres - BTP hosted CAP application that uses an on-premise PostgreSQL database

[federicobelotti]

Hello,
the scenario we are trying to cover involves a CAP application hosted on the BTP in a Cloud Foundry environment that uses an on-premise PostgreSQL database that could be reached through the Cloud Connector hosted inside the same network as the on-premise database:

We setup a virtual host inside the Cloud Connector and a destination on the BTP space that points to that virtual host. However, no matter how we set up the config file for the database inside the CAP application, the database is not reachable (all I have is a timeout error).
I checked that either the virtual host or the destination where called when trying to read data, but I didn't find anything inside logs, so my guess is that the @cap-js/postgres plugin just searches for a publicly exposed host (or inside the same network) and I couldn't find a way to address the connection to the destination we created since the plugin doesn't take in account any destination parameter (or similar).

Everything I could find was related to a psql database together with the use of an hyperscaler, which is not possible in this situation (in fact, inside the MTA deploy configuration of the application, we don't have any deploy or other related services).

Is it possible to achieve what we need with the official postgres plugin?

Thanks

[federicobelotti]

I did a bit of research and I found out about this: https://github.com/piejanssens/sap-cf-socks
It is a package made for the old postgres library but that never got pulled in the official repo and I couldn't find any trace of it inside @cap-js/postgres.
It also seems that a connectivity service instance on the BTP is required for this to work (it is not a problem to set it up and bind it to the application), but it's not very clear how it should work (how does that instance know where to find the on-premise database?).

This kind of scenario seems a bit foggy and everything I find doesn't cover our needs.
Thanks in advance for any clarifications.

[patricebender]

Hi @FedericoBelotti-Reg,
thanks for opening the issue. Your setup looks quite complex and goes beyond the scope of this package.
Currently, we - from the CAP Team - have a lot on our plate and won't be able to look into each individual case.

We leave this open, maybe someone from the community has made similar experiences and can help you out.

[federicobelotti]

Hi @patricebender ,
I managed to accomplish what we needed to do and I'd like to share our solution to the problem.
I took advantage of a few of repos and blog posts I stubled upon regarding proxies, socks5 and BTP Connectivity Service.
The solution seems to be the use of a socket that acts as a proxy and that redirects the database connection to the Cloud Connector through the destination service which in turn reaches the on-premise system.
The details of the implementation are not relevant for now since everyone could set up its own proxy application and host it on the BTP and don't concern the Postgres connector itself.
What could be useful instead is to be able to indicate the destination in the database configuration so that the pg client knows that it should instantiate a socket connection instead of connecting through hostname and port, for example:

{
    "requires":{
       "db":{
         "credentials":{
            "host":"127.0.0.1", // This will be ignored if destination parameter has been provided
            "port":5432, // This will be ignored if destination parameter has been provided
            "user":"postgres",
            "password":"password",
            "database":"database-name",
            "destination": "destination-name-on-the-btp"
         },
          "dialect": "postgres",
          "impl": "@cap-js/postgres"
       }
    }
 }

Then, inside the @cap-js/postgres package:

// . . .
const dbc = new Client(...credentials, stream: new StreamBuilder(credentials.destination))
await dbc.connect()
// . . .

This is only a suggestion and if the destination configuration could be integrated in the @cap-js/postgres package I will proceed with a pull request that shows the implementation of the proxy (this has been done in a standalone npm package that is hosted on our private repository, for now) as well as the actual changes to the connector when the destination parameter is provided.

I'd like to know that you think and if this could be a useful contribution to the package, because for now we had to extend the PostgresService class in our CAP application and override the getFactory method to achieve an on-premise database connection and it would be great to have this feature out-of-the-box.

Thanks!
Federico

[patricebender]

Hi @federicobelotti,

great that you found a solution which works for you.
I will take this with me to our next steering sync and get back to you.

Thanks
Patrice

[BobdenOs]

Hi @federicobelotti,

I had a quick look at sap-cf-socks and it seems to integrate with the connectivity service over basic auth. My main concern would be that it does not have mTLS implemented. Which could mean that the package will stop working once basic auth support is dropped.

It might be possible to achieve the same functionality with the @sap-cloud-sdk package. Which has official SAP support and seems to also provide cloud-connector support (docs).

[YoshiakiToma]

Hi @federicobelotti

Is it possible to talk with you about this?

I tried to communicate a CAP with Postgree in AWS, but I didn't to resolve.

[gregorwolf]

Hi @YoshiakiToma,

if you can reach the AWS hosted PostgreSQL DB with tools like DBeaver you should also be able to connect to it from CAP. This topic is about tunneling requests from SAP BTP to a PostgreSQL hosted on premise.

[YoshiakiToma]

Yes , I know @gregorwolf

My AWS is closed to connect from external, so if I want to connect any internal API from AWS, I use the SAP Cloud Connector to connect between SAP BTP and AWS.

This is possible because the cloud connector is installed in AWS Environment.

[gregorwolf]

Mabe the SAP Private Link Service could help you until the SOCKS5 proxy issue is solved.

[federicobelotti]

Hi @YoshiakiToma,
@gregorwolf is right, this is a different scenario that doesn't involve cloud services like AWS.

@BobdenOs I didn't have much time to work again on the repo that I used to connect to an on-premise PostgreSQL database, but I plan to publish it as soon as I can so you can look at what I did.