Topic: Rails app thinks postgres password is wrong

Hi everyone. I tried searching the forum (and google, for that matter) but found no results for my problem.

Short version: We've got a rails application that will, every once in awhile, for no apparent reason, start getting "password authentication failed" errors on the postgres database - despite config/database.yml having the right username/password. Sometimes, if we just go into the postgres server, and force reset the user's password to the original value (nevermind that the hash in the roles table is unchanged - e.g. the password is still correct), this will fix it. This time, however, this hasn't fixed it, and the app is refusing to start.

Long version: We've got a web server setup thusly....

[root@webserver current]# cat /etc/redhat-release
CentOS release 5.5 (Final)
[root@webserver current]# uname -a
Linux webserver 2.6.18-53.1.4.el5.028stab053.4 #1 SMP Fri Jan 18 12:29:44 MSK 2008 i686 athlon i386 GNU/Linux
[root@webserver current]# ruby --version
ruby 1.8.7 (2008-08-11 patchlevel 72) [i686-linux]
[root@webserver current]# gem --version
1.3.7
[root@webserver current]# mongrel_rails --version
Mongrel Web Server 1.1.5

We've also got a postgresql server, configured thusly:

[root@dbserver ~]# psql --version
psql (PostgreSQL) 8.1.11
contains support for command-line editing
[root@dbserver ~]# cat /etc/redhat-release
CentOS release 5 (Final)
[root@dbserver ~]# uname -a
Linux dbserver 2.6.18-53.1.4.el5.028stab053.4 #1 SMP Fri Jan 18 12:29:44 MSK 2008 i686 athlon i386 GNU/Linux

... So we've got an application we run via mongrel on this webserver. The application normally runs correctly with no problems, including connecting to the database. Here's the config/database.yml from the webserver's application (please note here that "dbserver" is a placeholder, and the actual file contains valid IP addresses; the username/password fields also contain placeholders, the actual file is correct):

development:
  adapter: postgresql
  host: dbserver
  port: 5432
  username: USERNAME
  password: PASSWORD
  database: DBNAME
  schema_search_path: public
  encoding: utf8
  pool: 5
  template: template0

production:
  adapter: postgresql
  host: dbserver
  port: 5432
  username: USERNAME
  password: PASSWORD
  database: DBNAME
  schema_search_path: public
  encoding: utf8
  pool: 5
  template: template0

So the application normally works just fine. It will start up and run with no problems. But sometimes, it will (randomly, it seems) give us a "We're sorry, something went wrong" error, and the logs will show the following:

/!\ FAILSAFE /!\  Thu Oct 28 14:17:23 -0400 2010
  Status: 500 Internal Server Error
  FATAL:  password authentication failed for user "USERNAME"

    /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/postgresql_adapter.rb:968:in `initialize
'
    /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/postgresql_adapter.rb:968:in `connect'
    /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/postgresql_adapter.rb:968:in `connect'
    /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/postgresql_adapter.rb:217:in `initialize
'
    /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/postgresql_adapter.rb:37:in `new'
    /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/postgresql_adapter.rb:37:in `postgresql_
connection'
    /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract/connection_pool.rb:223:in `send
'
    /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract/connection_pool.rb:223:in `new_
