12

I have a VM set up with Vagrant that has Postgres running on it (on port 5432), forwarded to port 8280 on the host machine.

I have set the password for the default user and I can connect locally just fine.

I have been trying to set up access from the host machine over port 8280, and I have been unable to get it working with 'MD5' as the trust method.

I have set up postgresql.conf to listen on all addresses:

# postgresql.conf
listen_addresses = '*'

and I have configured pg_hab.conf as follows:

# pg_hab.conf
#TYPE   DATABASE  USER  CIDR-ADDRESS  METHOD
host    all       all   0.0.0.0/0     md5

With all of these settings, if I run the following command from my host machine:

psql --host=127.0.0.1 --port=8280 --username=postgres -d mydb -c '\l'

I am prompted for the password, and then I get:

psql: FATAL:  password authentication failed for user "postgres"

If I then change the METHOD from 'md5' to 'trust' I'm not asked for a password and I can connect as expected. My question is - why can't I connect using 'md5', which is what I want to be able to do? I know that the password I am entering is correct (I have changed it), but for some reason it isn't working.

qaisjp
  • 722
  • 8
  • 31
Hugo Rodger-Brown
  • 11,054
  • 11
  • 52
  • 78

2 Answers2

26

I had the same exact problem. The issue was on the host side, basically the firewall was blocking the port I was using. So this is what I did (I am using OSX Mavericks)

  1. Open the port (Host)

    sudo ipfw add 7000 allow tcp from any to any dst-port 7001

  2. Modify Vagrantfile in order to allow portforwarding

    config.vm.network "forwarded_port", guest: 5432, host: 7001

  3. Edit postgresql.conf (Guest)

    listen_addresses = '*'

  4. Edit pg_hba.conf (you might want to tune this better)

    host all all 0.0.0.0/0 md5

  5. Now, from the host connect normally using the port (in my case 7001) and 'localhost' as host address

Herman Schaaf
  • 46,821
  • 21
  • 100
  • 139
Guillermo Mansilla
  • 3,779
  • 2
  • 29
  • 34
  • Thanks! What need to be tuned in pg_hab.conf? – s7anley Mar 06 '14 at 13:01
  • Well, "host all all 0.0.0.0/0 md5" will allow connections from any host, that's why I said you might want to change it to something more restrictive. – Guillermo Mansilla Mar 06 '14 at 15:27
  • To allow only the Vagrant host you can use `host all all 10.0.2.2/32 trust` - replace `trust` by the method you prefer – conceptdeluxe Dec 02 '17 at 19:42
  • For those landing from Google; I also I found my port forward hadn't taken effect through `vagrant provision` so had to do `vagrant destroy` and `vagrant up`. – Pocketsand Dec 17 '17 at 22:07
0

You need to set a password for the postgres user. It does not have one by default, so you cannot connect.

ALTER USER postgres PASSWORD 'somepassword';

Your local connections probably work because they're using unix sockets with peer authentication, not TCP/IP. If you use:

psql -h 127.0.0.1 -U postgres postgres

on the VM, you'll probably find that that fails too, because you're actually testing TCP/IP based connections now.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • Thanks Craig, but you may not have seen in my last paragraph - I have already set the password, and I can connect to the database using it so I know it's correct. – Hugo Rodger-Brown Jan 04 '13 at 09:48
  • @HugoRodger-Brown ... in which case I'd be checking to see if I was really connecting to the DB I thought I was. Try enabling detailed logging in the guest and looking for auth errors in the logs, or using tcpdump in the guest and tracking the traffic. – Craig Ringer Jan 05 '13 at 04:56