So there is the openSAP course “Cloud-Native Development with SAP Cloud Platform” (iiz a good course yeh):
https://open.sap.com/courses/cp5
... now in the Week 3 slides (slide 2), there is an optional exercise suggested: follow the tutorial from cloudfoundry.org on how to access your backing-service (a database) on Cloud Foundry, using this link:
https://docs.cloudfoundry.org/devguide/deploy-apps/ssh-services.html
So, I followed the instructions, they were quite clear... but… there’s always a catch... I got stuck for a little bit at the last step, where (in their tutorial example) you would use the mysql command line tool with its options, to connect to the backing-service via the SSH-tunnel created in the previous step with “cf ssh”-tool.
So, in case anyone else gets stuck, here is the solution, in case (as in this openSAP course) we have a Postgres database as a backing-service on Cloud Foundry (and not a mysql database).
Let’s say, your main app is named “bulletinboard-ads”, and your backing-service is named “postgres-bulletinboard-ads” (since those were the names suggested in the course exercises). We assume that app and database are running nicely (“cf push” worked and the manifest.yml bound the app to the backing-service correctly). Like for example you just completed Exercise 10 from the openSAP course:
https://github.com/SAP/cloud-bulletinboard-ads/blob/Documentation/ConnectDatabase/Exercise_10_Deploy...
Now let’s also assume you have logged in to your Cloud Foundry account using the cf CLI. I also assume you are using a bash-terminal, because that is what I prefer 🙂
So, you start off by entering some commands to the terminal:
Check whether ssh-enabled state is “on”:
$ cf ssh-enabled bulletinboard-ads
If needed, you enable ssh for your main app:
$ cf enable-ssh bulletinboard-ads
(If your app was running but not enabled till when you executed the above command, you will likely need to restart the app, for this ssh-enablement to be picked up (according to advice in the comments section, thanks Ivan)).
Test that ssh-ing into your main app works ok, by logging into the “default” instance:
$ cf ssh bulletinboard-ads
If this cf ssh login succeeds, your prompt should change to e.g.:
vcap@22c5f36e-f49e-487c-1234-5678:~$
...where the hostname part after the @-sign is a long hexadecimal string.
You can logout from your app container using this command:
$ exit
Back in your own machine’s shell, you need to create the service-key for your backing-service, like so – I call mine “bee-key-pers”, you can always choose your own name for the key:
$ cf create-service-key postgres-bulletinboard-ads bee-key-pers
Now you can check and maybe copy-paste onto gedit or notepad++ or wherever, the details of your service-key:
$ cf service-key postgres-bulletinboard-ads bee-key-pers
It will look something like this:
{
"dbname": "Z8PoB_8i12345678",
"hostname": "10.11.12.13",
"password": "oKHPbQoL12345678",
"port": "39890",
"ports": {
"5432/tcp": "39890"
},
"uri": "postgres://n3zEEVv512345678:oKHPbQoL12345678@10.11.12.13:39890/Z8PoB_8i12345678",
"username": "n3zEEVv512345678"
}
[Unlikely that the string “12345678” will appear quite so often in a service-key, but you get the idea].
Now we are coming to one of those “magic spells” kind of commands… let’s say, that on your machine (the computer from which you are logging in to Cloud Foundry), you decide to use the local machine’s port number 60606, whenever you want to login to the postgres backing-service on Cloud Foundry… then, your cf CLI incantation would be:
$ cf ssh -L 60606:10.11.12.13:39890 bulletinboard-ads
It’s worth just checking through this command, to see what it is doing… the “cf ssh” utility, is saying via the “-L” flag, that we want to forward the local (as in your local machine’s) port 60606 in such a way, that when a call comes in for port 60606, then we “cf ssh into/via the main app (bulletinboard-ads)”, where an ssh-tunnel has been just now defined, i.e. the local port 60606 is forwarded to the host 10.11.12.13 on the Cloud Foundry space’s network – and then the port of that host has to be the one specified in the postgres backing-service’s service-key, so: port 39890.
At least, that is how I have explained this command to myself, if anyone has a better explanation please enlighten me also 🙂
Ok, so now you have set up the ssh-tunnel from your local port to the postgres database via the main app. You will have noticed that on this terminal, you also have a shell open in your main app, where you have just ssh:ed in as user vcap. Don’t exit now from this shell, because when you exit from the ssh-session, you also close the tunnel that you created… whereas you still want to login to postgres via this tunnel: so, you keep the tunnel open for now. Leave that terminal open, and open a new terminal.
In the new terminal, the first task is to check that the port 60606 is listening – this command should work on Linux:
$ netstat –listen | grep 60606
tcp 0 0 localhost:60606 *:* LISTEN
[macOS (High Sierra) equivalent command is:
Windows equivalent:
> netstat -na | find "60606"
… (tested that this works on a Windows 10 machine)].
Ok, so what is the next step?
Well, first you need to make sure you have the “postgres command line tool”, called “psql”, on your local machine. You might need to google the easiest installation method (depending on what OS and version of OS you are using). For example, on Ubuntu 16.04 LTS, this command was enough:
$ sudo apt install postgresql postgresql-contrib
After install, you can check the tool is working with e.g.:
$ psql --version
psql (PostgreSQL) 9.5.12
$
So, now comes the second “magic spells”-command, worked out by trial and error (and error, and error, and error …. until error == gone) … you will want to have your database’s user-password handy for the password challenge that follows:
$ psql -U n3zEEVv512345678 -d Z8PoB_8i12345678 -h localhost -p 60606
Password for user n3zEEVv512345678:
psql (9.5.12, server 9.4.15)
Type "help" for help.
Z8PoB_8i12345678=>
So at this point, you are logged in to your postgres database backing-service on Cloud Foundry! Let’s just break down what that command did: “-U” is the flag for the DB-user; “-d” is the flag for the name of the database; “-h” is the flag for the host on which the port exists, and in this case you need to specify “localhost”; “-p” is for the port number.
Now, you can execute a few psql commands, just to convince yourself that this is all real and you are mucking around in the Cloud Foundry via SSH tunnel:
Z8PoB_8i12345678=> \dt
Z8PoB_8i12345678=> SELECT * FROM advertisements;
… see, there’s an entry (id=7) that I had previously manually adverbially psql:d into the database table, in honour of and on the occasion of this ssl-tunneling game working out after several near-misses… And now we can add our own new record to the table, just because, well, because we can:
Z8PoB_8i12345678=> INSERT INTO advertisements (id, createdat, mytitle, version) VALUES (57, ‘2018-04-27 18:50:22.968’, ‘also via ssh’, 1);
We could check using the previous SELECT * that our new record is there, or we can use Postman REST client as a more external smoketest procedure, sure the record was added ok:
When you are done troughing around in the backing service, exit the psql session using command:
Z8PoB_8i12345678=> \q
… that ends your Cloud Foundry psql session. And then you can also exit from your ssh session in the main app, since you don’t need the ssh-tunnel - you can always recreate it using the same magic spell as before, if you get an "oops, forgot to check that"-feeling, anyway it’s better to close connections once they are not needed.
Cheers n have a nice day.
--EOF
https://open.sap.com/courses/cp5
... now in the Week 3 slides (slide 2), there is an optional exercise suggested: follow the tutorial from cloudfoundry.org on how to access your backing-service (a database) on Cloud Foundry, using this link:
https://docs.cloudfoundry.org/devguide/deploy-apps/ssh-services.html
So, I followed the instructions, they were quite clear... but… there’s always a catch... I got stuck for a little bit at the last step, where (in their tutorial example) you would use the mysql command line tool with its options, to connect to the backing-service via the SSH-tunnel created in the previous step with “cf ssh”-tool.
So, in case anyone else gets stuck, here is the solution, in case (as in this openSAP course) we have a Postgres database as a backing-service on Cloud Foundry (and not a mysql database).
Let’s say, your main app is named “bulletinboard-ads”, and your backing-service is named “postgres-bulletinboard-ads” (since those were the names suggested in the course exercises). We assume that app and database are running nicely (“cf push” worked and the manifest.yml bound the app to the backing-service correctly). Like for example you just completed Exercise 10 from the openSAP course:
https://github.com/SAP/cloud-bulletinboard-ads/blob/Documentation/ConnectDatabase/Exercise_10_Deploy...
Now let’s also assume you have logged in to your Cloud Foundry account using the cf CLI. I also assume you are using a bash-terminal, because that is what I prefer 🙂
So, you start off by entering some commands to the terminal:
Check whether ssh-enabled state is “on”:
$ cf ssh-enabled bulletinboard-ads
If needed, you enable ssh for your main app:
$ cf enable-ssh bulletinboard-ads
(If your app was running but not enabled till when you executed the above command, you will likely need to restart the app, for this ssh-enablement to be picked up (according to advice in the comments section, thanks Ivan)).
Test that ssh-ing into your main app works ok, by logging into the “default” instance:
$ cf ssh bulletinboard-ads
If this cf ssh login succeeds, your prompt should change to e.g.:
vcap@22c5f36e-f49e-487c-1234-5678:~$
...where the hostname part after the @-sign is a long hexadecimal string.
You can logout from your app container using this command:
$ exit
Back in your own machine’s shell, you need to create the service-key for your backing-service, like so – I call mine “bee-key-pers”, you can always choose your own name for the key:
$ cf create-service-key postgres-bulletinboard-ads bee-key-pers
Now you can check and maybe copy-paste onto gedit or notepad++ or wherever, the details of your service-key:
$ cf service-key postgres-bulletinboard-ads bee-key-pers
It will look something like this:
{
"dbname": "Z8PoB_8i12345678",
"hostname": "10.11.12.13",
"password": "oKHPbQoL12345678",
"port": "39890",
"ports": {
"5432/tcp": "39890"
},
"uri": "postgres://n3zEEVv512345678:oKHPbQoL12345678@10.11.12.13:39890/Z8PoB_8i12345678",
"username": "n3zEEVv512345678"
}
[Unlikely that the string “12345678” will appear quite so often in a service-key, but you get the idea].
Now we are coming to one of those “magic spells” kind of commands… let’s say, that on your machine (the computer from which you are logging in to Cloud Foundry), you decide to use the local machine’s port number 60606, whenever you want to login to the postgres backing-service on Cloud Foundry… then, your cf CLI incantation would be:
$ cf ssh -L 60606:10.11.12.13:39890 bulletinboard-ads
It’s worth just checking through this command, to see what it is doing… the “cf ssh” utility, is saying via the “-L” flag, that we want to forward the local (as in your local machine’s) port 60606 in such a way, that when a call comes in for port 60606, then we “cf ssh into/via the main app (bulletinboard-ads)”, where an ssh-tunnel has been just now defined, i.e. the local port 60606 is forwarded to the host 10.11.12.13 on the Cloud Foundry space’s network – and then the port of that host has to be the one specified in the postgres backing-service’s service-key, so: port 39890.
At least, that is how I have explained this command to myself, if anyone has a better explanation please enlighten me also 🙂
Ok, so now you have set up the ssh-tunnel from your local port to the postgres database via the main app. You will have noticed that on this terminal, you also have a shell open in your main app, where you have just ssh:ed in as user vcap. Don’t exit now from this shell, because when you exit from the ssh-session, you also close the tunnel that you created… whereas you still want to login to postgres via this tunnel: so, you keep the tunnel open for now. Leave that terminal open, and open a new terminal.
In the new terminal, the first task is to check that the port 60606 is listening – this command should work on Linux:
$ netstat –listen | grep 60606
tcp 0 0 localhost:60606 *:* LISTEN
[macOS (High Sierra) equivalent command is:
$ lsof -nP -i4TCP:60606
Windows equivalent:
> netstat -na | find "60606"
… (tested that this works on a Windows 10 machine)].
Ok, so what is the next step?
Well, first you need to make sure you have the “postgres command line tool”, called “psql”, on your local machine. You might need to google the easiest installation method (depending on what OS and version of OS you are using). For example, on Ubuntu 16.04 LTS, this command was enough:
$ sudo apt install postgresql postgresql-contrib
After install, you can check the tool is working with e.g.:
$ psql --version
psql (PostgreSQL) 9.5.12
$
So, now comes the second “magic spells”-command, worked out by trial and error (and error, and error, and error …. until error == gone) … you will want to have your database’s user-password handy for the password challenge that follows:
$ psql -U n3zEEVv512345678 -d Z8PoB_8i12345678 -h localhost -p 60606
Password for user n3zEEVv512345678:
psql (9.5.12, server 9.4.15)
Type "help" for help.
Z8PoB_8i12345678=>
So at this point, you are logged in to your postgres database backing-service on Cloud Foundry! Let’s just break down what that command did: “-U” is the flag for the DB-user; “-d” is the flag for the name of the database; “-h” is the flag for the host on which the port exists, and in this case you need to specify “localhost”; “-p” is for the port number.
Now, you can execute a few psql commands, just to convince yourself that this is all real and you are mucking around in the Cloud Foundry via SSH tunnel:
Z8PoB_8i12345678=> \dt
Z8PoB_8i12345678=> SELECT * FROM advertisements;
… see, there’s an entry (id=7) that I had previously manually adverbially psql:d into the database table, in honour of and on the occasion of this ssl-tunneling game working out after several near-misses… And now we can add our own new record to the table, just because, well, because we can:
Z8PoB_8i12345678=> INSERT INTO advertisements (id, createdat, mytitle, version) VALUES (57, ‘2018-04-27 18:50:22.968’, ‘also via ssh’, 1);
We could check using the previous SELECT * that our new record is there, or we can use Postman REST client as a more external smoketest procedure, sure the record was added ok:
When you are done troughing around in the backing service, exit the psql session using command:
Z8PoB_8i12345678=> \q
… that ends your Cloud Foundry psql session. And then you can also exit from your ssh session in the main app, since you don’t need the ssh-tunnel - you can always recreate it using the same magic spell as before, if you get an "oops, forgot to check that"-feeling, anyway it’s better to close connections once they are not needed.
Cheers n have a nice day.
--EOF
- SAP Managed Tags:
8 Comments
