1

I'm following these instructions to get postgres working with rails on Windows 7:

Run "Start Command Prompt with Ruby" then...

  1. createuser -U postgres -P NewUserName
  2. When it prompts for a password for new role, assign it a new password.
  3. No, the new role should not be a superuser.
  4. Yes, the new role should be allowed to create databases.
  5. No, the new role should not be allowed to create more new roles.
  6. 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...

  • I got everything working once I figured out how to make handy use of the pgAdmin interface, but via Windows command line this is still all very baffling to me. – temporary_user_name Aug 20 '13 at 4:13
  • 1
    @EvanCarroll: I've reverted your edits because they're removing way too much and loosing half the meaning. Not OK with that at all. – Daniel Vérité Apr 30 '18 at 19:07
  • @Aerovistae Ok, then I'll vote to close. I've voted to close this question for being Too Broad createuser prompts have nothing to do with database you're connecting too, and as the chosen answer says: they're "two distinct questions". Please consider breaking the question a part (or accepting the edit to refine the scope). – Evan Carroll Apr 30 '18 at 19:07
  • You realize this is a 5 year old question, right? What difference does it make to anyone whether this is open or closed? It already had an accepted answer. – temporary_user_name May 1 '18 at 2:07
  • @Aerovistae we have to weight out the value of the question in perpetuity. 3,693 people have seen this. Fact is that's a lot of stuff not relevant to the prompt that shouldn't be there imho (or in the opinion of the Daniel unless he's advocating for compound questions). I don't think it's a very high quality question to be burdening those searching for this problem with what database they should be connecting to, so I tried to clean it up and fix the issue. If you want to accept the edits, I'll give you the upvote. Until then, StackOverflow has better more terse material on this. – Evan Carroll May 1 '18 at 16:20
3

It seems that there are two distinct questions here.

  1. 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:

    --interactive Prompt for the user name if none is specified on the command line, and also prompt for whichever of the options -d/-D, -r/-R, -s/-S is not specified on the command line. (This was the default behavior up to PostgreSQL 9.1.)

    So by adding this flag you'll get back to the behavior described by the tutorial.

  2. 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:

createdb -U newusername newdb_name

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 of newdb_name, that will give it full over control over it and is probably what your application needs.

4

First, let's understand how createuser works, run this command and take a look at the results:

> createuser --help

Will you see a lot of information, by those the most important for you now is the following...

This one is what makes PostgreSQL ask for a password for this new user:

-P, --pwprompt            assign a password to new role

This one is the one that will make this user able to create a new database (notice you didn't used it):

-d, --createdb            role can create new databases

At the bottom, you will have a group of arguments:

Connection options:
  -h, --host=HOSTNAME       database server host or socket directory
  -p, --port=PORT           database server port
  -U, --username=USERNAME   user name to connect as (not the one to create)
  -w, --no-password         never prompt for password
  -W, --password            force password prompt

These ones are used to connect to PostgreSQL's instance, which means that, to create a new user you will have first to connect to PostgreSQL using an already existent user that is able to create another. On a default installation the only user you have is postgres, so to connect you have to use it as -U postgres. Depending on your pg_hba.conf configuration, the createuser utility will ask for the password of postgres user.

With all these information, you should be able to know how to create your new user:

> createuser -U postgres -e -P -d NewUserName

OBS: -e is not necessary, but is good to learn what is happening behinde the scenes.

Afeter that all, you probably can create a new database using rake db:create. Perhaps you will need to edit pg_hba.conf, but I think this is a new topic.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.