How to Setup Remote Access to PostgreSQL Database from Windows

Setting up a remote connection to the PostgreSQL 13 database on the Ubuntu 18 LTS server from Windows 10 using the pgAdmin 4 utility, in two ways: connecting using an SSH tunnel and connecting directly to the PostgreSQL server.

? I recommend using an SSH tunnel, which is easy to set up and secure. When using an SSH tunnel, the PostgreSQL port does not open for external connections.

To use SSH tunnel, you need to configure SSH Certificates for login to Ubuntu.

pgAdmin is the most popular and feature rich Open Source administration and development platform for PostgreSQL, the most advanced Open Source database in the world.

PgAdmin official website

Find out the location of PostgreSQL configuration files: postgresql.conf, pg_hba.conf.

ps aux | grep postgres | grep -- -D

Find out the PostgreSQL port with the command:

grep -H '^port' /etc/postgresql/*/main/postgresql.conf

In the example, the PostgreSQL configuration file is located at /etc/postgresql/13/main/postgresql.conf, connection port 5432.

Content

1. SSH tunnel to PostgreSQL
2. Direct connection to PostgreSQL
3. FAQ

Article in other languages:
?? – Cómo configurar el acceso remoto a la base datos PostgreSQL desde Windows
?? – Как настроить удаленное подключение к БД PostgreSQL из Windows

SSH tunnel to PostgreSQL

Connect to PostgreSQL server using SSH tunnel. When choosing this type of connection, no additional settings are required on the PostgreSQL server.

Setting Up pgAdmin on Windows 10:

1. Launch pgAdmin
2. Create a new connection to the server: Object -> Create -> Server;
3. General tab:
   1. Name: server name (arbitrary);
4. Connection tab:
   1. Host Name: localhost;
   2. Port: 5432;
   3. Maintenance database: mybase;
   4. Username: postgres;
5. SSH Tunnel tab:
   1. Use SSH tunneling: Yes;
   2. Tunnel host: myserver-IP;
   3. Tunnel port: 58222;
   4. Username: root;
   5. Authentication: Identity file;
   6. Identity file: path_key.

? Specify the id_rsa private key from Configuring SSH certificates on the server as a key. How to change SSH port in FAQ.

Direct connection to PostgreSQL

To set up a direct connection to PostgreSQL, we make changes to the postgresql.conf and pg_hba.conf configuration files

PostgreSQL setup

postgresql.conf

The postgresql.conf file is located in the PostgreSQL installation folder.

sudo nano /etc/postgresql/13/main/postgresql.conf

Let’s uncomment or add the line:

listen_addresses = '*'

We have allowed requests from all IP addresses.

? Possible options:
listen_addresses = '0.0.0.0' to listen to all IPv4;
listen_addresses = '::' to listen to all IPv6;
listen_addresses = 'your.host.ip.adress' specific address or list of addresses separated by commas.

pg_hba.conf

The pg_hba.conf file is located in the PostgreSQL installation folder.

sudo nano /etc/postgresql/13/main/pg_hba.conf

Add an entry to the # IPv4 local connections section:

host     mybd     postgres     41.223.232.15/32     md5

The entry allows the user postgres with IP address 41.223.232.15 to connect to the mybd database using a password.

After the changes, restart the PostgreSQL service.

systemctl restart postgresql

pgAdmin setup

Setting up a direct connection to the PostgreSQL database via the Internet or local network using pgAdmin.

1. Start pgAdmin
2. Create a new connection to the server: Object -> Create -> Server;
3. General tab:
   1. Name: server name (arbitrary);
4. Connection tab:
   1. Host Name: RemoteServerIP;
   2. Port: 5432;
   1. Maintenance database: mybase;
   2. Username: postgres.

FAQ

---

How to Setup Remote Access to PostgreSQL Database from Windows was discussed in this article. I hope you can now configure pgAdmin to connect to PostgreSQL (direct connection or using SSH tunnel). However, if you run into any problems while configuring the server or pgAdmin, feel free to write in the comments. I will try to help.