1

I have got the vagrant machine with Postgres on it. I need to connect with this database using some external tools (eg. pgmodeler, keetle). So i start tunnel using:

ssh -L 5433:127.0.0.1:5432 vagrant@192.168.56.140 -i puphpet/files/dot/ssh/id_rsa

Then I try to login with command:

psql -h 127.0.0.1 -p 5433 -U postgres postgres

And I get an error:

FATAL: password authentication failed for user "postgres"

I'm a little bit confused because it used to work, and now it doesn't. I've tried to set user password but it didn't work. Where should i look for a problem?

doubleB
  • 348
  • 2
  • 11
  • Why not forward ports using vagrant? https://www.vagrantup.com/docs/networking/forwarded_ports.html – zerkms Apr 13 '16 at 07:51
  • OK. So I've tried your solution but problem still exists. I think there is problem in Postgres. Maybe in pg_hba.conf file? – doubleB Apr 13 '16 at 08:14
  • It's a pg configuration issue. Show the pg_hba.conf of the vagrant machine. – Jakub Kania Apr 13 '16 at 08:14
  • Possible duplicate of [Postgresql: password authentication failed for user "postgres"](http://stackoverflow.com/questions/7695962/postgresql-password-authentication-failed-for-user-postgres) – Jakub Kania Apr 13 '16 at 08:15
  • @JakubKania Here is my pg_hba.conf file: `local all postgres ident ` `local all all ident ` `host all postgres 127.0.0.1/32 md5 ` `host all postgres 0.0.0.0/0 reject` `host all all 127.0.0.1/32 md5 ` `host all all ::1/128 md5 ` – doubleB Apr 13 '16 at 08:52
  • @JakubKania the solution in the topic above doesn't work for me. – doubleB Apr 13 '16 at 08:59

1 Answers1

1

You need to adjust the pg_hba.conf file of your postgres server to tell postgres from where connections for which users are allowed. In debian-like distributions you'll find this file in /etc/postgresql/9.1/main/pg_hba.conf.

When developing with vagrant i usually use the following entry in pg_hba.conf which allows all users to connect from everywhere without a password

# IPv4 connections from everywhere:
host    all             all             0.0.0.0/0               trust

NEVER USE THIS LINE ON A PRODUCTION SERVER

On top there is the following line in my Vagrantfile to forward the port

Vagrant.configure(2) do |config|
    # more stuff
    config.vm.network "forwarded_port", guest: 5432, host: 5433, host_ip: "127.0.0.1"
end

Do not forget to set the host_ip to localhost, otherwise postgres binds to all network interfaces of your local machine.

dahrens
  • 3,879
  • 1
  • 20
  • 38
  • OK. I found the problem. Locally I have Postgres installed too. So i needed to switch it off and it works now. But can't I have it both? – doubleB Apr 13 '16 at 10:53
  • Sure you can just change the host port in Vagrantfile to e.g. 5433 as you did it with your tunnel. – dahrens Apr 13 '16 at 11:02