PostgreSQL, SSH tunnels, and Induction.app

For my new personal project I’ve been using software that I’m not familiar with. One of these pieces of software is PostgreSQL, an open-source relational database system. I use MySQL for any projects I’ve done that need a database, with the exception being a few instances of SQLite. It was easy to install PostgreSQL on my Debian 7 server:

apt-get install postgresql

Once I had that running, I followed a few different guides for getting everything in order. I found that the PostgreSQL guides were technically correct, but lacked concrete commands that help newbies feel good when they’re starting. For example, I’ve configured a PostgreSQL server on a test box a long time ago and vaguely remembered that there’s a relationship between the user on the system and the user in Postgres. In the Postgres docs, you’ll be sent from “Getting Started” to chapter 20 if you want to make a new user. A scary experience indeed!

I thought I’d be able to use Sequel Pro to work with PostgreSQL, but it turns out it’s still in development. I searched for a blog post for some hints/best practices and saw someone recommend Induction. I’d seen it mentioned a while ago as an agnostic DB GUI and decided I’d give it a go. I also begrudgingly upgraded my Mac Air from Snow Leopard to Mountain Lion for the privilege of installing. Having remembered the recent PostgreSQL vulnerability, I made sure to keep the database server listening on localhost only, which meant I needed to set up an SSH tunnel from the remote end to my laptop. I’ve done this before and felt like using the example code from the Linode Library. Induction.app was unable to connect to the tunnel because of a mistake I had made along the way. I ended whipping up my own tiny bash script instead:

#!/bin/bash
tunnel_cmd='ssh -f -L 5433:localhost:5432 -l user example.com -N'
echo "Killing any existing processes"
pkill -f "$tunnel_cmd"
echo "Establishing tunnel"
exec $tunnel_cmd

When I finally connected with Induction, I immediately noticed that my user password was in the titlebar of the window. This is one of 61 open issues.  I’m not sure if it’s an oversight or a genius dev idea to prevent people from submitting screenshots with their bug reports. I ended up installing PGadmin, and it’s been a really smooth replacement for a really great editor.