Skip to content

Instantly share code, notes, and snippets.

@simo97

simo97/Connecting_postgress_RDS_from_local_via_bastion.md

Forked from kshailen/Connecting_postgress_RDS_from_local_via_bastion.md
Created July 23, 2021 01:05
Show Gist options
  • Save simo97/5e1b6e1bdb2f7e5358aeca75a456e7c5 to your computer and use it in GitHub Desktop.
Save simo97/5e1b6e1bdb2f7e5358aeca75a456e7c5 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. @kshailen kshailen revised this gist Jul 16, 2019. 1 changed file with 1 addition and 0 deletions.
    1 change: 1 addition & 0 deletions Connecting_postgress_RDS_from_local_via_bastion.md
    View file
    Original file line number Diff line number Diff line change
    @@ -62,6 +62,7 @@ Home screen will as below
    Click on server and the click on add servers:

    ![Add server](https://user-images.githubusercontent.com/6451436/61276043-cbf4cc00-a79e-11e9-94c1-bf9a67ec378b.png)

    You should put ```localhost``` in ```hostname/address``` ```local port(8886)``` in ```port``` and put username and password accordingly. After this click on save.


  2. @kshailen kshailen revised this gist Jul 16, 2019. 1 changed file with 1 addition and 0 deletions.
    1 change: 1 addition & 0 deletions Connecting_postgress_RDS_from_local_via_bastion.md
    View file
    Original file line number Diff line number Diff line change
    @@ -60,6 +60,7 @@ Home screen will as below
    ![pgAdmin home screen ](https://user-images.githubusercontent.com/6451436/61275856-57ba2880-a79e-11e9-8820-efbbc99fb389.png)

    Click on server and the click on add servers:

    ![Add server](https://user-images.githubusercontent.com/6451436/61276043-cbf4cc00-a79e-11e9-94c1-bf9a67ec378b.png)
    You should put ```localhost``` in ```hostname/address``` ```local port(8886)``` in ```port``` and put username and password accordingly. After this click on save.

  3. @kshailen kshailen revised this gist Jul 16, 2019. 1 changed file with 6 additions and 0 deletions.
    6 changes: 6 additions & 0 deletions Connecting_postgress_RDS_from_local_via_bastion.md
    View file
    Original file line number Diff line number Diff line change
    @@ -59,3 +59,9 @@ Steps are as follows:
    Home screen will as below
    ![pgAdmin home screen ](https://user-images.githubusercontent.com/6451436/61275856-57ba2880-a79e-11e9-8820-efbbc99fb389.png)

    Click on server and the click on add servers:
    ![Add server](https://user-images.githubusercontent.com/6451436/61276043-cbf4cc00-a79e-11e9-94c1-bf9a67ec378b.png)
    You should put ```localhost``` in ```hostname/address``` ```local port(8886)``` in ```port``` and put username and password accordingly. After this click on save.



  4. @kshailen kshailen revised this gist Jul 16, 2019. 1 changed file with 2 additions and 0 deletions.
    2 changes: 2 additions & 0 deletions Connecting_postgress_RDS_from_local_via_bastion.md
    View file
    Original file line number Diff line number Diff line change
    @@ -57,3 +57,5 @@ Steps are as follows:
    2. Make connection: Make sure while making connection you should use loaclhost as server address and port on which tunnel was made(in this case 8886)

    Home screen will as below
    ![pgAdmin home screen ](https://user-images.githubusercontent.com/6451436/61275856-57ba2880-a79e-11e9-8820-efbbc99fb389.png)

  5. @kshailen kshailen revised this gist Jul 16, 2019. 1 changed file with 4 additions and 4 deletions.
    8 changes: 4 additions & 4 deletions Connecting_postgress_RDS_from_local_via_bastion.md
    View file
    Original file line number Diff line number Diff line change
    @@ -6,7 +6,7 @@ How can you easily access and manage your secured data?
    ![basic_RDS_bastion_architecture](https://user-images.githubusercontent.com/6451436/61270199-7ca79f00-a790-11e9-9c05-9ad2bbbea517.jpg)

    There are two basic ways to acees it.
    1. Access postgres RDS from bastion host.
    1. Access postgres RDS from bastion host.
    There are following requirements for this.
    * postgre RDS instance should open 5432 for ingress from bastion/jump server
    * pgsql client should be installed on bastion host
    @@ -17,7 +17,7 @@ There are following requirements for this.
    ```
    To install pgsql client you should following this page [Install PostgreSQL Client](https://www.compose.com/articles/postgresql-tips-installing-the-postgresql-client/)

    2. You can also access postgress sql from your local (Mac).
    2. You can also access postgress sql from your local (Mac).
    For this you should create tunnel from you local to rds instance via bastion host.
    ```
    Suppose:
    @@ -53,7 +53,7 @@ $psql -h localhost -p 8886 -U <username>

    Using pgAdmin:
    Steps are as follows:
    1 Open pgAdmin
    2 Make connection: Make sure while making connection you should use loaclhost as server address and port on which tunnel was made(in this case 8886)
    1. Open pgAdmin
    2. Make connection: Make sure while making connection you should use loaclhost as server address and port on which tunnel was made(in this case 8886)

    Home screen will as below
  6. @kshailen kshailen revised this gist Jul 16, 2019. 1 changed file with 3 additions and 2 deletions.
    5 changes: 3 additions & 2 deletions Connecting_postgress_RDS_from_local_via_bastion.md
    View file
    Original file line number Diff line number Diff line change
    @@ -53,6 +53,7 @@ $psql -h localhost -p 8886 -U <username>

    Using pgAdmin:
    Steps are as follows:
    1.Open pgAdmin
    2. Make connection: Make sure while making connection you should use loaclhost as server address and port on which tunnel was made(in this case 8886)
    1 Open pgAdmin
    2 Make connection: Make sure while making connection you should use loaclhost as server address and port on which tunnel was made(in this case 8886)

    Home screen will as below
  7. @kshailen kshailen revised this gist Jul 16, 2019. 1 changed file with 0 additions and 1 deletion.
    1 change: 0 additions & 1 deletion Connecting_postgress_RDS_from_local_via_bastion.md
    View file
    Original file line number Diff line number Diff line change
    @@ -56,4 +56,3 @@ Steps are as follows:
    1.Open pgAdmin
    2. Make connection: Make sure while making connection you should use loaclhost as server address and port on which tunnel was made(in this case 8886)
    Home screen will as below
    ![pgAdmin home screen](https://www.google.com/url?sa=i&source=images&cd=&ved=2ahUKEwjRhv6J-bjjAhUVfH0KHalSANEQjRx6BAgBEAU&url=https%3A%2F%2Fwww.enterprisedb.com%2Fde%2Fdocs%2Fen%2F11.0%2FEPAS_11_Inst_Windows%2FEDB_Postgres_Advanced_Server_Installation_Guide_Windows.1.26.html&psig=AOvVaw0go2rlAWI3OxLKTen6wktW&ust=1563349531383395)
  8. @kshailen kshailen revised this gist Jul 16, 2019. 1 changed file with 14 additions and 2 deletions.
    16 changes: 14 additions & 2 deletions Connecting_postgress_RDS_from_local_via_bastion.md
    View file
    Original file line number Diff line number Diff line change
    @@ -35,8 +35,6 @@ NL — N will not open a session with the server. It will set up the tunnel.
    How to check if tunnel is established or not.
    ```bash
    netstat -ntaP tcp | grep -i LISTEN | grep portnumber
    or alternatively you can try telnet
    telnet localhost portnumber
    ```
    It will give output like below.
    ```
    @@ -45,3 +43,17 @@ tcp4 0 0 127.0.0.1.8886 *.* LISTEN
    tcp6 0 0 ::1.8886 *.* LISTEN 0 0
    ```
    Once tunnel is eatablished, You can either use pgsql command line client or you can [download pgAdmin](https://www.pgadmin.org/download/).

    Using pgsql command line client on mac:

    ```bash
    $psql -h localhost -p 8886 -U <username>
    ```

    Using pgAdmin:
    Steps are as follows:
    1.Open pgAdmin
    2. Make connection: Make sure while making connection you should use loaclhost as server address and port on which tunnel was made(in this case 8886)
    Home screen will as below
    ![pgAdmin home screen](https://www.google.com/url?sa=i&source=images&cd=&ved=2ahUKEwjRhv6J-bjjAhUVfH0KHalSANEQjRx6BAgBEAU&url=https%3A%2F%2Fwww.enterprisedb.com%2Fde%2Fdocs%2Fen%2F11.0%2FEPAS_11_Inst_Windows%2FEDB_Postgres_Advanced_Server_Installation_Guide_Windows.1.26.html&psig=AOvVaw0go2rlAWI3OxLKTen6wktW&ust=1563349531383395)
  9. @kshailen kshailen revised this gist Jul 16, 2019. 1 changed file with 15 additions and 1 deletion.
    16 changes: 15 additions & 1 deletion Connecting_postgress_RDS_from_local_via_bastion.md
    View file
    Original file line number Diff line number Diff line change
    @@ -30,4 +30,18 @@ ssh -i "bastion_key.pem" -NL 8886:postgress.cpypigm0kth7.us-east-1.rds.amazonaws
    ```
    Here 8886 is port for you loacl host. 5432 is port of postgress on rds.
    NL — N will not open a session with the server. It will set up the tunnel. L will set up the port forwarding.
    -v : Is optional. With this you will print the ssh log on your terminal.
    -v : Is optional. With this you will print the ssh log on your terminal.

    How to check if tunnel is established or not.
    ```bash
    netstat -ntaP tcp | grep -i LISTEN | grep portnumber
    or alternatively you can try telnet
    telnet localhost portnumber
    ```
    It will give output like below.
    ```
    shailendras-mbp:~ shaikuma$ netstat -ntaP tcp | grep -i LISTEN | grep 8886
    tcp4 0 0 127.0.0.1.8886 *.* LISTEN 0 0
    tcp6 0 0 ::1.8886 *.* LISTEN 0 0
    ```
  10. @kshailen kshailen revised this gist Jul 16, 2019. 1 changed file with 5 additions and 2 deletions.
    7 changes: 5 additions & 2 deletions Connecting_postgress_RDS_from_local_via_bastion.md
    View file
    Original file line number Diff line number Diff line change
    @@ -26,5 +26,8 @@ There are following requirements for this.
    ```
    Command to make tunnel:
    ```bash
    ssh -i "bastion_key.pem" -NL 4444:postgress.cpypigm0kth7.us-east-1.rds.amazonaws.com:5432 [email protected] -v
    ```
    ssh -i "bastion_key.pem" -NL 8886:postgress.cpypigm0kth7.us-east-1.rds.amazonaws.com:5432 [email protected] -v
    ```
    Here 8886 is port for you loacl host. 5432 is port of postgress on rds.
    NL — N will not open a session with the server. It will set up the tunnel. L will set up the port forwarding.
    -v : Is optional. With this you will print the ssh log on your terminal.
  11. @kshailen kshailen revised this gist Jul 16, 2019. 1 changed file with 12 additions and 0 deletions.
    12 changes: 12 additions & 0 deletions Connecting_postgress_RDS_from_local_via_bastion.md
    View file
    Original file line number Diff line number Diff line change
    @@ -16,3 +16,15 @@ There are following requirements for this.
    $psql -h <host> -p <port> -U <username> -W <password>
    ```
    To install pgsql client you should following this page [Install PostgreSQL Client](https://www.compose.com/articles/postgresql-tips-installing-the-postgresql-client/)

    2. You can also access postgress sql from your local (Mac).
    For this you should create tunnel from you local to rds instance via bastion host.
    ```
    Suppose:
    Bastion host IP is 132.5.10.11 and user is ec2-user and key_name is bastion_key.pem
    Your postgress RDS instance fqdn is postgress.cpypigm0kth7.us-east-1.rds.amazonaws.com
    ```
    Command to make tunnel:
    ```bash
    ssh -i "bastion_key.pem" -NL 4444:postgress.cpypigm0kth7.us-east-1.rds.amazonaws.com:5432 [email protected] -v
    ```
  12. @kshailen kshailen revised this gist Jul 16, 2019. 1 changed file with 3 additions and 2 deletions.
    5 changes: 3 additions & 2 deletions Connecting_postgress_RDS_from_local_via_bastion.md
    View file
    Original file line number Diff line number Diff line change
    @@ -8,8 +8,9 @@ How can you easily access and manage your secured data?
    There are two basic ways to acees it.
    1. Access postgres RDS from bastion host.
    There are following requirements for this.
    ```* postgre RDS instance should open 5432 for ingress from bastion/jump server
    ```* pgsql client should be installed on bastion host
    * postgre RDS instance should open 5432 for ingress from bastion/jump server
    * pgsql client should be installed on bastion host

    Run following command to connect:
    ```bash
    $psql -h <host> -p <port> -U <username> -W <password>
  13. @kshailen kshailen revised this gist Jul 16, 2019. 1 changed file with 2 additions and 2 deletions.
    4 changes: 2 additions & 2 deletions Connecting_postgress_RDS_from_local_via_bastion.md
    View file
    Original file line number Diff line number Diff line change
    @@ -8,8 +8,8 @@ How can you easily access and manage your secured data?
    There are two basic ways to acees it.
    1. Access postgres RDS from bastion host.
    There are following requirements for this.
    *. postgre RDS instance should open 5432 for ingress from bastion/jump server
    *. pgsql client should be installed on bastion host
    ```* postgre RDS instance should open 5432 for ingress from bastion/jump server
    ```* pgsql client should be installed on bastion host
    Run following command to connect:
    ```bash
    $psql -h <host> -p <port> -U <username> -W <password>
  14. @kshailen kshailen revised this gist Jul 16, 2019. 1 changed file with 2 additions and 2 deletions.
    4 changes: 2 additions & 2 deletions Connecting_postgress_RDS_from_local_via_bastion.md
    View file
    Original file line number Diff line number Diff line change
    @@ -8,8 +8,8 @@ How can you easily access and manage your secured data?
    There are two basic ways to acees it.
    1. Access postgres RDS from bastion host.
    There are following requirements for this.
    a). postgre RDS instance should open 5432 for ingress from bastion/jump server
    b). pgsql client should be installed on bastion host
    *. postgre RDS instance should open 5432 for ingress from bastion/jump server
    *. pgsql client should be installed on bastion host
    Run following command to connect:
    ```bash
    $psql -h <host> -p <port> -U <username> -W <password>
  15. @kshailen kshailen revised this gist Jul 16, 2019. 1 changed file with 15 additions and 2 deletions.
    17 changes: 15 additions & 2 deletions Connecting_postgress_RDS_from_local_via_bastion.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,4 +1,17 @@
    When it comes to databases and AWS VPC, best practice is to place your database in private subnet. By definition, private subnet in AWS is not reachable from the Internet because no Internet gateway is attached to private subnet. This is the way you protect your data.
    This kind of configuration is good for security but bad for data management. How can you easily access and manage your secured data? This question came up several time from my colleagues, so let’s write down the process step by step, for future use.
    This kind of configuration is good for security but bad for data management.

    ![basic_RDS_bastion_architecture](https://user-images.githubusercontent.com/6451436/61270199-7ca79f00-a790-11e9-9c05-9ad2bbbea517.jpg)
    How can you easily access and manage your secured data?

    ![basic_RDS_bastion_architecture](https://user-images.githubusercontent.com/6451436/61270199-7ca79f00-a790-11e9-9c05-9ad2bbbea517.jpg)

    There are two basic ways to acees it.
    1. Access postgres RDS from bastion host.
    There are following requirements for this.
    a). postgre RDS instance should open 5432 for ingress from bastion/jump server
    b). pgsql client should be installed on bastion host
    Run following command to connect:
    ```bash
    $psql -h <host> -p <port> -U <username> -W <password>
    ```
    To install pgsql client you should following this page [Install PostgreSQL Client](https://www.compose.com/articles/postgresql-tips-installing-the-postgresql-client/)
  16. @kshailen kshailen revised this gist Jul 16, 2019. 1 changed file with 3 additions and 1 deletion.
    4 changes: 3 additions & 1 deletion Connecting_postgress_RDS_from_local_via_bastion.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,2 +1,4 @@
    When it comes to databases and AWS VPC, best practice is to place your database in private subnet. By definition, private subnet in AWS is not reachable from the Internet because no Internet gateway is attached to private subnet. This is the way you protect your data.
    This kind of configuration is good for security but bad for data management. How can you easily access and manage your secured data? This question came up several time from my colleagues, so let’s write down the process step by step, for future use.
    This kind of configuration is good for security but bad for data management. How can you easily access and manage your secured data? This question came up several time from my colleagues, so let’s write down the process step by step, for future use.

    ![basic_RDS_bastion_architecture](https://user-images.githubusercontent.com/6451436/61270199-7ca79f00-a790-11e9-9c05-9ad2bbbea517.jpg)
  17. @kshailen kshailen created this gist Jul 16, 2019.
    2 changes: 2 additions & 0 deletions Connecting_postgress_RDS_from_local_via_bastion.md
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,2 @@
    When it comes to databases and AWS VPC, best practice is to place your database in private subnet. By definition, private subnet in AWS is not reachable from the Internet because no Internet gateway is attached to private subnet. This is the way you protect your data.
    This kind of configuration is good for security but bad for data management. How can you easily access and manage your secured data? This question came up several time from my colleagues, so let’s write down the process step by step, for future use.