Prerequisites
In this instructional guide, we will be discussing how to enable remote connections to a PostgreSQL server installed on Ubuntu. To follow along with the steps provided, you will need to have the following installed on your system:
- Ubuntu 20.04
- PostgreSQL server version 12
Please ensure that you have these requirements installed before proceeding with the guide.
Enable remote access to Postgres
To allow remote access to a PostgreSQL 12 server on Ubuntu 20.04, you need to follow the steps below:
1. Modify the PostgreSQL configuration file
Open the PostgreSQL configuration file “postgresql.conf” using your preferred 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
Then, find the line #listen_addresses = 'localhost' and uncomment it (remove the # character at the beginning of the line).
Next, change the value of “listen_addresses” to “*”. This allows 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.
2. Modify the pg_hba.conf file
Open the “pg_hba.conf” file using your preferred text editor. The file is typically located in the /etc/postgresql/<version>/main directory. To open the file from the Linux Terminal, execute: sudo nano /etc/postgresql/<version>/main/pg_hba.con
Take the following section:
Add new line to IPv4 local connection:
# IPv4 local connections:
host all all 0.0.0.0/0 md5
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
4. Restart PostgreSQL
Run the following command to restart PostgreSQL: sudo service postgresql restart
After completing these steps, you should be able to connect to the PostgreSQL server from a remote machine using a PostgreSQL client. However, please note that allowing remote access to a PostgreSQL server can bear a security risk, so it is recommended to use secure passwords, encryption, and firewall rules to protect your system.
How to allow remote connections to PostgreSQL on Windows
By default, when installing PostgreSQL, you get it configured to only accept connections from the local machine. This is a security measure to prevent unauthorized access to the database server. However, in some cases, you may need to allow connections from remote hosts. Let us look at how to do this.
To allow remote connections to PostgreSQL on Windows, follow these steps:
1. Edit the PostgreSQL configuration file
1.1 Open the PostgreSQL installation directory and locate the postgresql.conf file. By default, the configuration file for PostgreSQL (version 14) is located at C:\Program Files\PostgreSQL\<version>\data\postgresql.conf.
1.2 Make a backup of the file before making any changes.
1.3 Open the postgresql.conf file in a text editor.
1.4 Find the line that reads #listen_addresses = 'localhost' and uncomment it if it is commented (remove the ‘#’ character at the beginning of the line). Next, to ensure that PostgreSQL is configured to accept connections from any address, check the value of “listen_addresses” – it should be set to “*”.
Note: You can also use a specific IP address for the PostgreSQL server to listen on.
2. Edit the pg_hba.conf file to allow remote connections
2.1 Open the PostgreSQL installation directory and locate the pg_hba.conf file. By default, it is located at C:\Program Files\PostgreSQL\14\data\pg_hba.conf (for PostgreSQL 14).
2.2 Make a backup of the file before making any changes.
2.3 Open the pg_hba.conf file in a text editor.
2.4 Add a new line at the end of the file to allow remote connections. The line should have the following format:
# IPv4 local connections:
host all all 0.0.0.0/0 md5
This line allows connections from any IP address (0.0.0.0/0) and requires a password for authentication (md5).
2.5 Restart PostgreSQL (for example, from the Computer Management console).
3. Configure the Windows Firewall to allow incoming connections to PostgreSQL
3.1 Launch Windows Control Pane.
3.2 Open Windows Defender Firewall.
3.3 Click Advanced settings on the left-hand side of the window.
3.4 Click Inbound Rules on the left-hand side of the window.
3.5 Click New Rule on the right-hand side of the window.
3.6 Select Port as the type of rule and click Next.
3.7 Select TCP as the protocol and enter 5432 as the port number. Click Next.
3.8 Select Allow the connection and click Next.
3.9 Select the network types for which the rule should apply (Domain, Private, or Public). Click Next.
3.10 Enter a name and description for the rule and click Finish.
After completing these steps, remote connections to PostgreSQL should be allowed on the Windows machine.
Top comments (0)