Basic steps required for exposing a Postgres server to remote clients

[Pat France]

A newly installed Postgres server only supports local connections by default. This means that any attempts to connect from any other machine than "localhost" will be rejected by the Postgres server.

This article outlines the basic steps required for allowing remote clients to connect to a Postgres server. It is intended as an initial starting point by providing pointers into the relevant subject areas. The exact configuration depends on a number of factors, such as the respective use-case and the security requirements.

Please familiarize yourself with the related security concepts and their implications before using a Postgres server in production, or exposing it to the outside world!

Basic steps for exposing a Postgres server to remote clients​

1. Manually edit the pg_hba.conf file in your server installation. The file is usually located in your data directory and controls access for certain users (roles), machines or subnets.
   
   A setting for testing (*not* for production!) could be to add the following line to the pg_hba.conf file. It allows access for all remote users and for all connections.
   

   # TYPE  DATABASE        USER            ADDRESS                 METHOD...
   host    all             all             all                     scram-sha-256

2. Make sure your Postgres server is configured to listen on all addresses. This is configured in the postgres.conf file which also resides in the data directory.
   

   listen_addresses = '*'

3. Restart your Postgres server

You may also have to configure your machine's firewall to allow the incoming connections. Postgres uses port 5432 by default, which must be allowed through the firewall for TCP and UDP.

Once these steps have been completed, client applications should be able to connect to the Postgres server by specifying the machine's name or IP address in the connection string:

   cServer := ".. specify machine name or IP address .."
   cDB     := ".. specify Postgres database to connect to .."
   cUser   := ".. specify Postgres user .."
   cPwd    := ".. specify password .."

   cConnStr := "DBE=pgdbe;server=" + cServer + ";"
   cConnStr += "db=" + cDB + ";uid=" + cUser + ";"
   cConnStr += "pwd=" + cPwd"

   oSession := DacSession():New(cConnStr)
   ...

References:

• The pg_hba.conf file
• [Blog post] How to Configure PostgreSQL for Remote Connections: A Beginner’s Guide