A Guide to Accessing a Remote PostgreSQL Database in Ubuntu Server

In the interconnected world of today, accessing databases remotely has become a fundamental skill for developers and database administrators. PostgreSQL, with its robust features and open-source flexibility, is a popular choice for managing databases across various applications. This guide will walk you through the essential steps to connect to a remote PostgreSQL database, ensuring secure and efficient access to your valuable data.

step 1: Access your database server
To begin managing your PostgreSQL remotely, you first need to securely log into your server where the database is hosted. SSH (Secure Shell) is the most commonly used method for secure access over unsecured networks:

• Open your terminal.
• Use the SSH command to connect to your server: "ssh username@server_ip" (replace username with your server's username and server_ip with the IP address of your server)
• Enter your password when prompted to establish a secure connection.

Step 2: Locate and Modify pg_hba.conf
After securely accessing your server via SSH, the next step is to configure the pg_hba.conf file, which allows you to set rules that determine who can connect to your database and how they authenticate themselves.

• Locate the pg_hba.conf file, usually found in the PostgreSQL data directory. You can use the following command to find its exact location:

sudo -u postgres psql -c 'SHOW hba_file'

• Open the pg_hba.conf file with a text editor of your choice. For instance, you can use nano or vi: sudo vi /path_to_your_pg_hba.conf.

sudo vi /etc/postgresql/14/main/pg_hba.conf

• Modify or add entries to define which hosts are allowed to connect, which database they can access, which user they can connect as, and which authentication method should be used. An entry looks like this:

# If need to access from all IPs use 0.0.0.0/0
# TYPE  DATABASE    USER    ADDRESS               METHOD
host    all         all     0.0.0.0/0             md5

# If need to access from a Specific IP Address
# TYPE  DATABASE    USER    ADDRESS               METHOD
host    all         all     192.168.1.5/32        md5
host    all         all     10.0.0.5/32           md5

Step 3: Locate and Modify postgresql.conf
After configuring client authentication in the pg_hba.conf file, the next essential step is to ensure that your PostgreSQL server is set up to accept remote connections by modifying the postgresql.conf file:

• Locate the postgresql.conf file, usually found in the same directory as the pg_hba.conf file. You can use the following command to find its exact location:

sudo -u postgres psql -c "SHOW config_file"

• Open the postgresql.conf file with a text editor of your choice. For instance, you can use nano or vi: sudo vi /path_to_your_pg_hba.conf.

sudo vi /etc/postgresql/14/main/postgresql.conf

• Modify the listen_addresses Setting: In the postgresql.conf file, look for the listen_addresses parameter. You'll need to change its value to allow connections from other machines. To listen on all available IP addresses, set it to '*'. This allows connections from any IP, which is useful if you don’t want to restrict access at the network level or specify particular IP addresses if you want to restrict which machines can connect.

# To listen on all available IP addresses 
listen_addresses = '*'

# To listen on specific IP addresses
listen_addresses = '192.168.1.5, 10.0.0.5'

Step 4: Allowing Port 5432 for PostgreSQL Access (If Firewalls are active)
For remote clients to connect to your PostgreSQL server, it's necessary to allow traffic on port 5432 through your firewall. This is the default port used by PostgreSQL. Here’s how you can configure the firewall rules on a ubuntu server using ufw (Uncomplicated Firewall) and firewalld, two common firewall management tools:

# If using ufw
sudo ufw allow 5432/tcp
sudo ufw reload

#If using firewalls
sudo firewall-cmd --zone=public --add-port=5432/tcp --permanent
sudo firewall-cmd --reload

Step 5: Restart PostgreSQL Server
After making the necessary configuration changes in postgresql.conf and pg_hba.conf, it's essential to restart your PostgreSQL server to apply these changes. Restarting the server ensures that all new settings take effect immediately. Here’s how you can restart the PostgreSQL server:

sudo systemctl restart postgresql