Ruby on Rails | Postgresql ignores my password in database.yml

Question

Starting to develop my first Ruby on Rails app with postgresql on Ubuntu. I have created a postgresql user with a password. In the database.yml, I put in the postgresql username and password.

Whenever I run a rake db:migrate it executes without error no matter what I change the password to in the database.yml - even if the password field is blank. If I change the username I get an authentication error.

How do I get Ruby on Rails database to use a password?

TIA

Answer 1

You're probably using ident or even trust authentication. A quick synopsis of the most common authentication methods:

• trust - You can log in no matter what.
• ident - You can log in if your UNIX username is the same as the PostgreSQL username.
• md5 - You can log in if your password (encrypted with md5) is correct.

Edit: PostgreSQL 9.0 introduced the peer authentication method. From what I gather, ident and peer have the same purpose—your login is determined by your operating system username—but ident talks to an ident server listening on port 113, while peer looks up your credentials with a system call. See http://www.postgresql.org/docs/9.1/static/auth-methods.html#AUTH-IDENT

Locate your pg_hba.conf file, and see if you can find something that looks like this:

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# "local" is for Unix domain socket connections only
local   all         all                               ident
# IPv4 local connections:
host    all         all         127.0.0.1/32          md5
# IPv6 local connections:
host    all         all         ::1/128               md5

When you try to connect, PostgreSQL goes through this line-by-line. If the connection type (e.g. local, host), database, user (database user, not system user), and address all match up, it will use the given authentication method.

If you want to require a password to access your own PostgreSQL user, you could add a line like this at the top, before the local all all ident line:

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
local   mydbname    myusername                        md5

Be sure to restart PostgreSQL after changing pg_hba.conf.

Answer 2

I've only barely used PostgreSQL, but I do know it has a feature called sameuser. If the name of the system user matches the name of the database user, the password is not required. So, if I logged into this computer with the username "matchu", and there is a user in PostgreSQL named "matchu", I could log in to that database user without additional authentication.

Could that be what's going on here?