How to Connect to a Remote PostgreSQL Database

Connecting to a remote PostgreSQL database can prove daunting for some teams. Your organization risks losing valuable time, which then leads to lost productivity. Thankfully, there are four different ways to connect to a remote PostgreSQL database and improve your team's efficiency.

Prerequisites for Connecting to a Remote PostgreSQL Database

Before trying to access a remote PostgreSQL database, confirm that:

• You've installed PostgreSQL on your machine, and it's properly running (same as the server).
• You've installed pgAdmin, psql, or any third-party tool that supports the connection. If you haven’t, you can follow the instruction in this guide.
• You have all the required access credentials, such as the database name, hostname, port, username, and password.

Configuring PostgreSQL for Remote Connections

The default setting for PostgreSQL allows only localhost connections. However, you can adjust a few configuration files to enable external or remote connections. 

Editing 'postgresql.conf'

This adjustment enables connections beyond the local machine:

1. Locate the "postgresql.conf" file from either "/etc/postgresql/<version>/main/ directory" or "/var/lib/pgsql/data/" and open it using a text editor. 
2. Allow for remote connections by updating the "listen_addresses" setting to "listen_addresses = '*'" — in place of "*" specify the only IP addresses that should be allowed to connect to the server. 
3. Save your changes and close the text editor. 
4. Run the following command to restart PostgreSQL: "sudo systemctl restart postgresql."

Modifying 'pg_hba.conf'

This update controls who (users, IP addresses) can access the database by requiring password authentication (md5). 

1. Open the "pg_hba.conf" file from the "/etc/postgresql/12/main/" directory.
2. Add an entry for remote access by modifying it this way:

For IPV4 addresses: host all    all     0.0.0.0/0     md5 
 For IPV6 addresses: host all    all     ::0/0        md5 

Ensuring Network-Level Security

Allowing remote connections to access a PostgreSQL server can expose it to threat actors and other security risks. As such, you should take proactive measures to protect your network and database. 

Configuring Firewall Rules

Firewalls act as a barrier that restricts external access. As such, you must configure the server’s firewall to allow traffic on its default port, usually port 5432. 

For a better illustration, let’s use an example in which you’re running PostgreSQL on a Linux server. To configure the firewall and permit traffic on port 5432, you can use ufw by running the following command:

sudo ufw allow 5432/tcp

You can also use a tool like iptables by running the following command:

iptables -A INPUT -p tcp -m state --state NEW --dport 5432 -j ACCEPT

For cloud environments, you can adjust the security group settings or firewall rules within the cloud platform's management console. 

Using a VPN or SSH Tunnel

Using a VPN, you can keep PostgreSQL's default firewall settings and even limit its "listen_addresses" to the localhost. With a VPN, the database isn't directly exposed to the public internet.

On the other hand, SSH tunnels forward traffic from a local port on your machine to the PostgreSQL server over an encrypted connection. This helps protect data during transmission. 

Use the following command to establish an SSH tunnel:

ssh -L 5432:localhost:5432 user@remote_host

Connecting to a Remote PostgreSQL Database via Different Methods

After establishing the required configurations and ensuring network security, you can now connect to your remote PostgreSQL database. 

1. Using psql

This method leverages the following command syntax:

psql -h <hostname> -p <port> -U <username> -d <database>

When you press "Enter," you'll receive a prompt to enter your password for authentication. 

2. Using pgAdmin

1. Launch pgAdmin from your computer and click the "Add New Server" option.
2. Fill in the required information on the "Connection" tab at the top of the server screen.
3. Click "Save."

3. Using 3rd-Party Clients (e.g., DBeaver)

1. Launch the DBeaver application, select "Database," and then choose "New Database Connection."
2. Choose "PostgreSQL," and then enter the connection credentials.
3. Click "Test Connection" to verify the connection, then "Finish," and the application will connect you to the database.

4. Connecting With Different Programming Languages

Here's how to use various programming languages to connect to a remote PostgreSQL database.

Python

import psycopg2               
 conn = psycopg2.connect(      
          host="remote_host",  
          database="db_name",  
          user="username",     
          password="password"  
 )                            

Node.js

const { Client } = require('pg'); 
 const client = new Client({       
   host: 'remote_host',            
   database: 'db_name',            
   user: 'username',               
   password: 'password',           
 });                               
 client.connect();                

Java

Connection connection = DriverManager.getConnection(                         
       "jdbc:postgresql://remote_host:5432/db_name", "username", "password");

To connect to PostgreSQL, you need the PostgreSQL JDBC driver. Therefore, include it as a dependency in your project.

Using Gradle, add this to your build.gradle file:

implementation 'org.postgresql:postgresql:42.6.0' // Use the latest version

Common Issues and Tips

When connecting remotely, you may encounter authentication issues for several reasons, including wrong username/password combinations or incorrect "pg_hba.conf" settings. 

If you receive a "connection refused" message, it might be because you haven't configured PostgreSQL to listen to the right IP addresses or your firewall to allow traffic on port 5432. 

For instance, for incorrect client authentication configuration:

• Locate the "pg_hba.conf" file on your PostgreSQL server. It's typically found in the data directory (e.g., "/var/lib/postgresql/<version>/main/").
• Ensure there's a rule allowing connections from your client’s IP address. Add a line like this: "host    all    all   <client_IP_address>/32   md5 (Replace <client_IP_address> with the actual IP of the client)."
• Save the changes and restart PostgreSQL: "sudo systemctl restart postgresql."

Best Practices for Secure Remote Connections

The most straightforward way to secure remote PostgreSQL connections is by using complex passwords. Additionally, you can:

• Use the principle of least privilege (PoLP) to limit privileges to only what users require to perform their duties. 
• Use SSL/TLS to encrypt traffic between the client and server by updating "postgresql.conf" with "ssl = on."
• Review and deactivate any user accounts that are no longer in use.
• Restrict access to the PostgreSQL server by allowing only trusted IP addresses to connect. This can be managed through firewall rules or PostgreSQL's "pg_hba.conf" file.
• Bind the PostgreSQL service to a specific IP address rather than listening on all interfaces (0.0.0.0). Configure this in the "postgresql.conf" file

How StrongDM Helps Secure Access to PostgreSQL

StrongDM can help secure your databases by centralizing management. You get:

• Comprehensive logging and monitoring: You can monitor and audit user access through our built-in recording and logging mechanism. This provides full visibility into who accessed the database, what actions were performed, and when. 
• Seamless integration with your existing tools: StrongDM effortlessly integrates with your organization's existing tech stack, including identity providers, DevOps tools, and security frameworks. This minimizes disruptions during implementation and enhances compatibility across platforms.
• Simplified onboarding: With StrongDM, you can quickly onboard remote and distributed teams without sacrificing security. The platform streamlines access provisioning, ensuring team members can securely connect to PostgreSQL databases in minutes.
• Ease of configuration: Our intuitive UI simplifies the complex configurations that are often associated with PostgreSQL.

Book a demo today to see StrongDM in action!