How to Configure PostgreSQL on Windows for Remote Access

shape
shape
shape
shape
shape
shape
shape
shape

If you need to access PostgreSQL installed on Windows from another computer, this step-by-step guide will help you configure your PostgreSQL server to allow remote connections securely and efficiently.

Step 1: Install PostgreSQL

If you don’t already have PostgreSQL installed, follow these instructions:

  1. Visit the official PostgreSQL website: PostgreSQL Downloads.
  2. Download the appropriate installer for your version of Windows.
  3. Follow the installer’s instructions to complete the installation.

Step 2: Configure the file postgresql.conf

The file postgresql.conf controls various PostgreSQL server settings. You need to adjust it to allow external connections.

  1. Navigate to the PostgreSQL data directory, which is usually C:\Program Files\PostgreSQL\<versão>\data\.
  2. Open the file postgresql.conf with a text editor.
  3. Find the line:
   #listen_addresses = 'localhost'

Change to:

   listen_addresses = '*'
  1. Save and close the file.

Step 3: Configure the file pg_hba.conf

The file pg_hba.conf controls which computers can connect to PostgreSQL and which authentication methods will be used.

  1. In the same data directory, open the file pg_hba.conf with a text editor.
  2. Add the following line to the end of the file to allow connections from any IP address:
   host    all             all             0.0.0.0/0            md5

Alternatively, for greater security, you can specify a range of trusted IP addresses:

   host    all             all             <IP>/<máscara>       md5
  1. Save and close the file.

Step 4: Open the Port in Windows Firewall

To allow other computers to connect to PostgreSQL, you must open port 5432 in the Windows firewall.

  1. Open the “Control Panel” and go to “System and Security” > “Windows Firewall”.
  2. Click on “Advanced Settings”.
  3. Select “Input Rules” and click on “New Rule”.
  4. Choose “Port” and click “Next”.
  5. Select “TCP” and enter “5432” for the specific local ports.
  6. Click on “Next”, choose “Allow Connection”, and configure the profiles as required (Domain, Private, Public).
  7. Give the rule a name and finish.

Step 5: Restart the PostgreSQL Service

For the changes to the configuration files to take effect, the PostgreSQL service must be restarted.

  1. Open the Windows “Service Manager” (services.msc).
  2. Find the PostgreSQL service, right-click and select “Restart”.

Step 6: Test the Remote Connection

Finally, test the connection from another computer using a database client such as pgAdmin or DBeaver.

  1. Open the database client on the other computer.
  2. Set up a new connection with the IP of the Windows server where PostgreSQL is installed and port 5432.
  3. Test the connection to make sure everything is working properly.

Final considerations

Configuring PostgreSQL for remote access on Windows is a relatively simple process, but requires attention to configuration and security details. Make sure that your network environment allows remote connections and that your firewall and security rules are configured correctly to protect your server from unauthorized access.

By following these steps, you will be able to access your PostgreSQL database remotely, making it easier to manage and interact with your applications. If you have any questions or encounter problems during configuration, consult the official PostgreSQL documentation or seek help in specialized forums.

Leave a Reply

Your email address will not be published. Required fields are marked *

Latest news

Latest news directly from our blog.