DEV Community

Sospeter Mong'are
Sospeter Mong'are

Posted on

How to Access a PostgreSQL Database Remotely

#postgres #postgressql #sql #database

PostgreSQL is a powerful open-source relational database system. By default, it only allows connections from the local machine. But what if you want to connect to it from another device or client like DBeaver, pgAdmin, or your local development environment?

In this article, you’ll learn how to securely enable remote access to PostgreSQL step-by-step.

Prerequisites

  • A server with PostgreSQL installed (e.g., Ubuntu, CentOS)
  • SSH access to that server
  • PostgreSQL user credentials
  • A tool like DBeaver, pgAdmin, or psql on your local machine

Step 1: Configure postgresql.conf

This file controls PostgreSQL server behavior. By default, it only listens on localhost.

  1. Open the config file: 
sudo nano /etc/postgresql/14/main/postgresql.conf
Enter fullscreen mode Exit fullscreen mode

Adjust version/path if different, e.g., /var/lib/pgsql/14/data/postgresql.conf

  1. Find the line: 
#listen_addresses = 'localhost'
Enter fullscreen mode Exit fullscreen mode
  1. Change it to: 
listen_addresses = '*'
Enter fullscreen mode Exit fullscreen mode
  1. Save and close the file.

Step 2: Modify pg_hba.conf

This file defines who can connect, from where, and how.

  1. Open it: 
sudo nano /etc/postgresql/14/main/pg_hba.conf
Enter fullscreen mode Exit fullscreen mode
  1. Add a line at the end to allow remote IPs: 
# Allow any IP (not recommended for production)
host    all             all             0.0.0.0/0               md5

# OR restrict to your specific IP
host    all             all             203.0.113.10/32         md5
Enter fullscreen mode Exit fullscreen mode

Replace 203.0.113.10 with your real IP address.

  1. Save and exit.

Step 3: Restart PostgreSQL

Apply changes:

sudo systemctl restart postgresql
Enter fullscreen mode Exit fullscreen mode

Step 4: Allow Port 5432 on the Firewall

For Ubuntu with UFW: 

sudo ufw allow 5432/tcp
sudo ufw reload
Enter fullscreen mode Exit fullscreen mode

For Cloud Providers (AWS, GCP, etc.):

Go to your firewall or security group settings and allow port 5432 for your IP address.

Step 5: Test the Remote Connection

Use a tool like DBeaver, pgAdmin, or psql:

Connection Details

  • Host: Your server's IP (e.g., 176.18.293.12)
  • Port: 5432
  • Database: your database name
  • Username: PostgreSQL user
  • Password: user's password

Example using psql: 

psql -h 176.18.293.12 -U your_user -d your_db
Enter fullscreen mode Exit fullscreen mode

Troubleshooting Tips

Problem Solution
Connection refused Check PostgreSQL is running, firewall is open, and listen_addresses is set correctly
Timeout Verify network connectivity and firewall rules (including cloud provider)
FATAL: password authentication failed Ensure correct username and password are used and that pg_hba.conf uses md5 or scram-sha-256 for authentication

Security Best Practices

  • Never use 0.0.0.0/0 in production; always restrict access to specific IPs.
  • Use SSH tunneling or VPN for a secure connection.
  • Consider using SSL/TLS encryption for PostgreSQL connections.
  • Regularly update PostgreSQL and your server's firewall rules.

Conclusion

Allowing remote access to your PostgreSQL server can unlock powerful development and administrative workflows — but it must be done securely. By properly configuring postgresql.conf, pg_hba.conf, and your firewall, you can safely connect to your PostgreSQL database from anywhere.

Top comments (0)