Homestead PostgreSQL cant connect out the box from remote/host machine

[JFossey]

Problem

On a new setup of Homestead you get an error connecting remotely to VM via GUi Tools like pgAdmin3 and the Latest HeidiSQL (experimental).

Connecting VIA localhost in a Shell works.

Error:

homestead@postgres FATAL: no pg_hba.conf entry for host "192.168.10.1", user "homestead", database "postgres", SSL on
homestead@postgres FATAL: no pg_hba.conf entry for host "192.168.10.1", user "homestead", database "postgres", SSL off

Workaround:

Add the following to after.sh, based on code found in settler.

if [ ! -f ~/.homestead_post_install_customisations ]; then
    # Add one time setup customisation    
    echo "installing setup customisations"

    echo "host    all             all             0.0.0.0/0                md5" | tee -a /etc/postgresql/9.4/main/pg_hba.conf
    sudo /etc/init.d/postgresql reload
    # service postgresql restart

    touch ~/.homestead_post_install_customisations
else    
    # Add setup constomisation that should run with each reload/provision
    echo "installing reload/provisioning customisations"    
fi

Suggested Fix

Add "host all all 0.0.0.0/0 md5" to pg_hba.conf out the box on new setups providing remote access to PostgreSQL databases.

[GrahamCampbell]

We're open to PRs. ;)

[andrey-helldar]

In vagrant 1.9.1 and homestead 4.0.3 (1.0.1 box version):

vagrant@homestead:/etc/postgresql/9.5/main$ sudo echo "host all all 0.0.0.0/0 md5" | tee -a /etc/postgresql/9.5/main/pg_hba.conf
tee: /etc/postgresql/9.5/main/pg_hba.conf: Permission denied
host all all 0.0.0.0/0 md5

PS: Connect to MySQL: OK

[andrey-helldar]

Connected!

My params:
-- Properties --
Host: 127.0.0.1
Port: 5432
Username: homestead
Password: secret

-- SSH Tunnel --
Tunnel Host: 192.168.10.10
Tunnel Port: 22
Username: vagrant
Password: vagrant

[mikebronner]

This worked for me:

1. sudo nano /etc/postgresql/9.5/main/pg_hba.conf.
2. Update host line to read:

   host  all  all 0.0.0.0/0 md5
   

3. Restart PostgreSql: sudo service postgresql restart.

Source: http://dba.stackexchange.com/questions/83984/connect-to-postgresql-server-fatal-no-pg-hba-conf-entry-for-host

[lukeholder]

I have been getting this error too. I am latest vagrant and homestead, fresh install and CAN connect to mysql from my local machine (through auto the port forward), but can't connect to posgres - due to:

FATAL:  no pg_hba.conf entry for host "192.168.23.2", user "homestead", database "postgres", SSL on
FATAL:  no pg_hba.conf entry for host "192.168.23.2", user "homestead", database "postgres", SSL off

Should we need to add something to after.sh to get this to work?

Should we need to edit the after.sh directly to get this to work?

[mikebronner]

@lukeholder This might be a good issue to raise in Settler or Homestead repos, as the change would have to be made there.

[browner12]

@lukeholder if you're having issues visit us over in the laravel/homestead directory or in the Slack homestead-dev channel

[lukeholder]

Thanks @mikebronner and @browner12 didn't realize I wasn't in the right repo.

[dqfan2012]

I configured my homestead to run at 192.168.5.10 (because it's within my VMWare virtual network setup). I got my remote connection to postgresql to work as follows:

In the pg_hba.conf file, I added a line for the gateway because you'll be connecting to postgresql through the gateway. For me that looked like this:

host    all             all             192.168.5.1/32          trust

I dislike the solution above using 0.0.0.0/0. I think configuring the proper IP address and telling postgresql to trust connections from that IP is better.