PostgreSQL

Settings to allow other PCs to connect to the PostgreSQL database (Windows version)

Page update date :
Page creation date :

Operating environment

PostgreSQL
  • PostgreSQL 15
Windows (Server)
  • Windows Server 2019
Windows (Client)
  • Windows 11

Prerequisites

Windows
  • Windows Server 2019
  • Windows Server 2016
  • Other versions may work, but are not officially supported.

firewall

In Windows, firewalls prevent connections other than standard ports. It must be configured to be able to connect on the port for connecting to PostgreSQL.

After logging in to the server-side Windows environment, type "firewall" from the start menu and select Open Windows Defender Firewall with Advanced Security.

When the screen opens, select "Inbound Rules" from the menu on the left, and click "New Rule..." from the menu on the right.

For Rule Type, select Port.

For Protocol and Port, select TCP as the protocol. For the port, enter the default of 5432. If you specified a different port number in your PostgreSQL installation, enter that port number.

For "Action", select "Allow connection".

In "Profile", check "Domain" and "Private". I think it's unlikely that you'll let a database be accessed in a "public" environment that will allow unspecified people to access it.

Enter an optional name and description. Please enter it so that it is easy to understand the purpose of opening the port.

Once added, it will appear in the list.

postgresql.conf

Check the settings in postgresql.conf. I don't think you need to change it in version 15.

There is a file in the following location, so open it with a notepad application. Please check the path as it varies depending on the version.

C:\Program Files\PostgreSQL\15\data\postgresql.conf

listen_addresses Look for the parameter called. You do not need to change it if it is set as follows:

listen_addresses = '*'

pg_hba.conf

By default, the database can only be accessed from the local environment.

There is a file in the following location, so open it with a notepad application. Please check the path as it varies depending on the version.

C:\Program Files\PostgreSQL\15\data\pg_hba.conf

There is a possibility that you may make a mistake in correcting it, so please copy and back up the file before changing it just in case.

If you scroll to the bottom, you will find the following lines: This indicates that IPv4 can only be accessed locally.

# TYPE  DATABASE        USER            ADDRESS                 METHOD
  :
host    all             all             127.0.0.1/32            scram-sha-256
  :

Set this ADDRESS to a single or range so that it can be accessed from other PCs. The value you set depends on the network you are building. If you are not sure, please 0.0.0.0/0 allow all with or samenet type to allow all of the range of the same network as the server.

Configuration Examples

# TYPE  DATABASE        USER            ADDRESS                 METHOD
  :
host    all             all             192.168.0.0/24          scram-sha-256
  :

Restart the service

pg_hba.conf These configuration files are only read when PostgreSQL is started, so you need to restart the service. By the way, it is loaded when PostgreSQL starts, so if you restart Windows, it will be loaded at that timing.

After opening the start menu, type "services" to launch it.

Find and select "postgresql-x64-15 - PostgreSQL Server 15" from the middle list. Note that the name will vary depending on the version. Once selected, click "Restart Service" from the left.

Checking the connection from other PCs

Let's see if we can connect to the database from another PC. The connection destination information varies depending on the environment, so only the procedure is described here. I'm using pgAdmin to connect this time, but I think you can connect with other database access tools as well.

After starting pgAdmin on a client PC separate from the database server, right-click on Servers and select "Register > Server...".

On the General tab, the Name should be a name that will help you identify the server.

Select the Connection tab. Enter "Host name/address", "Maintenance databalse", "Username", and "Password". If you don't want to enter the password every time, turn on "Save password". By the way, pdAdmin may not be able to resolve the name of Host, so in that case, specify the IP address directly.

Fill in the other input fields as needed. When you're done, click the "Save" button.

If you can connect to the database of the target server, the verification is complete.

Install PostgreSQL on Windows
Install the PostgreSQL client connection tool pgAdmin 4 for Windows