Metawerx Support - How to set up a remote PostGreSQL connection
Downloading the tools
For these instructions, you will need:
- plink on Windows, or ssh on Linux/Unix - to establish the tunnel - download here
- a postgresql client, we recommend pgAdmin (OpenSource), Navicat (Commercial, 30 Day Trial) or Navicat Lite (free version)
- preferably a cable/ADSL connection - PostGreSQL admin tools are quite 'chatty' and access will be slow over dial-up
Download these tools before continuing.
Starting the tunnel
We provide access to PostGreSQL over an SSH tunnel for security. You will need to start the tunnel before using connecting with your client.
On Windows, the tunnel is established using plink. On Linux, the ssh tool is used. These are both command line tools.
You will have been provided with:
- a metawerx username, which will be referred to as metausername below
- a metawerx password, which will be referred to as metapassword below
- a postgres server name and port (in most cases this will be pgsql.metawerx.net:5440), these are referred to as servername:serverport below.
You will also use a new username for the SSH connection. This will always be the same as your normal metawerx username, but with ssh_ on the front. eg: ssh_neale
To start the tunnel, execute the following command on Windows:
plink -C -v -l ssh_metausername -pw metapassword -L 5440:servername:serverport ssh-meta1.metawerx.net
or on Linux:
ssh -Cv -L 5440:servername:serverport ssh_metausername@metawerx.net
Example, if your username is cassie, password cassiepass, server pgsql.metawerx.net:5440, and you are using Windows, use the following command:
plink -C -v -l ssh_cassie -pw cassiepass -L 5440:pgsql.metawerx.net:5440 ssh-meta1.metawerx.net
This will open a tunnel between your localhost port 5440, and our PostGreSQL server.
- The first time you connect, or at other times we regenerate our key files, you will be asked to store the key in your cache. Answer "y" (yes) to continue.
- All data transmitted through the tunnel will be encrypted by SSH
- A prompt will appear, awaiting commands. Do not enter any commands - just leave the window open and minimize it.
- When you close this window, the tunnel will be closed, and your connection will be lost
- PostGreSQL uses port 5432 by default - we are using port 5440 to avoid conflicts with any local version of PostGreSQL already running on your machine
- You are now ready to start your postgres client
- If you want to know how this works, or are creating your own tunnel, see the SSH Tunnel page for a full explanation
Using Navicat for PostGreSQL
- Navicat
- Ensure the tunnel is running, then start Navicat
- Click Connection
- Enter the following details:
- Connection Name: anything you like, such as 'metawerx tunnel' or your login name
- Host name/IP address: localhost (or 127.0.0.1, same thing)
- Port: 5440
- User name: your metawerx username
- Password: your metawerx password
- (x) Save password
- Click Test Connection - a dialog should appear that says "Connection Successful"
- Click OK
- Your new database connection will appear in the tree on the left, double click it to connect
- A series of databases will appear - find your database, and double click it to open it
- You will not be able to access databases owned by other users
If you want to eliminate the other databases from the list:
- Right-click the connection and select Connection Properties
- Click the Advanced tab
- Click (x) Use Advanced Connections
- A list of databases appear, select your database(s) from the list
- The first time you select, the tickbox will turn grey - click it again so it turns black-and-white
- For each database, select the name so that it highlights, and enter the correct username and password
- Click OK when finished
Using pgAdmin
- Ensure the tunnel is running, then start pgAdmin
- Click the icon that looks like a power-cord (Add a connection to a server)
- Enter the following details:
- Address: localhost (or 127.0.0.1, same thing)
- Description_: anything you like, such as 'metawerx tunnel' or your login name
- Service: <leave blank>
- Port: 5440
- SSL: <leave blank - connection already encrypted, this will just slow it down>
- Maintenance DB: template1
- Username: your metawerx username
- Password: your metawerx password
- (x) store password
- (x) connect now
- The new connection will appear on the tree on the left, double click to open it
- Double-click the databases item
- A series of databases will appear - find your database, and double click it to open it
- You will not be able to access databases owned by other users
See Also
--Neale Rudd
