CODEWITHSUNDEEP
ruby-on-rails-3postgresql
Max Williams
Max Williams

Reputation: 32955

Can't use postgres user in new database for rails 3 on ubuntu 10.04 server

I'm trying to get a rails 3 app up and running on an ubuntu 10.04. It's my first postgres project and i'm struggling to get postgres working properly. It's all installed, and i can get into it with psql.

I followed the instructions here to set the password for the postgres user called 'postgres': 

#on the command line
sudo -u postgres psql postgres

#in postgres
\password postgres
\quit

I thought that this would set the password for the postgres user to 'postgres'. But, now i can't log in as the postgres user.

My db config looks like this:

development:
  adapter: postgresql
  database: chronicle_development
  username: postgres
  password: postgres
  pool: 5
  timeout: 5000

test:
  adapter: postgresql
  database: chronicle_test
  username: postgres
  password: postgres
  pool: 5
  timeout: 5000

production:
  adapter: postgresql
  database: chronicle_production
  username: postgres
  password: postgres
  pool: 5
  timeout: 5000

I thought this would be fine - i've changed (postgres user) postgres's password to 'postgres' and this is in my config. But, when i do rake db:migrate i get

rake aborted!
FATAL:  Ident authentication failed for user "postgres"

Ok...maybe you can't specify the password in the db config like that? So, i made a ~/.pgpass file which has this:

*:*:*:postgres:postgres

Still get the same error message.

I've made another user called 'max' who can get in ok, but the config file (with postgres) works for the other guys on the project so i need to get mine working with that config file. Is it possible to remove the password from the postgres account? Or fix this some other way? thanks, max

EDIT - just tried something else, which didn't work unfortunately. I read on this page that renaming a role clears its password, so gave it a go.

#in psql
=# alter role postgres rename to postgres2;
NOTICE:  MD5 password cleared because of role rename

This sounded encouraging! So, i changed it back to postgres again. But still no luck. It also didn't work when i left the user as postgres2 and changed my config to have that username - i got FATAL: Ident authentication failed for user "postgres2". Why does it work for max but not for postgres/postgres2? Is it to do with users in my ubuntu env? my username in linux is also 'max'.

Upvotes: 1

Views: 2978

Answers (1)

oky_sabeni
oky_sabeni

Reputation: 7822

Looks like you need to change your your pg_hba.conf file. In my ubuntu 11.10 machine, it is in /etc/postgresql/9.1/main/pg_hba.conf

In it, towards the bottom, you need to find and replace the ident to md5. It will look something like this:

local   all             all                                     md5

I got stuck because for me (postgresql 9.1 and using Rails 3.2.2), the default for that is peer which is not right. Check out what peer and ident authentication mean here.

That is for postgresql 9.1 so you may have to search for your version.

Good luck!

Upvotes: 1

Related Questions