Allow Remote Connection

Configure client authentication and listen address for remote connection to PostgreSQL server

In order to access our PostgreSQL server hosted on AWS from our local computers, we must configure two different files to allow connection from any IP address to our server.

First, we will edit the "pg_hba.conf" to configure client authentication. Then, we will edit the "postgresql.conf" file to allow any IP address to connect to our server.

Configure Client Authentication

In this section, we will

1. define what client authentication is

2. use an example to understand its purpose

3. review different methods for implementation

At the end, we'll configure the client authentication file to use one of the securest methods for our server.

Note: Understanding Client Authentication

Imagine the following scenario: you have a PostgreSQL server with two databases (db1, db2) and three users (user1, user2, user3).

1. Bob is user1, who has access to d1

2. Dave is user2, who has access to db2

3. Sally is user3, who has access to d1 and db2

Bob wants to connect to the PostgreSQL server through their pgAdmin4 (i.e a client application). In order to connect, Bob has to tell the server that they want to connect as "user1" to "db1".

PostgreSQL server determines the identity of the client (i.e Bob through pgAdmin4) in a process called Authentication (9). Through authentication, the server also determines whether Bob is allowed to connect to db1 as user1.

PostgreSQL has different methods of client authentication which are further explained in these two documentations:

• pg_hba.conf File Section Documentation

• Password Authentication Documentation

Editing pg_hba.conf file

For this example, we'll be setting our authentication method as md5. To do this, we must edit the client authentication configuration file titled "pg_hba.conf" using a text editor.

To edit "pg_hba.conf" using the "vim" text editor,

• Type the following line of code in your terminal

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

• Using your keyboard arrow keys, scroll through the file until you've reached the portion of the file that looks like a table, where the last column is labeled "METHOD"

• Under IPv4 and IPv6 local connections, change the method to "md5"

  • If the method is already set to "md5", then you do not need to do anything.

• Additionally, in the "ADDRESS" column, change the IP addresses to be "0.0.0.0/0" for IPv4 and "::/0" for IPv6

To type in the vim editor, press "i" to be in insert mode, type as you normally would, then press ESC to be in command mode. To exit and save, type ":wq".

Here are the before and after pictures for your IPv4 and IPv6 connection configurations:

Before

After

Change Listening Address

In "postgresql.conf", the variable "listening_addresses" is set to "localhost". We will change "localhost" to "*" in order for the server to listen for any IP connection. (8)

• Open the file in vim by pasting the result in the last code here

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

• Change "listening_addresses = localhost" to "listening_addresses = *"

Before

After

Make sure listen_addresses = "*" does not have a # in front of it or else the statement will be commented out.

• Now that you've changed these files, restart your PostgreSQL server using the following line of code:

sudo systemctl restart postgresql

You are all set to connect remotely to your server!