138

I have PostgreSQL 9.2 Installed in Windows 7 and I have windows XP installed in Virtual Machine, how do I connect these two databases and allow remote access to add/edit the database from both Systems ?

Improve this question
1
  • Note that all answers enable access to all PostgreSQL databases on the server (in your case running on Win 7). Makes sense for exposing the server to a VM like here. In a more general case of course, one would restrict remote access to specific databases and users for security reasons. Commented Oct 26, 2021 at 0:13

7 Answers 7

Reset to default
177

Security Notice: making a DB available on any publicly visible IP is a security risk. Even in a LAN a DB with an open port can not be considered a "secure data storage"

In order to remotely access a PostgreSQL database, you must set the two main PostgreSQL configuration files:

  • postgresql.conf
  • pg_hba.conf

Here is a brief description about how you can set them (note that the following description is purely indicative: To configure a machine safely, you must be familiar with all the parameters and their meanings)

First of all configure PostgreSQL service to listen on port 5432 on all network interfaces.

In Windows 7 machine open the postgresql.conf file (usually located in C:\Program Files\PostgreSQL\9.2\data) and set the parameter:

listen_addresses = '*'

Check the network address of WindowsXP virtual machine and set parameters in the pg_hba.conf file (located in the same directory of the postgresql.conf file) so that PostgreSQL can accept connections from virtual machine hosts.

For example, if the machine with Windows XP have 192.168.56.2 IP address, add in the pg_hba.conf file:

host all all 192.168.56.1/24 md5

In this way PostgreSQL will accept connections from all hosts on the network 192.168.1.XXX.

Restart the PostgreSQL service in Windows 7 (Services-> PosgreSQL 9.2: right click and restart sevice). Install pgAdmin on windows XP machine and try to connect to PostgreSQL.

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

Thanks a lot, unfortunately it didn't work, all the changes I did were in the windows 7 side and when I restarted the PostgreSQL service it wouldn't start again until I remove the Virtual Machine IP address from pg_hba.conf, should I add anything in the virtual machines files ?
Have you checked Windows 7 firewall? Can you ping Windows7 from WindowsXP? You can also try with netstat -a -n command to check if postgresql is listening on the addresses
Nvm dude, it worked, it was an IP address problem, now it's working like a charm :)
@AlikElzin-kilaka you can use the slash notation to indicate the subnet. If you need to include all addresses starting with 192.168.*.* you can specify 192.168.0.0/16 in the pg_hba.conf configuration file.
i've added a maybe unrelated though pretty important paragraph to the answer - about the security aspect of non private/non local ports of the DB
|
102

After set listen_addresses = '*' in the postgresql.conf file

Edit the pg_hba.conf file and add the following entry at the very end of file:

host    all             all              0.0.0.0/0                       md5
host    all             all              ::/0                            md5

For finding the config files the following post might help you:

Improve this answer

10 Comments

yes it works. finally i could access postgresql using pgadmin from my local network. thank you.
conf file location: psql -U postgres -c 'SHOW config_file'. Default in Ubuntu: /etc/postgresql/12/main/postgresql.conf. Also, service should be restarted: sudo service postgresql restart - on ubuntu.
dont forget sudo systemctl restart postgresql
Mine worked. But I had to use "trust" instead of "md5". Also, the second line is needed only for ipv6 connections.
Do not use trust in combination with all all 0.0.0.0/0 - that makes your server practically open to everyone. As mentioned in PostgreSQL documentation: "This method should only be used when there is adequate operating-system-level protection on connections to the server."
|
45

In addition to above answers suggesting (1) the modification of the configuration files pg_hba.conf and (2) postgresql.conf and (3) restarting the PostgreSQL service, some Windows computers might also require incoming TCP traffic to be allowed on the port (usually 5432).

To do this, you would need to open Windows Firewall and add an inbound rule for the port (e.g. 5432).

Head to Control Panel\System and Security\Windows Defender Firewall > Advanced Settings > Actions (right tab) > Inbound Rules > New Rule… > Port > Specific local ports and type in the port your using, usually 5432 > (defaults settings for the rest and type any name you'd like)

Windows firewall settings

Now, try connecting again from pgAdmin on the client computer. Restarting the service is not required.

Improve this answer

2 Comments

This worked for a Windows 7 guest OS running on a Windows 10 host on VirtualBox.
That's it ! and don't forget to make a Routing rule into your router !
22

If using PostgreSql 9.5.1, please follow the below configuration:

  1. Open hg_hba.conf in pgAdmin pgAdmin
  2. Select your path, and open it, then add a setting pg_hba.conf
  3. Restart postgresql service

In order to allow 192.X.X.X use 192.0.0.0/8.

In order to allow 192.168.X.X use 192.168.0.0/16.

In order to allow 192.168.1.X use 192.168.1.0/24.

In order to allow only 192.168.1.2 use 192.168.1.2/32

Improve this answer

3 Comments

so your network ip address is 0.0.0.0/0 ? or is that just accept all ip address?
@ziggy : All IP addresses. In order to allow X.X.X.X use 0.0.0.0/0. In order to allow 192.X.X.X use 192.0.0.0/8. In order to allow 192.168.X.X use 192.168.0.0/8. In order to allow 192.168.1.X use 192.0.0.0/16. In order to allow 192.168.1.X use 192.168.1.0/24. In order to allow only 192.168.1.2 use 192.168.1.2/32
Respect, this helped
2

This is a complementary answer for the specific case of using AWS cloud computing (either EC2 or RDS machines).

Besides doing everything proposed above, when using AWS cloud computing you will need to set your inbound rules in a way that let you access to the ports.

Please check this answer about 'inbound rules'.

Improve this answer

Comments

1

You have to add this to your pg_hba.conf and restart your PostgreSQL.

host all all 192.168.56.1/24 md5

This works with VirtualBox and host-only adapter enabled. If you don't use Virtualbox you have to replace the IP address.

Improve this answer

1 Comment

Thanks but I'm using Microsoft Virtual PC, how do I know which IP address to add ?
-3

For PostgreSQL 13, I could not use scram-sha-256 encryption for remote connections for some reason. This worked.

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     scram-sha-256 # "local" is for Unix domain socket connections only
host    all             all             127.0.0.1/32            scram-sha-256 # IPv4 local connections:
host    all             all             ::1/128                 scram-sha-256 # IPv6 local connections
local   replication     all                                     scram-sha-256 # Allow replication connections from localhost, by a user with the replication privilege.
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256
host    all             all             0.0.0.0/0               trust # <---------- remote connections
Improve this answer

2 Comments

Please DO NOT do this, this leaves your database access open to all without password
do not do host all all 0.0.0.0/0 trust # instead host all all 0.0.0.0/0 scram-sha-256 # is better

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.