To configure PostgreSQL for remote connections, you need to adjust the PostgreSQL configuration files and your system’s firewall settings. Here’s a step-by-step guide:

1. Modify postgresql.conf

  • Locate the postgresql.conf file. It’s usually located in /etc/postgresql/{version}/main/ on Ubuntu or /var/lib/pgsql/{version}/data/ on CentOS.
  • Open the file:
  sudo nano /etc/postgresql/{version}/main/postgresql.conf
  • Find the listen_addresses setting, uncomment it, and set it to '*' to allow connections from any IP address:
  listen_addresses = '*'
  • Save and exit the file.

2. Edit pg_hba.conf

  • Locate the pg_hba.conf file in the same directory.
  • Open the file:
  sudo nano /etc/postgresql/{version}/main/pg_hba.conf
  • Add the following line to allow connections from a specific IP range or all IPs (0.0.0.0/0 for all):
  host    all             all             0.0.0.0/0               md5

You can replace 0.0.0.0/0 with a more restrictive range (e.g., 192.168.1.0/24).

3. Restart PostgreSQL

  • After making these changes, restart PostgreSQL to apply them:
  sudo systemctl restart postgresql

4. Adjust Firewall Settings (optional)

  • If you have a firewall enabled, allow connections on PostgreSQL’s default port (5432):
  sudo ufw allow 5432/tcp

5. Connect Remotely

  • You can now connect to PostgreSQL remotely using a connection string like this:
  psql -h <server-ip> -U <username> -d <database>
  • Ensure that the user trying to connect remotely has the correct privileges and the password is set up properly.

Let me know if you need any further help!