Starting situation<\/h2>\n
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<\/a> .<\/p>\n 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.<\/p>\n 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<\/a> on SAP BTP<\/a>. This limitation required a solution to improve efficiency and functionality.<\/p>\n To ensure the security and accessibility of the database for developers and database specialists, our team decided to set up an SSH tunnel<\/strong> . 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.<\/p>\n 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.<\/p>\n If all requirements are met, only the name of the instance needs to be assigned. You should avoid unusual signs:<\/p>\n The data is required for the tunnel and the DB admin software<\/strong><\/p>\n From now on we need the command line<\/strong><\/p>\n or<\/p>\n A possible API endpoint is: https:\/\/api.cf.us10-001.hana.ondemand.com. The URL depends on your subaccount.<\/p>\n optionally change the app name(APP_NAME) from myapp to sshenable in the manifest.yml <\/p>\n In the root folder of the app, run (replace APP_NAME):<\/p>\n Share the app as an SSH endpoint and restart:<\/p>\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<\/p>\n Four values are required from the service key:<\/p>\n It is not necessary to take care of the SSH tab!<\/p>\n 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.<\/p>\n 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.<\/p>\n The HO2 as part of the InnovateSAP<\/strong> 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.<\/p>\n 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.<\/p>\n","protected":false},"excerpt":{"rendered":" Discover how HO2 enables remote access to Postgre’s database instances on SAP BTP, taking data management, monitoring, and performance optimization to the next level.<\/p>\n","protected":false},"author":1,"featured_media":6306,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"wpupg_custom_link":[],"wpupg_custom_link_behaviour":[],"wpupg_custom_link_nofollow":[],"wpupg_custom_image":[],"wpupg_custom_image_id":[],"footnotes":""},"categories":[511,497],"tags":[723,728,722,727,729,730,725,731,721,510,726,724],"class_list":{"0":"post-6315","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","7":"category-sap-btp-en","8":"category-sap-code-en","9":"tag-database-management","10":"tag-debugging-en","11":"tag-external-access","12":"tag-migration","13":"tag-monitoring-en","14":"tag-performance-optimization","15":"tag-pgadmin-en","16":"tag-postgres-en","17":"tag-postgresql-en","18":"tag-sap-btp-en","19":"tag-sql-workbench-j-en","20":"tag-ssh-tunnel-en"},"yoast_head":"\n![\"PostgreSQL](\"https:\/\/www.ho2-gmbh.de\/wp-content\/uploads\/2024\/03\/postgres-locked-on-btp-480x480.webp\")
<\/a><\/h2>\nApproach<\/h2>\n
Howto: SAP BTP PostgreSQL Database Connection to pgAdmin with SSH Tunnel<\/h2>\n
Preconditions:<\/h3>\n
\n
Steps for external access:<\/h3>\n
\n
1.Create PostgreSQL Database Instance on SAP BTP<\/h3>\n
![\"Create](\"https:\/\/www.ho2-gmbh.de\/wp-content\/uploads\/2024\/03\/postgreSQL-instanz-anlegen-640x364.webp\")
<\/a><\/p>\n2.Create a new service key for external access<\/h3>\n
![\"\"](\"https:\/\/www.ho2-gmbh.de\/wp-content\/uploads\/2024\/03\/createServiceKey-640x352.webp\")
<\/a><\/p>\n![\"\"](\"https:\/\/www.ho2-gmbh.de\/wp-content\/uploads\/2024\/03\/createServiceKey-credentials-640x441.webp\")
<\/a><\/p>\n3. To log in to the Cloudfoundry:<\/h3>\n
cf login --sso<\/code><\/pre>\ncf api API_ENDPOINT \ncf login<\/code><\/pre>\n4. To deploy the “SSH Enable” APP:<\/h3>\n
git clone https:\/\/github.com\/alimert801\/cf-sample-python-app.git<\/code><\/pre>\ncf push APP_NAME<\/code><\/pre>\ncf enable-ssh APP_NAME<\/code><\/pre>\ncf restage APP_NAME<\/code><\/pre>\n5. Bind the PostgreSQL database to the app<\/h3>\n
![\"binding](\"https:\/\/www.ho2-gmbh.de\/wp-content\/uploads\/2024\/03\/binding-der-DB-zur-APP-640x429.webp\")
<\/a><\/h3>\n6. To create the SSH tunnel:<\/h3>\n
cf ssh -L localhost:2641:REMOTEHOST:REMOTEPORT APP_NAME -N <\/code><\/pre>\n7. Configuring pgAdmin:<\/h3>\n
\n
![\"\"](\"https:\/\/www.ho2-gmbh.de\/wp-content\/uploads\/2024\/03\/pgAdmin-4-537x480.png\")
<\/a><\/p>\nArchitecture:<\/h3>\n
Results<\/h2>\n
![\"Developer](\"https:\/\/www.ho2-gmbh.de\/wp-content\/uploads\/2024\/03\/dev-access-to-postgreSQL-480x480.webp\")
<\/a><\/h2>\nClosing remarks<\/h2>\n