Wayne's Docs Site

Appearance

How to Configure PostgreSQL on Windows for Remote Access ​

Introduction ​

PostgreSQL is a powerful, open-source relational database management system. By default, PostgreSQL is configured to only allow connections from localhost (i.e., the computer running PostgreSQL). This guide explains how to configure PostgreSQL on Windows to allow remote access from other computers.

Prerequisites ​

Before you begin, ensure that:

  • PostgreSQL is installed on your Windows machine.
  • You have administrative privileges to modify PostgreSQL’s configuration files and network settings.
  • You know the IP address or hostname of the computer that will access PostgreSQL remotely.

Step 1: Modify PostgreSQL Configuration ​

PostgreSQL’s configuration for remote access is controlled by two key files:

  1. postgresql.conf
  2. pg_hba.conf

1.1 Locate PostgreSQL Configuration Files ​

By default, the PostgreSQL configuration files are located in the data directory within the PostgreSQL installation folder. You can find them here:

C:\Program Files\PostgreSQL<version>\data\

1.2 Edit postgresql.conf ​

The postgresql.conf file contains the settings for PostgreSQL’s behavior, including network settings.

  1. Open postgresql.conf in a text editor (such as Notepad or Notepad++).
  2. Locate the following line:
shell
#listen_addresses = 'localhost'

This setting controls which IP addresses PostgreSQL listens to for incoming connections.

  1. Uncomment the line (remove the #) and change 'localhost' to '*' to allow connections from all IP addresses:
bash
listen_addresses = '*'
  1. Save the changes to the postgresql.conf file.

1.3 Edit pg_hba.conf ​

The pg_hba.conf file defines the client authentication rules. It controls which clients can connect and what authentication methods they must use.

  1. Open pg_hba.conf in a text editor.
  2. Add a new line at the bottom of the file to allow remote connections. For example, to allow all IP addresses on the local network:
bash
host    all             all             192.168.0.0/24            md5
  • host: Refers to TCP/IP connections.
  • all: Specifies the database (you can replace all with a specific database name).
  • all: Specifies the user (you can replace all with a specific username).
  • 192.168.0.0/24: This defines the IP range allowed to connect (adjust this to your specific network or IP address).
  • md5: The authentication method (MD5 is a common method for password-based authentication).
  1. Save the changes to the pg_hba.conf file.

Step 2: Restart PostgreSQL ​

After modifying the configuration files, you need to restart the PostgreSQL service for the changes to take effect.

  • Open the Services application in Windows (services.msc).
  • Find PostgreSQL in the list of services.
  • Right-click on the service and select Restart.

Alternatively, you can restart PostgreSQL from the command line using:

bash
net stop postgresql
net start postgresql

Step 3: Configure Windows Firewall ​

To allow external clients to connect to PostgreSQL, you may need to configure the Windows firewall to allow inbound connections on the PostgreSQL port (default is 5432).

  1. Open Control Panel > Windows Defender Firewall.
  2. Click on Advanced Settings.
  3. Select Inbound Rules > New Rule.
  4. Choose Port and click Next.
  5. Select TCP, enter 5432 in the Specific local ports field, and click Next.
  6. Allow the connection, then click Next.
  7. Give the rule a name (e.g., "PostgreSQL") and click Finish.

Step 4: Allow PostgreSQL Access from Other Machines ​

4.1 Determine Your Server's IP Address ​

You’ll need the IP address of the machine running PostgreSQL. To find this, run ipconfig in the command prompt. Look for the IPv4 Address under your network adapter.

4.2 Connect Remotely Using a PostgreSQL Client ​

To test the remote connection, use a PostgreSQL client (such as pgAdmin, DBeaver, or psql) from another machine and try to connect to the database using the server’s IP address, port number (5432), and credentials.

For example, using psql:

bash
psql -h <your_server_ip> -U <your_username> -d <your_database_name>

Conclusion ​

You’ve now configured PostgreSQL on Windows for remote access! Be sure to secure your server by restricting access to trusted IP addresses and using strong passwords.

Credit @Wayne19980