connection'
    /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract/connection_pool.rb:245:in `chec
kout_new_connection'
    /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract/connection_pool.rb:188:in `checkout'
    /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract/connection_pool.rb:184:in `loop'
    /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract/connection_pool.rb:184:in `checkout'
    /usr/local/lib/ruby/1.8/monitor.rb:242:in `synchronize'
    /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract/connection_pool.rb:183:in `checkout'
    /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract/connection_pool.rb:98:in `connection'
    /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract/connection_pool.rb:326:in `retrieve_connection'
    /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract/connection_specification.rb:123:in `retrieve_connection'
    /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract/connection_specification.rb:115:in `connection'
    /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/query_cache.rb:9:in `cache'
    /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/query_cache.rb:28:in `call'
    /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract/connection_pool.rb:361:in `call'
    /usr/local/lib/ruby/gems/1.8/gems/actionpack-2.3.5/lib/action_controller/failsafe.rb:26:in `call'
    /usr/local/lib/ruby/gems/1.8/gems/rack-1.0.1/lib/rack/lock.rb:11:in `call'
    /usr/local/lib/ruby/gems/1.8/gems/rack-1.0.1/lib/rack/lock.rb:11:in `synchronize'
    /usr/local/lib/ruby/gems/1.8/gems/rack-1.0.1/lib/rack/lock.rb:11:in `call'
    /usr/local/lib/ruby/gems/1.8/gems/actionpack-2.3.5/lib/action_controller/dispatcher.rb:114:in `call'
    /usr/local/lib/ruby/gems/1.8/gems/actionpack-2.3.5/lib/action_controller/reloader.rb:34:in `run'
    /usr/local/lib/ruby/gems/1.8/gems/actionpack-2.3.5/lib/action_controller/dispatcher.rb:108:in `call'
    /usr/local/lib/ruby/gems/1.8/gems/actionpack-2.3.5/lib/action_controller/cgi_process.rb:44:in `dispatch_cgi'
    /usr/local/lib/ruby/gems/1.8/gems/actionpack-2.3.5/lib/action_controller/dispatcher.rb:101:in `dispatch_cgi'
    /usr/local/lib/ruby/gems/1.8/gems/actionpack-2.3.5/lib/action_controller/dispatcher.rb:27:in `dispatch'
    /usr/local/lib/ruby/gems/1.8/gems/mongrel-1.1.5/bin/../lib/mongrel/rails.rb:76:in `process'
    /usr/local/lib/ruby/gems/1.8/gems/mongrel-1.1.5/bin/../lib/mongrel/rails.rb:74:in `synchronize'
    /usr/local/lib/ruby/gems/1.8/gems/mongrel-1.1.5/bin/../lib/mongrel/rails.rb:74:in `process'
    /usr/local/lib/ruby/gems/1.8/gems/mongrel-1.1.5/bin/../lib/mongrel.rb:159:in `process_client'
    /usr/local/lib/ruby/gems/1.8/gems/mongrel-1.1.5/bin/../lib/mongrel.rb:158:in `each'
    /usr/local/lib/ruby/gems/1.8/gems/mongrel-1.1.5/bin/../lib/mongrel.rb:158:in `process_client'
    /usr/local/lib/ruby/gems/1.8/gems/mongrel-1.1.5/bin/../lib/mongrel.rb:285:in `run'
    /usr/local/lib/ruby/gems/1.8/gems/mongrel-1.1.5/bin/../lib/mongrel.rb:285:in `initialize'
    /usr/local/lib/ruby/gems/1.8/gems/mongrel-1.1.5/bin/../lib/mongrel.rb:285:in `new'
    /usr/local/lib/ruby/gems/1.8/gems/mongrel-1.1.5/bin/../lib/mongrel.rb:285:in `run'
    /usr/local/lib/ruby/gems/1.8/gems/mongrel-1.1.5/bin/../lib/mongrel.rb:268:in `initialize'
    /usr/local/lib/ruby/gems/1.8/gems/mongrel-1.1.5/bin/../lib/mongrel.rb:268:in `new'
    /usr/local/lib/ruby/gems/1.8/gems/mongrel-1.1.5/bin/../lib/mongrel.rb:268:in `run'
    /usr/local/lib/ruby/gems/1.8/gems/mongrel-1.1.5/bin/../lib/mongrel/configurator.rb:282:in `run'
    /usr/local/lib/ruby/gems/1.8/gems/mongrel-1.1.5/bin/../lib/mongrel/configurator.rb:281:in `each'
    /usr/local/lib/ruby/gems/1.8/gems/mongrel-1.1.5/bin/../lib/mongrel/configurator.rb:281:in `run'
    /usr/local/lib/ruby/gems/1.8/gems/mongrel-1.1.5/bin/mongrel_rails:128:in `run'
    /usr/local/lib/ruby/gems/1.8/gems/mongrel-1.1.5/bin/../lib/mongrel/command.rb:212:in `run'
    /usr/local/lib/ruby/gems/1.8/gems/mongrel-1.1.5/bin/mongrel_rails:281

Normally, we could fix this by just logging into the postgres server and force updating the password for that user. It was unnecessary, because the 'pg_roles' table showed clearly that the user's password had not been changed - rails was being crazy, it had the right password. But regardless, doing this would make the application start working again.

Unfortunately, that hasn't worked this time. Using the same username and password, we can connect to the dbserver from the command line via pgsql:

[root@webserver current]# psql -U USERNAME -W --host dbserver DBNAME
Password for user USERNAME: PASSWORD
Welcome to psql 8.1.21 (server 8.1.11), the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

DBNAME=#

... so we're greater than 100% sure it's NOT actually a postgres problem, whether password, pg_hba, or whatever else, the problem is all inside Ruby's head.

We've tried:

- Restarting the web application
- Restarting the postgres server
- Deleting the entire web application directory, checking it out from subversion, and starting it again
- Using a different (but also valid) user & password in the config/database.yml file, and the error persists.

None of the steps above have helped. Does anyone have any idea what I can do to fix this?

Last edited by akesterson (2010-10-28 15:10:24)

Re: Rails app thinks postgres password is wrong

Sounds very much like you have some database corruption.
It's been a loooong time (6 years at least) since I last worked with postgres so I'm no longer familiar with the tools available to verify the integrity of the data and the indexes in a postgres db but you would be well advised to do some quick reading up on how to do this as your very next step.

If it's not a data corruption/postgres db issue I'd be very very surprised.

What you want and what you need are too often not the same thing!
When your head is hurting from trying to solve a problem, stop standing on it. When you are the right way up you will see the problem differently and you just might find the solution.
(Quote by me 15th July 2009)

Re: Rails app thinks postgres password is wrong

jamesw wrote:

Sounds very much like you have some database corruption.
It's been a loooong time (6 years at least) since I last worked with postgres so I'm no longer familiar with the tools available to verify the integrity of the data and the indexes in a postgres db but you would be well advised to do some quick reading up on how to do this as your very next step.

If it's not a data corruption/postgres db issue I'd be very very surprised.

I would be inclined to agree, except that we have 2 different applications which both access this same database, at the same time, using the same credentials, from different web servers. One application will stop working, while the other one will keep on chugging, even if we restart it.

However for the sake of completeness I'll check into your suggestion, I suppose crazier things have happened ... ?

Last edited by akesterson (2010-10-28 21:47:47)

Re: Rails app thinks postgres password is wrong

I checked the postgres documentation, and according to them, postgres doesn't get corrupt indexes and tables the same way that MySQL does with myisam tables, so there is no "check for / repair corrupt tables" utility. According to them, you can run a vacuumdb to clean it up a bit, but if a pg_dump command returns successfully, then your database is not corrupt.

I checked all that I could, and according to the documentation I have, the postgres database we're using is not corrupt. (Besides - it doesn't make sense that corrupt tables would cause the ruby postgres lib to fail inside its connect() function, but connections through regular postgres client would work fine. A corrupt DB would most likely generate errors when the tables were accessed, I would think?)

Like I said, we're pretty sure this is Ruby losing its mind. Nobody else has encountered this?

Re: Rails app thinks postgres password is wrong

Normally, we could fix this by just logging into the postgres server and force updating the password for that user. It was unnecessary, because the 'pg_roles' table showed clearly that the user's password had not been changed - rails was being crazy, it had the right password. But regardless, doing this would make the application start working again.

This implies that the issue is an environment or a db issue rather than a coding issue.

could it be something to do with deadlocks?

What you want and what you need are too often not the same thing!
When your head is hurting from trying to solve a problem, stop standing on it. When you are the right way up you will see the problem differently and you just might find the solution.
(Quote by me 15th July 2009)

Re: Rails app thinks postgres password is wrong

jamesw wrote:

This implies that the issue is an environment or a db issue rather than a coding issue.

Given that this rails application is the only piece of software - out of hundreds of scripts, small tools, etc which access this database - exhibiting this problem, I'm more inclined to think it's some sort of problem in either the application or the VM running it.

could it be something to do with deadlocks?

Unlikely. According to the postgres documentation (here http://developer.postgresql.org/pgdocs/ … cking.html ), Postgres will automatically detect deadlocks and terminate one of the transactions to terminate the lock condition. In that case what we would see is a postgres error related to a failed statement of some kind; we would not be seeing errors related to a bad password.

I mean, we're not even logging in here, how could we be getting far enough to generate deadlocks?

Last edited by akesterson (2010-10-29 10:03:26)

Re: Rails app thinks postgres password is wrong

Hhhmmm!
I'm stumped and all I can offer is thoughts. Sorry.

The fact that the app has been able to connect in the past proves that you have the code right.
The fact that on reset of the users password you have been able to resolve the issue in the past again indicates that you have your code sorted out.
The fact that this is an intermittent problem indicates that your issues are environmental issues that only occur under certain circumstances. all of which I'm sure you are more than likely aware of anyway.

This is going to be something really obscure and the only thing I can think of is that you fire up the console on the server that runs the RoR app and see if you are able to get a connection that way. If so then you will at least understand that the problem is going to be RoR related which right now I'm sure would be a help.

What you want and what you need are too often not the same thing!
When your head is hurting from trying to solve a problem, stop standing on it. When you are the right way up you will see the problem differently and you just might find the solution.
(Quote by me 15th July 2009)

Re: Rails app thinks postgres password is wrong

I tried from ./script/console, and I get the same errors when doing anything DB-related ... always "PGError: FATAL: password authentication failed" ...

Just found the official ruby Google Talk page, will go there and see if anyone has any suggestions. Thanks