26

I'm setting up my PostgreSQL 9.1 in windows.

I can't do anything with PostgreSQL: can't createdb, can't createuser; all operations return the error message

Fatal: role root does not exist
root is my account name, which I created while installing Postgresql

But I am able to connect using: 

 username : postgres

How can I connect to postgres using role root?
There is a solution mentioned for linux platforms using su command here but not able to figure out solution for windows7

Thanks in Advance

Community
  • 1
  • 1
Abhishek
  • 1,999
  • 5
  • 26
  • 52

3 Answers3

55

If you want to login to Postgres using the username root you need to first create such a user.

You first need to login as the Postgres super user. This is typically postgres (and is specified during installation):

psql -U postgres <user-name>

Then you can create roles and databases:

psql (9.4.0)
Type "help" for help.

postgres=# create user root with password 'verysecret';
CREATE ROLE
postgres=# \q

c:\
c:\>psql -U root postgres
psql (9.4.0)
Type "help" for help.

postgres=>

Logged in as the superuser you can also grant the root user the necessary privileges.

All parameters for psql are documented in the manual.

Creating users and databases is also documented in the manual:

bathyscapher
  • 1,615
  • 1
  • 13
  • 18
  • 1
    I have tried as you have explained, i.e. create user root with password 'root'; \q psql -U root : gives error : Fatal database "root" does not exist. – Abhishek Feb 02 '15 at 13:16
  • @Abhishek: then you need to specify which database you want to connect to (psql defaults to the same database name as the username [as documented in the manual](http://www.postgresql.org/docs/current/static/app-psql.html#R2-APP-PSQL-CONNECTING)). Or you need to create a database with that name if you one database per user. Do read the manual, it's all in there –  Feb 02 '15 at 13:21
  • 1
    If user `postgres` doesn't exist, then the very first line will not work. – Pere Jan 12 '17 at 10:31
  • 2
    @Pere: that's why I wrote "e.g. postgres" in the text before that. You need to use whatever the superuser is on your Postgres installation. But that is the default superuser name when installing Postgres –  Jan 12 '17 at 10:36
  • 1
    Sorry, @a_horse_with_no_name, it was my fault: I erroneously understood from the question that the user was unable to login **with `postgres` user**. Not only your answer was totally correct but my comment, plus my negative vote, lacked any sense. So double thumbs up for your edit because that little clarification makes it even better, if possible. – Pere Jan 12 '17 at 16:10
0

In some cases, when you install postgres the initial DB is not created.

You need to execute initdb.

Natacha
  • 1,132
  • 16
  • 23
0

Same issue appeared while restoring DB/table on postgres docker container .

  • . When you connect to Postgres DB(psql shell) from inside the docker container, the default user would be a "root" (unless you specify psql -U "some-user-name") [Manjunath-MacBook-Air:$ sudo docker exec -it a2ff6075344e bash

    bash-5.0# psql -U postgres postgres < testdb_pg_dump

  • So, the issue gets resolved, by logging to psql shell with appropriate username

Here , -U postgres specifies that user connecting to DB is "postgres"

Manju N
  • 886
  • 9
  • 14