Securely Manage Remote PostgreSQL Servers with pgAdmin on Mac OS X

pgAdmin is a free, open-source PostgreSQL database administration GUI for Microsoft Windows, Apple Mac OS X, and Linux systems. It features capabilities with regard to database server information retrieval, development, testing, and ongoing maintenance. This guide provides steps to get you up and running with pgAdmin on Mac OS X, providing secure access to remote PostgreSQL databases.

Before You Begin

1. If you have not already done so, create a Linode account and Compute Instance. See our Getting Started with Linode and Creating a Compute Instance guides.

2. Follow our Setting Up and Securing a Compute Instance guide to update your system. You may also wish to set the timezone, configure your hostname, create a limited user account, and harden SSH access.

3. Install PostgreSQL on your Linode using one of our PostgreSQL installation guides.

Install pgAdmin

1. Visit the pgAdmin download page to obtain the most recent version. Save the installer to your desktop and launch it. Read the license agreement and click the “Agree” to continue.

   

2. After the program is installed, you’ll see a pgAdmin icon in a Finder window. You may drag this to your Applications folder or your dock.

3. Start the pgAdmin interface. A welcome page should be displayed:

Use pgAdmin

1. Open pgAdmin 4.
2. In the Quick Links section, click Add New Server.
3. Under the General tab, enter a name for your server connection. For example: Linode PostgreSQL
4. Navigate to the Connection tab:
   • Hostname/address: localhost. The SSH tunnel redirects this to the Linode server.
   • Port: The PostgreSQL port on your Linode, typically 5432.
   • Maintenance Database: postgres or your database name.
   • Username: Your PostgreSQL username. For example: postgres
   • Password: The password for your PostgreSQL user.
5. Navigate to the SSH Tunnel tab:
   • Use SSH tunneling: Enable this option.
   • Tunnel host: Your Linode’s IP address.
   • Tinnel port: 22 . This is the default SSH port.
   • Username: Your SSH username for the Linode instance.
   • Authentication: Choose Identity file if you are using an SSH key, or Password for password-based authentication.
   • Identity file: If you are using an SSH key, provide the location of the private key file.
   • Password: If you are using password-based authentication, enter your SSH password.
6. Click Save to create the server connection.

Verify Connection

1. After saving the configuration, right-click your new server in pgAdmin and select Connect.
2. If the connection is successful, you should see your databases listed in the Servers panel.

Troubleshooting

• SSH Access Issues: Ensure your Linode firewall allows port 22.

• PostgreSQL Bind Address:

  1. Check the PostgreSQL postgresql.conf file to confirm it’s listening on 127.0.0.1 or localhost. Update listen_addresses if necessary:

  1	listen_addresses = 'localhost'

  2. Restart PostgreSQL after making changes:

  sudo systemctl restart postgresql

• Firewall: Ensure PostgreSQL’s port (5432) is open for local connections.

More Information

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.

• pgAdmin Documentation
• PostgreSQL Documentation