Enabling Remote Connections to PostgreSQL on Ubuntu

Enabling remote connections to a PostgreSQL server on Ubuntu 20.04 involves several steps to configure the PostgreSQL server and open up the necessary ports. Additionally, I'll provide information on changing the password for an existing PostgreSQL user if needed.

 

Step 1: Modify the PostgreSQL Configuration File

Open the PostgreSQL configuration file "postgresql.conf" using a text editor. The file is typically located in the /etc/postgresql/12/main directory. To open the file from the Linux Terminal, execute:

sudo nano /etc/postgresql/12/main/postgresql.conf

Find the line #listen_addresses = 'localhost' and uncomment it by removing the # character at the beginning of the line.

Next, change the value of listen_addresses to "*", allowing PostgreSQL to listen on all available IP addresses. Alternatively, you can specify a specific IP address or a range of IP addresses that are allowed to connect to the server.

Step 2: Modify the pg_hba.conf File

Open the "pg_hba.conf" file using your preferred text editor, typically located in the /etc/postgresql/12/main directory. To open the file from the Linux Terminal, execute:

sudo nano /etc/postgresql/12/main/pg_hba.conf

Find the section that allows local connections and modify it as follows:

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5

Change it to allow connections from any IP address (0.0.0.0/0):

# IPv4 local connections:
host    all             all             0.0.0.0/0            md5

Step 3: Allow Port 5432 Through the Firewall

To enable traffic on port 5432 through the firewall, execute the following command:

sudo ufw allow 5432/tcp

Step 4: Restart PostgreSQL

Restart the PostgreSQL service to apply the changes:

sudo service postgresql restart

Changing the Password for an Existing PostgreSQL User:

If you need to change the password for an existing PostgreSQL user, you can follow these steps:

Log in to the PostgreSQL database as a superuser or a user with administrative privileges. You can do this by using the psql command:

sudo -u postgres psql

Change the password for the user using an SQL command. Replace username with the actual username and new_password with the new password you want to set:

ALTER USER username WITH PASSWORD 'new_password';

Exit the PostgreSQL prompt:

\q

After completing these steps, you should be able to connect to the PostgreSQL server from a remote machine and have successfully changed the password for an existing user. However, please keep in mind that allowing remote access to a PostgreSQL server can pose security risks. Therefore, it's recommended to use strong passwords, encryption, and firewall rules to protect your system.