27

I recently installed the latest version of Ubuntu and I'm not used to it yet. I had several problems connecting PostgreSQL with pgadmin3.

I tried to follow several tutorials to create a localhost server which I can use in pgadmin3. I didn't really hit the mark and I think I made it worse by installing, uninstalling, installing, uninstalling, trying answers I found... At this point I'm not sure my PostgreSQL is clean. It could be possible that I have two PostgreSQL installed, once again, I'm not sure. I tried to uninstall it but I had an error which basically said PostgreSQL wasn't in sudo.

The thing is I now have this error running psql in the console:

$psql
psql: FATAL: role "user" does not exist

I can't find the pg_hba.conf and at this point I'm too afraid to make things even worse by trying following any other posts.

How could I make this work?

whiplash
  • 375
  • 1
  • 3
  • 5
  • `pg_hba.conf` is irrelevant here. You are trying to connect with the username `user` - but that account/username/role does not exists. You need to specify an existing database user using the `-U` switch when starting `psql` –  Jan 29 '15 at 11:47
  • Since the error message comes from psql, you are already connected to the DBMS, No need to mess with pg_hba.conf. As the message says: the user name "user" does not exist. BTW: `user` is a terrible name for a user. Would you call your child `child` ? – joop Jan 29 '15 at 11:47
  • 1
    I wans't clear but instead of user, that's the name of my session that is written. I'll try all that tonight. – whiplash Jan 30 '15 at 13:31
  • Possible duplicate of [PostgreSQL error: Fatal: role "username" does not exist](http://stackoverflow.com/questions/11919391/postgresql-error-fatal-role-username-does-not-exist) – Teemu Leisti Aug 17 '16 at 14:30

5 Answers5

55

As pointed out in the comments, your pg_hba.conf seems fine.

Usually, the database will run as the postgres user (check ps aux | grep postgres to find out the username postgres is running under).

Log in as that user, for example sudo su - postgres, then create a user matching your normal Ubuntu user account (createuser username), and finally create a database with that same name and set the owner (-O) to that database user, like this: createdb -O username username).

That should make calling psql work, and pgadmin - as long as you start it as your default user, username - should work as well.

Edit: By default, psql will use your Linux username as default value for both the database-username and the database-name. You can override the username by using -U someotherusername, and connect to a different database by adding that DB name to the command line, such as psql someotherdbname. You might also find psql -l useful for listing the existing databases.

niko
  • 1,816
  • 13
  • 13
  • 2
    You don't necessarily need a database for that user. The database can be specified when starting psql: `psql db_name` –  Jan 29 '15 at 11:55
  • 1
    I've added an edit to clarify that. In most cases though, you will want calling `psql` with no parameters to "just work", so that's what my answer focussed on. – niko Jan 29 '15 at 12:00
  • Hi, Thank's for your answers, – whiplash Jan 30 '15 at 18:36
  • Sorry for double post Hi, Thank's for your help, it's fine for postgres, now. Still, I cannot manage to connect to pgadmin3, it seems that the server version is not supported by pgadmin3 but that's another topic... Anyway, thank's again. – whiplash Jan 30 '15 at 18:52
  • `sudo su - postgres` ------ `createuser jonny` -------- `createuser: creation of new role failed: ERROR: role "jonny" already exists` --------- `exit; whoami;` ------- `jonny` ---------- `psql` ---------- `FATAL: role "jonny" does not exist` – Jonathan May 01 '19 at 03:44
12
sudo -u postgres createuser user

may help you to create a new user with all the privileges of postgres

Anonymous Platypus
  • 1,242
  • 4
  • 18
  • 47
watsy0007
  • 132
  • 1
  • 7
4

Use the postgres user:

sudo su postgres

and then use psql.

If you still face problems check this link: PostgreSQL error: Fatal: role "username" does not exist

Community
  • 1
  • 1
Amr Arafat
  • 469
  • 3
  • 8
0

In windows 10 this works for fine

pg_restore --format=c --verbose --no-owner --no-privileges --dbname=MYDB --username=postgres --no-password --jobs=8 F:/MYBK.backup

--username=(role) and --no-password are necesaries

Giordhano
  • 581
  • 5
  • 5
0

Though it's a bit question old if you're facing this on windows, simply delete Postgres data directory and re-initialize Postgres data by running initdb /data