Change postgreSQL database user from windows user in CMD

postgresql

i recently installed PostgreSQL on windows 7, each time i try to use CMD to execute SQL command, i get this error


createdb: could not connect to database template1: FATAL: password authentication failed for user "R-61"

Where R-61 is my windows machine username.

Here is how i use CMD with pgSQL

  • first i cd to the pgSQL bin folder $ cd C:\xampp\pgsql\9.1\bin
  • i try creating a database $ createdb NEWDATABASE

  • i get a prompt to enter a password. this is where am confuse as i do not know what password.
    I have tried my window user password, password for the postgres super user, none seem to work. this is the error message i get
    createdb: could not connect to database template1: FATAL: password authentication failed for user "R-61"

    Anybody know the solution to this?
    am thinking changing the user form R-61 to postgres superuser would work but i do not know how to do it.

Your help is greatly needed.

Best Answer

createdb is a command that has to connect to the server just like any other client command (even when run on the server itself), and as such requires a database user to connect with.

If you don't specify a database user with -U username option, it takes your OS username, hence the reference to "R-61" in the error message. The password that is asked is the password of a non-existing R-61 database user.

There are also other options such as the host defaulting to localhost and the port defaulting to 5432, see its manual page for all of them.

Since you mention that you know the postgres superuser password, the simplest solution would be to use the postgres user to initiate the command. It makes sense anyway, because on a fresh install this user would the only one that has the permission to create a database.

Try createdb -U postgres newdatabase

It should ask for a password, and that will be the password of the postgres database user.

If it doesn't ask for a password, it means that the security policy in pg_hba.conf is configured that way. If the password is lost (or no password has ever been set, which is the case for some automated installs, especially on Unix), pg_hba.conf has to be changed and the service reloaded to authorize passwordless connections, at least temporarily to set a new password.

Related Question