Securely Manage Remote PostgreSQL Servers with pgAdmin on Mac OS X
Updated by Phil Zona
pgAdmin is a free, open-source PostgreSQL database administration GUI for Microsoft Windows, Apple Mac OS X and Linux systems. It offers excellent capabilities with regard to database server information retrieval, development, testing, and ongoing maintenance. This guide will help you get up and running with pgAdmin on Mac OS X, providing secure access to remote PostgreSQL databases. It is assumed that you have already installed PostgreSQL on your Linode in accordance with our PostgreSQL installation guides.
Visit the pgAdmin download page to obtain the most recent version of the program. Save the installer to your desktop and launch it. Read the license agreement and click the “Agree” button to continue.
After the program has uncompressed itself, you’ll see a pgAdmin icon in a Finder window. You may drag this to your Applications folder or your dock.
Configure SSH Tunnel
While PostgreSQL supports SSL connections, it is not advisable to instruct it to listen on public IP addresses unless absolutely necessary. For this reason, you’ll be using following command to create an SSH tunnel to your database server, replacing
username with your Linux username and
remote-host with your Linode’s hostname or IP address:
ssh -f -L 5433:127.0.0.1:5432 username@remote-host -N
Although PostgreSQL uses port 5432 for TCP connections, we’re using the local port 5433 in case you decide to install PostgreSQL locally later on.
Launch pgAdmin and you’ll be presented with a default view containing no servers. Click “File -> Add Server” as shown below.
If you’re having problems connectiong you may need to check PostgreSQL’s configuration to ensure it accepts connections. Modify the following lines in
1 2 3
listen_addresses = 'localhost' port = 5432
Restart PostgreSQL to activate these changes. This command may vary among different distributions:
sudo systemctl restart postgresql
In the “New Server Registration” dialog that appears, enter appropriate values for your server name and PostgreSQL account credentials. Be sure to specify “localhost” for the “Host” field, as you’ll be connecting via your SSH tunnel. In the username and password fields, enter the credentials you specified when setting up PostgreSQL.
For greater security, uncheck the “Store password” box. Click “OK” to connect to your server.
You will be presented with a full view of the databases that your user account has access to:
Congratulations! You’ve securely connected to your remote PostgreSQL server with pgAdmin III.
You may wish to consult the following resources for additional information on this topic. While these are provided in the hope that they will be useful, please note that we cannot vouch for the accuracy or timeliness of externally hosted materials.
This guide is published under a CC BY-ND 4.0 license.