PostgreSQL error: Fatal: role "username" does not exist

Question

I'm setting up my PostgreSQL 9.1. I can't do anything with PostgreSQL: can't createdb, can't createuser; all operations return the error message

Fatal: role h9uest does not exist

h9uest is my account name, and I sudo apt-get install PostgreSQL 9.1 under this account.
Similar error persists for the root account.

Afterwards you might run into `FATAL: role "user" is not permitted to log in`, check http://dba.stackexchange.com/questions/57723/superuser-is-not-permitted-to-login for that. — the, Sep 18 '14 at 14:32
I answered this question [here](http://stackoverflow.com/a/31712520/3637498). Read this tutorial too "[How to Install Postgres for Ubuntu Linux](http://www.gotealeaf.com/blog/how-to-install-postgres-for-linux)". — ruzenhack, Jul 29 '15 at 23:01
@Ruzenhack that tutorial link was exactly what I needed! Very clear and simple — dwanderson, Sep 25 '15 at 22:36
If you get the error `role doesn't exist` AND the error `can't create role, it already exists` check out this answer: https://stackoverflow.com/a/56658832/1689770 — Jonathan, Jun 19 '19 at 01:16
I ran into this trying to connect to a local Postgres container from DataGrip, and I had to configure the authentication method to be User & Password, set user to postgres, and leave password blank (using NoAuth, Datagrip tried to use my OS username as the role) — mway, Mar 28 '22 at 18:50

Erwin Brandstetter · Accepted Answer · 2023-04-09T16:06:49.323

1048

Use the operating system user postgres to create your database - as long as you haven't set up a database role with the necessary privileges that corresponds to your operating system user of the same name (h9uest in your case):

sudo -u postgres -i

As recommended here or here.

Then try again. Type exit when done with operating as system user postgres.

Or execute the single command createuser as postgres with sudo, like demonstrated by dsrees in another answer.

The point is to use the operating system user matching the database role of the same name to be granted access via ident authentication. postgres is the default operating system user to have initialized the database cluster. The manual:

In order to bootstrap the database system, a freshly initialized system always contains one predefined role. This role is always a “superuser”, and by default (unless altered when running initdb) it will have the same name as the operating system user that initialized the database cluster. Customarily, this role will be named postgres. In order to create more roles you first have to connect as this initial role.

I have heard of odd setups with non-standard user names or where the operating system user does not exist. You'd need to adapt your strategy there.

Read about database roles and client authentication in the manual.

edited Apr 09 '23 at 16:06

answered Aug 12 '12 at 04:13

Erwin Brandstetter

605,456
145
1,078
1,228

1

It is impossible to run `sudo etc` as user postgres... How to install a program? See [this S2geometry installation problem](https://github.com/AfieldTrails/s2-postgis/issues/2). – Peter Krauss Oct 17 '18 at 15:06
3

Depending on the configuration, one might simply need `su postgres`. – Fabien Snauwaert Jul 30 '19 at 13:49
6

`sudo -u postgres` doesn't work, while `sudo -u postgres -i` works! What does the `-i` flag do? – parsecer Nov 15 '19 at 13:56
2

@parsecer: An interactive shell is started, similar to `su`, but based on sudo privileges. See `man sudo`. – Erwin Brandstetter Nov 15 '19 at 19:02
WIth Postgres 12, I had to change `-u` to `-U` and I get "invalid option -- 'i'". – Code-Apprentice Oct 16 '21 at 17:00
I ran into the "postgres user does not exist" problem when restoring global roles and permissions from a backup file on a Mac. I found that I could just specify my Mac system user, i.e., `psql -U -d template1 -f `. Is there anything wrong with that approach? After doing that, the database roles include my regular system username (as well as a `postgres` user); I don't know if that's a security risk. – John Jan 03 '22 at 05:59

score 347 · Answer 2 · edited Feb 28 '20 at 11:52

347

After trying many other people's solutions, and without success, this answer finally helped me.

https://stackoverflow.com/a/16974197/2433309

In short, running

sudo -u postgres createuser owning_user

creates a role with name owning_user (in this case, h9uest). After that you can run rake db:create from the terminal under whatever account name you set up without having to enter into the Postgres environment.

edited Feb 28 '20 at 11:52

Vaibhav Mule

5,016
4
35
52

answered Sep 09 '13 at 23:27

dsrees

6,116
2
26
26

35

If you get an "insufficient privileges" error after this, you could append the -s flag to the above command. This will create your new user as a superuser. Please be aware of the security implications of this should you decide to do it. – Kabir Sarin Oct 20 '15 at 19:57
how to do this with homebrew? – SuperUberDuper Jul 15 '16 at 15:24
1

I'm getting "insufficient privileges", but now I get ERROR: roll "username" already exists. – Wylliam Judd Aug 17 '16 at 22:50
5

@ConnorLeech You can replace 'postgres' with the name of your PG super user. In my case it was my name. – myfashionhub Sep 14 '16 at 10:13
1

I also had to use `-s`, and did not need to use `sudo -u postgres` (just `createuser new_user` worked fine) – Meekohi Dec 20 '17 at 18:43
3

`psql: FATAL: role "jonny" does not exist` okay so `sudo -u postgres -s createuser jonny` then why `createuser: creation of new role failed: ERROR: role "jonny" already exists` – Jonathan May 01 '19 at 03:35
@Jonathan Drop using "sudo -u postgres dropuser jonny" – Siddanatham Saikalyan Aug 04 '20 at 08:59
The original answer has fewer up-votes, LOL!! – Kartik Feb 05 '21 at 19:11
1

If you get "insufficient privileges" error and you do not want to create your user as `superuser`. Then basically create a user with `CREATEDB` attribute like explained [here](https://www.postgresql.org/docs/9.1/role-attributes.html#ROLE-ATTRIBUTES). – Burak Kaymakci Feb 27 '21 at 21:07

score 172 · Answer 3 · edited Aug 09 '16 at 17:02

172

sudo su - postgres

psql template1

creating role on pgsql with privilege as "superuser"

CREATE ROLE username superuser;
eg. CREATE ROLE demo superuser;

Then create user

CREATE USER username; 
eg. CREATE USER demo;

Assign privilege to user

GRANT ROOT TO username;

And then enable login that user, so you can run e.g.: psql template1, from normal $ terminal:

ALTER ROLE username WITH LOGIN;

edited Aug 09 '16 at 17:02

the

21,007
11
68
101

answered May 29 '14 at 13:35

Mohammed Saleem

1,883
1
11
6

4

I just did a new install of PostgreSQL 9.4.4 and was able to use the follow to create a new superuser: ```CREATE ROLE username superuser createdb login;``` which combines all three steps... – iPad Guy Jul 06 '15 at 06:57
37

# GRANT ROOT to me; ERROR: role "root" does not exist – shacker Nov 24 '16 at 08:41
8

simpler version: ```sudo -u postgres createuser --superuser $USER; sudo -u postgres createdb $USER``` – Zach Estela Aug 05 '17 at 03:16
3

@shacker `#GRANT postgres TO username` – 7kemZmani Oct 23 '19 at 19:34
I am using root to start the server then ```psql -U postgres -h 127.0.0.1 --command "CREATE USER postgresondocker WITH SUPERUSER PASSWORD 'postgresondocker';" &&\ createdb -O postgresondocker postgresondocker`` can I use root to do this if I add it to the pg_ident? – Shivangi Singh Mar 02 '20 at 00:33

score 140 · Answer 4 · edited Dec 19 '14 at 18:12

140

This works for me:

psql -h localhost -U postgres

edited Dec 19 '14 at 18:12

ssbl

43
2
6

answered Aug 12 '14 at 11:39

mateusz.szymborski

1,590
1
9
10

please add the password for above user. – StealthTrails Oct 30 '18 at 11:13
2

@Vincent if the password was never changed after PostgreSQL installation, by default it is `postgres` (tested on Linux and Windows). – silvioprog Mar 07 '19 at 15:22
6

`role postgres does not exist` – J-Cake Oct 02 '19 at 08:59
2

To access a specific database you'll need to specify the db name ```psql -h hostname -U username -d database_name``` – toel Feb 12 '21 at 09:52

score 117 · Answer 5 · answered Jul 18 '16 at 19:01

117

Installing postgres using apt-get does not create a user role or a database.

To create a superuser role and a database for your personal user account:

sudo -u postgres createuser -s $(whoami); createdb $(whoami)

answered Jul 18 '16 at 19:01

Miles Erickson

2,574
2
17
18

3

I needed to run the second part as: `sudo -u postgres createdb $(whoami)` – SanD Sep 26 '18 at 20:02
2

@SanD If you’ve created your own user account as a super user (via the `-s` flag in the first command), you can create the database as yourself. You won’t need to run `createdb` as `postgres`. – Miles Erickson Dec 02 '18 at 15:38

score 40 · Answer 6 · answered Apr 07 '19 at 15:08

40

psql postgres

postgres=# CREATE ROLE username superuser;
postgres=# ALTER ROLE username WITH LOGIN;

answered Apr 07 '19 at 15:08

Abel

3,989
32
31

9

`psql postgres` results: `psql: FATAL: role "root" does not exist` – TomSawyer May 29 '19 at 21:16
5

`psql postgres -U postgres`? Depends on the user (-U) you have. – Meadowlark Bradsher Jan 28 '20 at 21:04
thank you! I struggled how to backup postgresql with root since I dont know postgres password – Dika Feb 06 '20 at 08:47
I get an error "must be a superuser to create a superuser" – Naveed Hasan Aug 21 '20 at 13:10

score 23 · Answer 7 · answered Jan 13 '19 at 05:07

23

For version Postgres 9.5 use following comand:

psql -h localhost -U postgres

Hope this will help.

answered Jan 13 '19 at 05:07

Kalyan Halder

1,485
24
28

score 19 · Answer 8 · answered Sep 10 '17 at 16:23

19

Working method,

vi /etc/postgresql/9.3/main/pg_hba.conf
local all postgres peer here change peer to trust
restart, sudo service postgresql restart
now try, psql -U postgres

answered Sep 10 '17 at 16:23

Mohideen bin Mohammed

18,813
10
112
118

2

It should be obvious that the `trust` method is only advisable in a completely secure environment. While access from untrusted connections is possible, *never* expose your DB cluster like that. – Erwin Brandstetter Jul 11 '19 at 15:36
Check: If the postgresql service is not started, possibly there can be some error in the file `pg_hba.conf` – Walk Jun 01 '20 at 11:18

score 19 · Answer 9 · answered Aug 07 '21 at 08:07

I did a healthcheck with docker-compose.

healthcheck:
  test: ['CMD-SHELL', 'pg_isready']
  interval: 5s
  timeout: 5s
  retries: 5

If you also have that change the user:

healthcheck:
  test: ['CMD-SHELL', 'pg_isready -U postgres'] # <<<---
  interval: 5s
  timeout: 5s
  retries: 5

Andriy Tolstoy · Answer 10 · 2019-02-08T12:18:54.547

11

For Windows users : psql -U postgres

You should see then the command-line interface to PostgreSQL: postgres=#

edited Feb 08 '19 at 12:18

answered Feb 08 '19 at 12:13

Andriy Tolstoy

5,690
2
31
30

Thanks man. This worked for me on windows. – Kidus Tekeste Mar 21 '21 at 14:54

score 8 · Answer 11 · edited Aug 24 '15 at 18:10

8

In local user prompt, not root user prompt, type

sudo -u postgres createuser <local username>

Then enter password for local user.

Then enter the previous command that generated "role 'username' does not exist."

Above steps solved the problem for me. If not, please send terminal messages for above steps.

edited Aug 24 '15 at 18:10

Erwin Brandstetter

605,456
145
1,078
1,228

answered Jun 21 '15 at 20:28

Robert Cambil

99
1
1

score 7 · Answer 12 · answered Apr 29 '18 at 02:15

7

I installed it on macOS and had to:

cd /Applications/Postgres.app/Contents/Versions/9.5/bin
createuser -U postgres -s YOURUSERNAME
createdb YOURUSERNAME

Here's the source: https://github.com/PostgresApp/PostgresApp/issues/313#issuecomment-192461641

answered Apr 29 '18 at 02:15

Henry

2,870
1
25
17

score 5 · Answer 13 · edited Jun 20 '20 at 09:12

Manually creating a DB cluster solved it in my case.

For some reason, when I installed postgres, the "initial DB" wasn't created. Executing initdb did the trick for me.

This solution is provided in the PostgreSQL Wiki - First steps:

initdb

Typically installing postgres to your OS creates an "initial DB" and starts the postgres server daemon running. If not then you'll need to run initdb

score 4 · Answer 14 · answered Jul 25 '22 at 13:05

4

sudo -u postgres createuser --superuser $USER

sudo -u postgres createdb $USER

This should definitely work for you.

answered Jul 25 '22 at 13:05

Boy Nandi

83
7

score 2 · Answer 15 · answered Jul 25 '19 at 12:29

dump and restore with --no-owner --no-privileges flags

e.g.

dump - pg_dump --no-owner --no-privileges --format=c --dbname=postgres://userpass:username@postgres:5432/schemaname > /tmp/full.dump

restore - pg_restore --no-owner --no-privileges --format=c --dbname=postgres://userpass:username@postgres:5432/schemaname /tmp/full.dump

score 0 · Answer 16 · answered Apr 27 '22 at 04:57

0

for those who using docker and correctly followed the instructions from official doc, if you still met this problem, RESTART windows and try again.

answered Apr 27 '22 at 04:57

Siwei

19,858
7
75
95

score -2 · Answer 17 · edited Aug 17 '17 at 14:07

Follow These Steps and it Will Work For You :

run msfconsole
type db_console
some information will be shown to you chose the information who tell you to make: db_connect user:pass@host:port.../database sorry I don't remember it but it's like this one then replace the user and the password and the host and the database with the information included in the database.yml in the emplacement: /usr/share/metasploit-framework/config
you will see. rebuilding the model cache in the background.
Type apt-get update && apt-get upgrade after the update restart the terminal and lunch msfconsole and it works you can check that by typing in msfconsole: msf>db_status you will see that it's connected.

score -3 · Answer 18 · answered Jul 11 '21 at 05:48

Follow these steps to get postgres working.

In your terminal, locate the Application Support folder with the following command. /Users/[name of the user]/library/application support
Delete the application, Postgres.
Reinstall the app and it should work just fine.

score -5 · Answer 19 · answered Aug 12 '20 at 00:23

-5

Something as simple as changing port from 5432 to 5433 worked for me.

answered Aug 12 '20 at 00:23

Guka Nozadze

9
4

Has nothing to do w/ this issue, directly. – Tsar Bomba Jul 26 '21 at 19:59

PostgreSQL error: Fatal: role "username" does not exist

19 Answers19

Linked

Related