Need to connect to PostgreSQL from another computer? This beginner-friendly guide walks you through how to configure PostgreSQL remote connections and enable secure remote access to your PostgreSQL server on both Linux and Windows systems. You’ll learn how to edit configuration files, open the right ports, and test your PostgreSQL remote access safely using dbForge Studio for PostgreSQL, an advanced GUI tool that simplifies remote database management.
PostgreSQL’s design makes this process straightforward. Built for flexibility and reliability, it gives you full control over how connections are established, whether on a local machine or across networks.
- Introduction to remote database connections
- How to allow remote connections to PostgreSQL on Windows
- How to configure PostgreSQL remote access in docker containers
- Connect to a remote PostgreSQL server from dbForge Studio for PostgreSQL
- Best practices for secure remote PostgreSQL access
- Advantages of using dbForge Studio for PostgreSQL
- FAQ
Introduction to remote database connections
PostgreSQL restricts access to local connections by default, preventing remote clients from connecting. This configuration improves security but also limits access to users working on the same machine as the database.
However, remote access is often required for collaboration, cloud deployments, and administrative tasks. Learning how to enable it securely lets teams connect from any machine or network while keeping sensitive data protected.
When PostgreSQL allows remote access, you enable external clients to connect to the database through standard communication protocols such as TCP/IP, SSH, or VPN. These methods make it possible to enable access externally while maintaining encryption and authentication safeguards.
Every connection relies on a port, the communication channel between your client and server. PostgreSQL listens on port 5432 by default, and opening this port correctly is essential to establish a connection. Be sure your firewall allows incoming traffic on this port, but only from trusted sources, to reduce security risks.
In a nutshell, PostgreSQL local connections setup keeps your system safe, but extending it securely to remote users is key to modern database administration.
Securing remote connections to PostgreSQL
Remote access to PostgreSQL lets users connect from external machines, but it also opens the server to potential threats. When remote access is enabled, unauthorized users could attempt to exploit open ports or weak authentication methods.
To ensure secure PostgreSQL connections, it’s critical to configure authentication rules in pg_hba.conf and apply strict IP access control. Only trusted addresses or networks should be allowed to connect to your database.
Here are several best practices to protect your PostgreSQL server when enabling remote access:
- Use SSL/TLS encryption: Encrypt traffic between clients and the server to prevent interception of sensitive data.
- Configure IP whitelisting: Limit access to specific IP addresses or subnets in pg_hba.conf or your firewall.
- Set up a VPN or SSH tunnel: Establish a secure communication channel between client and server without exposing PostgreSQL directly to the public internet.
- Use strong authentication: Apply secure password policies or switch to scram-sha-256 for more robust user verification.
- Restrict superuser access: Grant elevated privileges only when absolutely necessary and monitor login activity regularly.
Securing remote access doesn’t just protect data: it safeguards uptime, performance, and compliance. A well-configured authentication and IP control strategy ensures your PostgreSQL instance remains both accessible and protected.
Prerequisites
To access your PostgreSQL server remotely, you need to update a few key configuration files that control how clients connect to the database. Before starting, locate the following files.
| File | Purpose | Common path (Ubuntu) | Notes |
|---|---|---|---|
| postgresql.conf | Controls network and IP access settings. Defines which IP addresses PostgreSQL should listen to using the listen_addresses parameter. | /etc/postgresql/<version>/main/postgresql.conf | Adjust this file to enable PostgreSQL to listen for remote connections. |
| pg_hba.conf | Manages authentication methods and client permissions. Determines which users and hosts can connect and what authentication method (e.g., md5, scram-sha-256) is required. | /etc/postgresql/<version>/main/pg_hba.conf | Edit this file to configure trusted hosts and authentication rules. |
Before you proceed, make sure you have:
- Administrative rights to edit PostgreSQL configuration files.
- Access to the server’s firewall settings to open port 5432.
- A basic text editor like Nano, Vim, or Notepad++ to modify these files safely.
Confirming you have these prerequisites for your PostgreSQL setup will help avoid connection errors and ensure a smooth configuration process later on.
Enable remote access to PostgreSQL
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/12/main directory. To open the file from the Linux Terminal, execute:
sudo nano /etc/postgresql/12/main/pg_hba.conf Take the following section.
# IPv4 local connections:
host all all 127.0.0.1/32 md5 Modify it in this way.
# 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.
To allow remote connections to PostgreSQL on Windows, locate and open the postgresql.conf file.
By default, it is located at: C:\Program Files\PostgreSQL\14\data\postgresql.conf.
Before making any edits, create a backup of the file. Then, open it in a text editor such as Notepad++ with administrator rights. Inside the file, locate the following line.
#listen_addresses = 'localhost' Replace it with:
listen_addresses = '*' This step ensures correct configuration of postgresql.conf listen_addresses, which allows PostgreSQL to listen for connections from any IP address. You can also specify a particular address or subnet for more control.
Next, edit pg_hba.conf file located in the same directory: C:\Program Files\PostgreSQL\14\data\pg_hba.conf
Before editing, make a backup of the file. Then add the following entry at the end.
host all all 0.0.0.0/0 md5 This line allows connections from any IP address using MD5 password authentication. For higher security, replace 0.0.0.0/0 with a specific IP range.
Restart PostgreSQL
After making these changes, restart PostgreSQL Windows to apply the new configuration.
Using PowerShell: Run the following command.
Restart-Service postgresql-x64-14 Using Windows Services:
- Press Win + R, type services.msc, and press Enter.
- Locate postgresql-x64-14 (or your installed version).
- Right-click the service and select Restart.
Configure the Windows firewall
To allow external connections through the firewall, follow these steps:
- Open Control Panel > Windows Defender Firewall.
- Select Advanced settings > Inbound Rules.
- Click New Rule > choose Port > Next.
- Select TCP, and enter 5432 as the port number > Next.
- Choose Allow the connection > Next.
- Select applicable network types (Domain, Private, or Public) > Next.
- Enter a rule name (e.g., PostgreSQL Remote Access) > Finish.
Once done, you can accept secure PostgreSQL remote access windows connections.
How to configure PostgreSQL remote access in docker containers
Running PostgreSQL in Docker makes deployment and testing easier, but containers are isolated by default. This means no remote connections can be made unless you expose the correct ports and adjust authentication settings. Here’s how to set it up properly.
Expose port 5432
PostgreSQL listens on port 5432 by default. To allow remote connections, publish this port from the container to the host machine using the -p flag. Run the following command when starting your container.
-p 5432:5432 This binds the container’s internal port 5432 to the same port on the host, enabling remote clients and tools like dbForge Studio for PostgreSQL to connect externally.
Persist configuration with volume mapping
If you want your PostgreSQL settings to persist after container restarts, mount a local folder that contains your configuration files.
-v /path/to/local/data:/var/lib/postgresql/data This ensures files such as postgresql.conf and pg_hba.conf remain accessible and editable directly from the host. You can update parameters like listen_addresses or modify authentication rules without rebuilding the image.
Run PostgreSQL with trusted authentication To enable quick access for testing, use the POSTGRES_HOST_AUTH_METHOD=trust environment variable when starting the container:
docker run -d \
--name my_postgres \
-e POSTGRES_PASSWORD=mysecretpassword \
-e POSTGRES_HOST_AUTH_METHOD=trust \
-p 5432:5432 \
-v /path/to/local/data:/var/lib/postgresql/data \
postgres This setup starts PostgreSQL with port 5432 open, configuration persistence enabled, and trust-based authentication for easy connectivity.
Note: Use trust authentication only in non-production or isolated environments. For production, replace it with stronger methods such as md5 or scram-sha-256, and restrict access to trusted IP addresses.
Connect to a remote PostgreSQL server from dbForge Studio for PostgreSQL
Whether working with an existing PostgreSQL deployment or have recently decided to migrate MySQL to PostgreSQL, connecting remotely is a key step in enabling distributed database development and administration.
Let us look at how you can connect to the PostgreSQL server and then manage connections in one of the best PostgreSQL clients – dbForge Studio for PostgreSQL. Here is what to do:
- On the Database menu, select New Connection.
- On the Database Connection Properties > General tab, specify the connection details:
- Host: Provide the host name
- Port: Provide the port number
- User and Password: Enter respective user credentials. By default, the password is saved automatically. If you don’t want to save the password, clear the Allow saving password checkbox.
- Database: Enter the name of a PostgreSQL database you want to connect to or select it from the drop-down list.
- Optional: Connection Name: The connection name is generated automatically from the host name. If you want to create a distinctive name for the connection, type the new name.
Click Test Connection to verify the connection details you have provided. Click Connect to connect to a PostgreSQL server.
Best practices for secure remote PostgreSQL access
Enabling remote connections increases flexibility, but it also introduces security risks if not configured properly. Follow these best practices to protect your PostgreSQL server when enabling remote access:
- Use strong authentication: Prefer
md5orscram-sha-256instead oftrustin yourpg_hba.conffile. These methods ensure password-based authentication and prevent unauthorized logins. - Restrict IP access: Instead of using
0.0.0.0/0, which allows connections from anywhere, restrict access to known IP addresses or networks. This minimizes the exposure of your database to potential attacks. - Avoid direct internet exposure: Use an SSH tunnel or a VPN to create a secure communication channel between clients and the PostgreSQL server. This adds an extra layer of encryption and prevents direct network access to the database.
- Keep PostgreSQL updated: Security patches are released frequently. Keeping your PostgreSQL installation and system packages up to date helps prevent exploits targeting older versions.
- Use firewalls: Configure both the server and network firewalls to allow incoming connections only from trusted IPs and block all others.
- Review logs often: Regularly review PostgreSQL logs for failed login attempts or suspicious activity. Monitoring tools can help automate alerts for potential intrusions.
Following these practices helps ensure that your PostgreSQL remote access setup remains both functional and secure.
Advantages of using dbForge Studio for PostgreSQL
dbForge Studio is a powerful and feature-rich PostgreSQL GUI client for database development and administration. Here are some of the advantages of using it in daily work:
User-friendly interface: dbForge Studio for PostgreSQL has a clean interface that is easy to use, even for those who are new to PostgreSQL database management.
Advanced PostgreSQL functionality: The Studio provides a wide range of advanced features, including code completion, database management, PostgreSQL data export and import, SQL editing, debugging, and profiling.
Efficient data and schema compare and sync: The IDE allows its users to easily compare PostgreSQL databases, get comprehensive information on all differences, and generate clear and accurate SQL synchronization scripts to deploy changes.
Easy database management: With dbForge Studio for PostgreSQL, you can easily create, edit, and delete tables, views, procedures, and other database objects.
Advanced SQL editing: The tool boasts a mighty PostgreSQL editor with advanced features, including code highlighting, code formatting, and auto-completion, which help to improve the efficiency and quality of your work.
Instant test data generation: dbForge Studio incorporates a powerful Data Generator allowing users to create realistic test data in just a few clicks.
Efficient query execution: dbForge Studio for PostgreSQL helps optimize the execution of queries by providing detailed information about query execution plans.
Customizable code snippets: The IDE provides a library of customizable code snippets for frequently used SQL commands, which can help reduce the time and effort required to write code.
Overall, dbForge Studio for PostgreSQL is a powerful and versatile tool that provides many benefits that make it a popular choice for database developers.
To see for yourself how dbForge Studio for PostgreSQL can help you manage your databases more efficiently, we invite you to download and evaluate our free trial. Simply download free trial of dbForge Studio for PostgreSQL.
With the free trial, you’ll have full access to all the features of the Studio for PostgreSQL for 30 days, so you can try out all the functionality and see how it fits into your workflow. We’re confident that you’ll love the product, and we look forward to hearing your feedback.
FAQ
How do I allow only one IP to access my PostgreSQL database?
To let PostgreSQL allow IP access, open the pg_hba.conf file and specify a single allowed IP address.
Example
host all all 192.168.1.10/32 md5 This restricts database connections so that only that IP can connect securely.
What’s the difference between host and hostssl in pg_hba.conf?
The difference between hostssl vs host lies in how PostgreSQL handles client connections. host allows both encrypted and unencrypted TCP/IP connections, while hostssl permits only SSL-encrypted connections for enhanced security.
How can I secure PostgreSQL remote access using SSL?
For PostgreSQL remote access SSL configuration, enable SSL in your postgresql.conf by setting ssl = on and specify the paths to your certificate and key files. This encrypts data between the client and server, protecting credentials and sensitive information.
Which firewall rules are needed to connect to PostgreSQL remotely on Windows?
To configure the Postgresql remote access windows firewall, open port 5432 for inbound TCP connections in Windows Defender Firewall. Always restrict the rule to trusted IPs to prevent unauthorized access.
How do I check if PostgreSQL is listening on port 5432?
You can verify PostgreSQL TCP port activity using this command in the terminal or Command Prompt:
netstat -an | find "5432" If PostgreSQL is active, you’ll see the port listed under open connections.
Why does my PostgreSQL remote connection say “connection refused”?
A PostgreSQL connection refused error usually means PostgreSQL isn’t listening on the correct interface or the firewall is blocking access. Check listen_addresses in postgresql.conf and confirm that port 5432 is open.
Can I use Docker to expose PostgreSQL for remote access?
Yes. To set up the PostgreSQL remote access docker, run your PostgreSQL container with the -p 5432:5432 flag to expose the port, and configure authentication using POSTGRES_HOST_AUTH_METHOD=md5 for production or trust for testing.
How do I test a remote PostgreSQL connection from the command line?
To test a PostgreSQL remote connection, use the psql tool as follows:
psql -h your_server_ip -U username -d database_name If credentials and access permissions are set correctly, the connection will succeed.
What are the best practices for enabling PostgreSQL remote access securely?
Follow PostgreSQL remote access best practices by using md5 or scram-sha-256 authentication, limiting IP ranges, and avoiding direct internet exposure. Use a VPN or SSH tunnel for an extra security layer.
Which file controls who can connect to PostgreSQL from a remote machine?
To manage remote access, edit pg_hba.conf, this file defines which users and IP addresses can connect and which authentication methods they must use.
