Allow remote connections on PostgreSQL

To configure PostgreSQL to accept connections from hosts other than localhost, you'll need to modify two files:

1. postgresql.conf: This file controls various settings related to the PostgreSQL server.
2. pg_hba.conf: This file controls client authentication and permissions (if not configured properly the PostgreSQL instance will accept unauthenticated connections).

Here’s how you can configure both:

Edit postgresql.conf

This file controls how PostgreSQL listens for incoming connections.

• Find and edit the listen_addresses setting: Open postgresql.conf, typically located in the PostgreSQL data directory (e.g., /var/lib/pgsql/data/ or /etc/postgresql/{version}/main/ depending on your installation). Search for listen_addresses (it might be commented out).

listen_addresses = 'localhost'

Change it to:

listen_addresses = '*'

This will allow PostgreSQL to listen on all available network interfaces. If you want to restrict it to specific IP addresses, you can replace '*' with a comma-separated list of IP addresses, like listen_addresses = 'localhost,192.168.1.100'.

Edit pg_hba.conf

This file defines client authentication rules for connecting to the database.

Find and modify the authentication rules: Open pg_hba.conf (usually in the same directory as postgresql.conf). You’ll see entries similar to the following for local connections:

IPv4 local connections:

host    all             all             127.0.0.1/32            md5

IPv6 local connections:

host    all             all             ::1/128                 md5

To allow remote connections, add a line for the IP addresses or subnets you want to allow. For example, to allow all IP addresses (not recommended for production, but fine for testing), add:

host    all             all             0.0.0.0/0               md5

Or to limit it to a specific subnet (e.g., 192.168.1.0/24):

host    all             all             192.168.1.0/24          md5

• Replace md5 with other authentication methods if necessary (like password or scram-sha-256).

Restart PostgreSQL

After making these changes, restart the PostgreSQL service to apply them:

sudo systemctl restart postgresql

or

sudo service postgresql restart

Check Firewall Settings (if applicable)

If your server has a firewall enabled, make sure that the port PostgreSQL uses (by default, port 5432) is open for the IP addresses you want to allow.