I'm following these instructions to get postgres working with rails on Windows 7:
Run "Start Command Prompt with Ruby" then…
- createuser -U postgres -P NewUserName
- When it prompts for a password for new role, assign it a new password.
- No, the new role should not be a superuser.
- Yes, the new role should be allowed to create databases.
- No, the new role should not be allowed to create more new roles.
- Enter the postgresPWD you made when we installed PostgreSQL.
But this isn't happening. After I run #1, it asks me for a password, then skips straight to 5. This is completely screwing me over! Where did 2 through 4 go?
So then I tried doing it manually with createuser -s -U postgres -d -r
(I have to do -U postgres
because otherwise it asks for a password for a user that seems to be based on my Windows account, and no password I give it is successful…I tried fixing this by changing pg_hba.conf and then NOTHING worked)….so anyway I tried doing it manually and it was successful, but then I tried createdb newdb_name
and it said createdb: could not connect to database template1: FATAL: password authentication failed for user "{my windows account}"
.
So I guess I could createdb
as postgres
, but then my rails commands to rake db:create
would still fail. I need the active account to have full rights so that I can keep doing rails work.
I'm becoming really miserable about this. Please help…
Best Answer
It seems that there are two distinct questions here.
Why don't the
createuser
instructions apply?It's because they apply to PostgreSQL 9.1 or older, and not the newest versions.
createuser
now has a new flag--interactive
. Per documentation:So by adding this flag you'll get back to the behavior described by the tutorial.
Once the user is created, why does
createdb
fails with a password authentication problem?It's because you're not using your newly created user to create the database, although it's what would seem to make sense in this context.
Generally with all PostgreSQL command line tools, when a user is not specified on the command line, the OS user is assumed instead (but it's only useful when there happens to be a database user named after the OS user, otherwise that may unfortunately be a source of confusion).
Anyway, instead of
createdb newdb_name
, try:Then you should be asked for the password of
newusername
that you created in the step above.Technically
createdb
has to connect to a database (template1
in this case) to be able to create another database, because this is done by a SQL statement and no SQL can be issued when you're not connected to a database in the first place.Also by doing this
newusername
will be the database owner ofnewdb_name
, that will give it full over control over it and is probably what your application needs.