Summary

I’ve been working on some onboarding exercises here at Isos Technology where I’m tasked with getting familiar with how we deploy and customize Atlassian applications. As part of this, I thought it would be a good idea to explore the database schema and look around a bit at some tables. I began with psql, the command line interface for postgresql. But after awhile I thought it would be nice to have a graphical interface to explore with. A quick duckduckgo search and I learn there’s something called pgadmin. I was trying to set up this interface to browse a database on a vagrant box using the built in ssh tunnel feature when I encountered a few problems.

The Experience section below is a depiction of my experience and internal dialogue through this process.

Below this is a “how to” on installing pgadmin4, and creating and connecting to a server, including a solution to what I encountered.

Finally, there’s a brief conclusion of my thoughts with respect to this issue and the tool used to solve it.

The Experience

Cool, pgadmin has a built in ssh tunnel feature, no command line tunnels for me today! Well that ended quickly…

Ok… well perhaps it can’t generate the ssh tunnel and connect simultaneously or something, I’ll uncheck the connect now box.

Great, it let me save the configuration. This should go swimmingly now!  I don’t have a password on my rsa key, so I’ll leave that blank and enter my database password.

Uh oh…  I’m guessing this is because for some reason the rsa key must have a password for pgadmin to create the tunnel for me.

I suppose I’ll just create the tunnel manually and connect that way…

How To and Solution

  1. Install pgadmin4
    brew cask install pgadmin4
  2. Set up ssh access to the machine with rsa keys.  You can use a password but this is the advised way of connecting over ssh for security.
    1. Generate a keypair if you don’t already have one.

      ssh-keygen -t rsa -b 4096
      # then complete the prompts for your desired configuration
    2. Append your public key to the authorized_keys file of the user you plan to connect with.
      # this public key will be here unless you specified a different location when generating your keys
      cat /Users/your_username/.ssh/id_rsa.pub
      # copy the output of that command, access the machine hosting your database, and append it to the authorized keys file
      # the following command is to be executed on the database host
      echo "your_key_text_here" >> /home/database_host_username/.ssh/authorized_keys
      
  3. Forward a local port we’ll enter in your server configuration to the actual database port on the database host.
    # -N says don't execute anything, not even a shell, just forward the port and leave the tunnel open
    # -L says forward the local port(5433) to the remote localhost's port(5432)
    ssh -p database_host_ssh_port database_host_username@database_hostname_or_IP -N -L 5433:127.0.0.1:5432
    
  4. Open pgadmin4 and add a server.
    1. On the dashboard tab click Add New Server
    2. Name the server, enter your database name, and database user.  In the port location use the local port you forwarded to the remote hosts database port, in this example 5433.
    3. Click Save.  Pgadmin will connect and you should now be able to browse the database.
    4. Expanding jira>Databases>jiradb>Schemas>public>Tables    I can control click a table and view all the rows.  This also conveniently shows me the SQL I could use to view this data with the cli interface psql.
  5. For the equivalent in psql
    # switch to root
    sudo su
    # switch to the database user
    su postgres
    # open the cli to the database
    psql jiradb
    # display the desired table
    SELECT * FROM public.app_user;
    
    jiradb=# SELECT * FROM public.app_user;
      id   | user_key | lower_user_name
    -------+----------+-----------------
     10000 | sky      | sky
    (1 row)
    

Conclusion

Often things don’t work exactly as expected.  Forwarding ports over an ssh tunnel is a valuable and useful tool to have in your toolbox.  I believe everyone who has to do any type of admin work should have at least a cursory understanding of this concept.  This is not the first time ssh port forwarding has gotten me out of a bind and I’m certain it won’t be the last.  Now get out there and do database things!  All the database things!