Skip to main content
SAP BTPSAP Code

External access to your PostgreSQL database on SAP BTP? HO2 shows how it’s done

By 2024-03-22April 9th, 2024No Comments
Inhalte Anzeigen

External Access to PostgreSQL Database Instances on SAP BTP with HO2

Are you ready to take your database management to the next level? As part of the InnovateSAP initiative, we want to help you effectively overcome the challenges of accessing PostgreSQL database instances on SAP BTP.

Starting situation

As a database specialist or software developer, it is quite common to have direct access to the databases. Even if this does not seem absolutely necessary, for example, in the context of SAP CAP application development .

However, access can be helpful or crucial in many situations and also in day-to-day operations. Think of areas such as data migration, debugging, monitoring, and performance optimization.

Access to PostgreSQL database instances was a significant hurdle here. By default, SAP does not provide an application to manage PostgreSQL instances. The ability to view tables, data, and other objects and, if necessary, modify them was limited compared to the options available with HANA-DB on SAP BTP. This limitation required a solution to improve efficiency and functionality.

PostgreSQL inaccessible on the BTP

Approach

To ensure the security and accessibility of the database for developers and database specialists, our team decided to set up an SSH tunnel . By deploying a small application, such a connection was successfully established. As a result, we were able to implement an efficient and secure solution for external database access.

Howto: SAP BTP PostgreSQL Database Connection to pgAdmin with SSH Tunnel

This guide provides you with an in-depth guide to establish a secure and direct connection between your SAP BTP hosted PostgreSQL database and the pgAdmin application on your local machine.

Preconditions:

  • SAP BTP Account
  • pgAdmin installed on your computer
  • Authorization and quota to create a PostgreSQL-DB instance, to create applications for deploys and bindings
  • Cloud Foundry CLI installed

Steps for external access:

  1. Create a PostgreSQL database instance on SAP BTP.
  2. Create a new service key for external access. The key data is required for the tunnel and the admin software
  3. Log in to Cloud Foundry using the command line.
  4. Deployment of the sshenable app.
  5. Bind the PostgreSQL database to the app.
  6. Open an SSH tunnel for local database access.
  7. Configuration of pgAdmin to access the SAP BTP PostgreSQL database.

1.Create PostgreSQL Database Instance on SAP BTP

If all requirements are met, only the name of the instance needs to be assigned. You should avoid unusual signs:

Create postgreSQL instance

2.Create a new service key for external access

The data is required for the tunnel and the DB admin software

3. To log in to the Cloudfoundry:

From now on we need the command line

cf login --sso

or

cf api API_ENDPOINT 
cf login

A possible API endpoint is: https://api.cf.us10-001.hana.ondemand.com. The URL depends on your subaccount.

4. To deploy the “SSH Enable” APP:

git clone https://github.com/alimert801/cf-sample-python-app.git

optionally change the app name(APP_NAME) from myapp to sshenable in the manifest.yml

In the root folder of the app, run (replace APP_NAME):

cf push APP_NAME

Share the app as an SSH endpoint and restart:

cf enable-ssh APP_NAME
cf restage APP_NAME

5. Bind the PostgreSQL database to the app

binding of the DB to the APP

6. To create the SSH tunnel:

cf ssh -L localhost:2641:REMOTEHOST:REMOTEPORT APP_NAME -N

In this command, the (arbitrarily chosen) local port 2641 is forwarded to the PostgreSQL database port of the SAP BTP, which allows pgAdmin to access your SAP BTP database as if it were local. REMOTEHOST and REMOTEPORT can be found in the service key created at the beginning as host and port

7. Configuring pgAdmin:

Four values are required from the service key:

  1. dbname
  2. Password
  3. port
  4. username

It is not necessary to take care of the SSH tab!

Architecture:

This architecture enables seamless communication and secure data access between your on-premises work environment and SAP BTP. By binding the database to your cloud application and using an SSH tunnel to route the ports, you create a secure and efficient data bridge directly in pgAdmin on your local machine.

Results

By implementing this solution, access to the PostgreSQL database instances is enabled. Tools such as pgAdmin and SQL Workbench/J can now be used to manage the database efficiently. This improvement has brought significant improvements in areas such as data migration, debugging, monitoring, and performance optimization.

Developer access to PostgreSQL

Closing remarks

The HO2 as part of the InnovateSAP initiative has once again demonstrated the results that can be achieved through technical expertise. With this enabled database accessibility, we are setting new standards in efficiency and productivity.

If you have any questions about the implementation or other services, do not hesitate to contact us. We are ready to tackle any challenge and seize every opportunity to innovate.

Tags: