How to allow remote connections to PostgreSQL on Linux
Tags: postgres, ubuntuserver, linux
Published: 2024-05-14 11:09:19

<div class="tdb-block-inner td-fix-index">
<h3 class="wp-block-heading" id="Prerequisites"><strong>Prerequisites</strong></h3>



<p>In this instructional guide, we will be discussing how to enable remote connections to a PostgreSQL server installed on Ubuntu. To follow along with the steps provided, you will need to have the following installed on your system:</p>



<ul>
<li>Ubuntu 20.04</li>



<li>PostgreSQL server version 12</li>
</ul>



<p>Please ensure that you have these requirements installed before proceeding with the guide.</p>



<h3 class="wp-block-heading" id="Enable-remote-access-to-Postgres"><strong>&nbsp;Enable remote access to <strong>Postgres</strong></strong></h3>



<p>To allow remote access to a PostgreSQL 12 server on Ubuntu 20.04, you need to follow the steps below:</p>



<p><strong>1. Modify the PostgreSQL configuration file</strong></p>



<p>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: <code class=" prettyprinted" style=""><span class="pln">sudo nano </span><span class="pun">/</span><span class="pln">etc</span><span class="pun">/</span><span class="pln">postgresql</span><span class="pun">/</span><span class="lit">12</span><span class="pun">/</span><span class="pln">main</span><span class="pun">/</span><span class="pln">postgresql</span><span class="pun">.</span><span class="pln">conf</span></code>&nbsp;</p>



<p>Then, find the line <code class=" prettyprinted" style=""><span class="com">#listen_addresses = 'localhost'</span></code> and uncomment it (remove the # character at the beginning of the line).</p>



<p>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.</p>



<p><strong>2. Modify the pg_hba.conf file</strong></p>



<p>Open the “pg_hba.conf” file using your preferred text editor. The file is typically located in the /etc/postgresql/&lt;version&gt;/main directory. To open the file from the Linux Terminal, execute: <code class=" prettyprinted" style=""><span class="pln">sudo nano </span><span class="pun">/</span><span class="pln">etc</span><span class="pun">/</span><span class="pln">postgresql</span><span class="pun">/</span><span class="lit">&lt;version&gt;</span><span class="pun">/</span><span class="pln">main</span><span class="pun">/</span><span class="pln">pg_hba</span><span class="pun">.</span><span class="pln">con</span></code></p>



<p>Take the following section:</p>







<p>Add new line to IPv4 local connection:&nbsp;</p>



<p><code class=" prettyprinted" style=""><span class="com"># IPv4 local connections:</span><br><span class="pln">host&nbsp;&nbsp;&nbsp; all&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; all&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span><span class="lit">0.0</span><span class="pun">.</span><span class="lit">0.0</span><span class="pun">/</span><span class="lit">0</span><span class="pln">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; md5&nbsp;</span></code></p>



<p><strong>3. Allow port 5432 through the firewall </strong></p>



<p>To enable traffic on port 5432 through the firewall, execute the following command: <code class=" prettyprinted" style=""><span class="pln">sudo ufw allow </span><span class="lit">5432</span><span class="pun">/</span><span class="pln">tcp</span></code>&nbsp;</p>



<p><strong>4. Restart PostgreSQL</strong></p>



<p>Run the following command to restart PostgreSQL: <code class=" prettyprinted" style=""><span class="pln">sudo service postgresql restart</span></code></p>



<p>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.</p>



<h2 class="wp-block-heading" id="How-to-allow-remote-connections-to-PostgreSQL-on-Windows"><strong>How to allow remote connections to PostgreSQL on Windows</strong></h2>



<p>By default, when <a href="https://blog.devart.com/download-install-postgresql-on-windows.html">installing PostgreSQL</a>, 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. Let us look at how to do this.</p>



<p>To allow remote connections to PostgreSQL on Windows, follow these steps:</p>



<p><strong>1. Edit the PostgreSQL configuration file</strong></p>



<p>1.1 Open the PostgreSQL installation directory and locate the <code class=" prettyprinted" style=""><span class="pln">postgresql</span><span class="pun">.</span><span class="pln">conf</span></code> file. By default, the configuration file for PostgreSQL (version 14) is located at C:\Program Files\PostgreSQL\&lt;version&gt;\data\postgresql.conf.</p>



<p>1.2 Make a backup of the file before making any changes. </p>



<p>1.3 Open the <code class=" prettyprinted" style=""><span class="pln">postgresql</span><span class="pun">.</span><span class="pln">conf</span></code> file in a text editor. </p>



<p>1.4 Find the line that reads <code class=" prettyprinted" style=""><span class="com">#listen_addresses = 'localhost'</span></code> and uncomment it if it is commented (remove the ‘#’ character at the beginning of the line). Next, to ensure that PostgreSQL is configured to accept connections from any address, check the value of “listen_addresses” – it should be set to “*”.</p>



<p><strong>Note:</strong>&nbsp;You can also use a specific IP address for the PostgreSQL server to listen on.</p>



<p><strong>2. Edit the pg_hba.conf file to allow remote connections</strong></p>



<p>2.1 Open the PostgreSQL installation directory and locate the <code class=" prettyprinted" style=""><span class="pln">pg_hba</span><span class="pun">.</span><span class="pln">conf</span></code> file. By default, it is located at C:\Program Files\PostgreSQL\14\data\pg_hba.conf (for PostgreSQL 14).</p>



<p>2.2 Make a backup of the file before making any changes. </p>



<p>2.3 Open the <code class=" prettyprinted" style=""><span class="pln">pg_hba</span><span class="pun">.</span><span class="pln">conf</span></code> file in a text editor. </p>



<p>2.4 Add a new line at the end of the file to allow remote connections. The line should have the following format:</p>



<p><code class=" prettyprinted" style=""><span class="com"># IPv4 local connections:</span><br><span class="pln">host&nbsp;&nbsp;&nbsp; all&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; all&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span><span class="lit">0.0</span><span class="pun">.</span><span class="lit">0.0</span><span class="pun">/</span><span class="lit">0</span><span class="pln">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; md5&nbsp;</span></code></p>



<p>This line allows connections from any IP address (0.0.0.0/0) and requires a password for authentication (md5).</p>



<p>2.5 Restart PostgreSQL (for example, from the Computer Management console).</p>



<p><strong>3. Configure the Windows Firewall to allow incoming connections to PostgreSQL</strong></p>



<p>3.1 Launch&nbsp;<strong>Windows Control Pane</strong>.</p>



<p>3.2 Open Windows Defender Firewall.</p>



<p>3.3 Click <strong>Advanced settings</strong> on the left-hand side of the window. </p>



<p>3.4 Click <strong>Inbound Rules</strong> on the left-hand side of the window. </p>



<p>3.5 Click <strong>New Rule</strong> on the right-hand side of the window. </p>



<p>3.6 Select <strong>Port </strong>as the type of rule and click <strong>Next</strong>. </p>



<p>3.7 Select <strong>TCP </strong>as the protocol and enter <em>5432 </em>as the port number. Click <strong>Next</strong>.</p>



<p>3.8 Select <strong>Allow the connection</strong> and click <strong>Next</strong>.</p>



<p>3.9 Select the network types for which the rule should apply (<em>Domain</em>, <em>Private</em>, or <em>Public</em>). Click <strong>Next</strong>. </p>



<p>3.10 Enter a name and description for the rule and click <strong>Finish</strong>.</p>



<p>After completing these steps, remote connections to PostgreSQL should be allowed on the Windows machine.</p>
</div>
[References](https://blog.devart.com/configure-postgresql-to-allow-remote-connection.html)