8

first things first, please excuse my utter noobness. I really tried to find a solution out there, but now i'm stuck and completely clueless.

i'm trying to deploy a rails 3 app on a distant server ; when developping on my local VM, no problem showed. But now, when i try to run 

rake db:create

it fails, with error (here translated, since i'm french):

FATAL : password authentication failed for user <<mylogin>>

here's my database.yml :

login: &login
  adapter: postgresql
  username: mylogin
  password: mypassword
  host: localhost
  port: 5432
  encoding: UTF8

development:
  <<: *login
  database: somesite_development

test:
  <<: *login
  database: somesite_test

production:
  <<: *login
  database: somesite_production

the user "mylogin" has been created postgre-side with the command-line tool "createuser". It's authorized to create dbs. postgresql.conf configures the server to listen on localhost. I've tried many things with pg_hba.conf, none worked - whatever the method used (ident, password, md5) for user "mylogin" on 127.0.0.1, authentication fails - though i've never had problems connecting / creating dbs with psql.

any clue ?

EDIT: okay, found out how incredibly stupid i've been... the password for my user was simply not set ! I think i forgot the semicolon after

ALTER USER xxxx WITH PASSWORD xxxx ;

... i saw this by requesting "SELECT * FROM pg_shadow;" - the password field was empty. Three days of my life wasted because of this dumb mistake...

John Topley
  • 113,588
  • 46
  • 195
  • 237
m_x
  • 12,357
  • 7
  • 46
  • 60

6 Answers6

20

I was also stuck on this problem for a really long time, and went to a variety of links (including the ones offered in this post) to try and find the answer, but to no avail. However, the solution is very simple. While many of the other responses were on the right track here are the exact steps to solve the problem:

  1. Open your pg_hba.conf file in a text editor of your choice. (It is located in /etc/postgresql//main)

  2. Navigate to the line that reads:

    # "local" is for Unix domain socket connections only

and paste below it:

  local all         all                 trust

This will trust all unix users trying to connect to the psql server on the local machine. (Read documentation at top of page for further info about function of columns)

  1. Save the pg_hba.conf file and exit the text editor.

  2. Restart the Postgresql server by running the command:

    service postgresql restart

  3. Now try and start a psql server by running:

    psql -d -U (or "psql " for short)

  4. You should be able log in with no problem.

*Note: All this assumes that you have have a valid psql username for logging in. If you don't follow the links below to set one up:

Setting up a user: http://erikonrails.snowedin.net/?p=274

Making sure you have a valid postgres user: http://archives.postgresql.org/pgsql-novice/2002-08/msg00072.php

Listing all existing psql users: If you are looking for a "LIST USERS" or "DISPLAY USERS" command then try:

"select * from pg_user;" (when logged in to psql)

Good luck!

Vidal Ekechukwu
  • 219
  • 2
  • 4
  • 4
    Thanks, but this question has already an accepted answer... (the problem was just me being dumb >.<) Moreover, i don't think that the "hey, let's remove all security and let all local users in" solution is quite a good idea, especially when you share a server with many other users from different companies. – m_x Feb 13 '12 at 08:53
  • @m_x is right. It's kind of like bird hunting with a bazooka. Sure you fix the issue but it opens you up for other concerns. – JaeGeeTee Nov 06 '15 at 03:18
4

okay, found out how incredibly stupid i've been... the password for my user was simply not set ! I think i forgot the semicolon after

ALTER USER xxxx WITH PASSWORD xxxx ;

... i saw this by requesting "SELECT * FROM pg_shadow;" - the password field was empty. Three days of my life wasted because of this dumb mistake...

m_x
  • 12,357
  • 7
  • 46
  • 60
2

I had same problem. In my case it was because in my database.yml file the username started with capital letter but in database it was all lower case.

Solution: When creating user from postgres command line, it converts all letters to lowercase. For using capital letters, double quotes must be used.

Example:

create user AlbertEinstein; result = alberteinstein

vs

create user "AlbertEinstein"; result = AlbertEinstein

Andres
  • 2,099
  • 3
  • 22
  • 39
0

In my case, I found that a later host permissions rule line in the pg_hba.conf file had over-ridden the earlier local line. I was correctly configuring the local line to use md5 authentication but the host line was set to ident which I changed to md5

As other have emphasized here, note that using trust is an insecure method, so you shouldn't use it in any production-style deployment.

sameers
  • 4,855
  • 3
  • 35
  • 44
0

Here are some concise instructions that should work for you

http://www.cyberciti.biz/faq/psql-fatal-ident-authentication-failed-for-user/.

Basically you need to set the authentication method for localhost to 'trust'.

JosephL
  • 5,952
  • 1
  • 28
  • 25
  • Thanks for the reply, but as i said : "I've tried many things with pg_hba.conf, none worked - whatever the method used (ident, password, md5)" Moreover, i don't believe my server admin would be glad if i allow this user to connect by trust method... But hey, thanks for the hint, i'll try and if i still cannot connect, this means something's wrong somewhere else. – m_x May 02 '11 at 08:11
0

You need to give "myuser" in postgresql the privilege "Can create database objects".

In pgadmin, it looks like this:

enter image description here

After calling rake db:create, you can take away this privilege.

plang
  • 5,446
  • 3
  • 25
  • 36
  • Thanks, but as stated at the end of my question (EDIT), my problem was due to pure dumbness. I might add that later i tripped on another bug: with ruby >1.9.2 , it seems that the default YAML parser is currently broken and does not support the merge ("<<:") keys. – m_x May 27 '11 at 15:42