CODEWITHSUNDEEP
macospostgresqlterminal
user805981
user805981

Reputation: 11069

psql: FATAL: role "postgres" does not exist

I'm a postgres novice.

I installed the postgres.app for mac. I was playing around with the psql commands and I accidentally dropped the postgres database. I don't know what was in it.

I'm currently working on a tutorial: http://www.rosslaird.com/blog/building-a-project-with-mezzanine/

And I'm stuck at sudo -u postgres psql postgres

ERROR MESSAGE: psql: FATAL: role "postgres" does not exist

$ which psql 

/Applications/Postgres.app/Contents/MacOS/bin/psql

This is what prints out of psql -l

                                List of databases
    Name    |   Owner    | Encoding | Collate | Ctype |     Access privileges     
------------+------------+----------+---------+-------+---------------------------
 user       | user       | UTF8     | en_US   | en_US | 
 template0  | user       | UTF8     | en_US   | en_US | =c/user                  +
            |            |          |         |       | user      =CTc/user      
 template1  | user       | UTF8     | en_US   | en_US | =c/user                  +
            |            |          |         |       | user      =CTc/user      
(3 rows)

So what are the steps I should take? Delete an everything related to psql and reinstall everything?

Thanks for the help guys!

Upvotes: 780

Views: 875154

Answers (30)

Naman Madharia
Naman Madharia

Reputation: 45

For Mac, if you have installed postgres using Brew then using psql try running

\du

If this has missing role of 'posttgres' then create one using the below:

CREATE USER postgres WITH SUPERUSER PASSWORD 'password';

Upvotes: -1

A.H.
A.H.

Reputation: 66283

NOTE: If you installed postgres using homebrew, see the comments from @user3402754 and @originalhat below.

Note that the error message does NOT talk about a missing database, it talks about a missing role. Later in the login process it might also stumble over the missing database.

But the first step is to check the missing role: What is the output within psql of the command \du ? On my Ubuntu system the relevant line looks like this:

                              List of roles
 Role name |            Attributes             | Member of 
-----------+-----------------------------------+-----------
 postgres  | Superuser, Create role, Create DB | {}

If there is not at least one role with superuser, then you have a problem :-)

If there is one, you can use that to login. And looking at the output of your \l command: The permissions for user on the template0 and template1 databases are the same as on my Ubuntu system for the superuser postgres. So I think your setup simple uses user as the superuser. So you could try this command to login:

sudo -u user psql user

If user is really the DB superuser you can create another DB superuser and a private, empty database for him:

CREATE USER postgres SUPERUSER;
CREATE DATABASE postgres WITH OWNER postgres;

But since your postgres.app setup does not seem to do this, you also should not. Simple adapt the tutorial.

Upvotes: 691

Salim muneer lala
Salim muneer lala

Reputation: 99

Fixing psql error: FATAL: role “postgres” does not exist

1 - Install postgresql

brew install postgresql

2 - Start postgresql

brew services start postgresql

When running the command to access postgresql:

psql -U postgres

And the error is thrown: "psql: FATAL: role “postgres” does not exist"

To correct:

/usr/local/Cellar/postgresql//bin/createuser -s postgres

or

/usr/local/opt/postgres/bin/createuser -s postgres

This way the postgresql user is created and you can have access.

  • if the above code doesn't work you can try this one below

sudo su postgres createuser -s postgres -U <your-os-username>

Upvotes: 2

Amit Meena
Amit Meena

Reputation: 4454

And if you are here in 2024 :) and wondering what works with the latest Postgres on the latest macOS (macOS Monterey )

follow this:

brew install postgresql
createuser -s postgres
brew services restart postgresql

Note: In case you are using a dockerized instance make sure to first set the variables so you can connect to that instance: export PGUSER="youruser" export PGHOST="127.0.0.1" export PGPASSWORD="yourpassword.

Thanks, @NicolasZ for the comment regarding dockerized instance.

Upvotes: 223

Jonas Braga
Jonas Braga

Reputation: 635

In my case I only needed to create that user in the database I was restoring:

CREATE ROLE postgres;
GRANT ALL PRIVILEGES ON DATABASE database_name TO postgres;

Upvotes: 2

Akshay Narang
Akshay Narang

Reputation: 236

My System

  • Machine: M2 Macbook Pro
  • macOS: Ventura 13.4.1
  • Postgres Version: 15.3 (postgres --version)
  • Installation Method: Homebrew

My Solution

I was able to resolve the issue by creating a postgres user via the following command. Please note your postgres location and createuser utility may vary based on your system and/or installation method:

/opt/homebrew/opt/postgresql@15/bin/createuser -s postgres

Upvotes: 10

Jchy
Jchy

Reputation: 98

Go to your terminal. Make sure that you're out of the postgres shell and your postgresql is running in the terminal. Now run the following command in your terminal :

createuser -s postgres

Upvotes: 3

zabop
zabop

Reputation: 7922

On Mac, executing

createuser -s postgres

in a terminal worked.

Upvotes: 4

Mikeumus
Mikeumus

Reputation: 3888

psql -U debug -d dashboard_3

I had to specify the username and database name using the postgres:14 Docker container's terminal to be able to psql into the database.

Upvotes: 0

Fed
Fed

Reputation: 1885

Context

I am adding an answer for a case I have not seen here, which is an edge case if you have multiple users on the same machine and the user who is trying to use postgres services is not the user who installed postgres on the machine.

What I have tried

Among other similar commands, for me all these commands failed:

createuser -s [your username]
# createuser: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL:  role "[your username]" does not exist

createuser -s postgres
# createuser: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL:  role "[your username]" does not exist

sudo -u postgres createuser --superuser [your username]
# sudo: unknown user: postgres
# sudo: error initializing audit plugin sudoers_audit

psql -U postgres
# psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL:  role "postgres" does not exist

Reason

The reason is because neither postgres role nor [your username] (aka whoami on your command line) are in postgres.

Solution

In such edge case I had to first login with the user who installed postgres:

sudo su - [username that installed postgres]

And then create a role for my new user:

createuser -s [your username]

Upvotes: 3

Amar Bisht
Amar Bisht

Reputation: 137

If you are a MAC (M1) user and installed the Postgres using HomeBrew then follow these steps:

  1. Check your Postgres location using which psql
  2. then run the command /opt/homebrew/bin/createuser -s postgres if the output for the first command is /opt/homebrew/bin/psql

The idea is to create a user named 'postgres' using the Postgres installation location. So you may need to change the command based on the location of your Postgres.

Upvotes: 2

Seyede Zahra Borghei
Seyede Zahra Borghei

Reputation: 61

For m1 chips, if you have not installed postgresql package by homebrew, install it in terminal with:

brew install postgre

then create a username manually by:

/opt/homebrew/bin/createuser -s <username>

your error is probably fixed; but if you occur the error

FATAL: database "databasename" does not exist

then you have to create your database manually by:

/opt/homebrew/bin/createdb -U <username>  <databasename>

Upvotes: 6

Nitesh Rawat
Nitesh Rawat

Reputation: 91

For m1 chips and homebrew version 3.4.9, the createuser is moved inside Cellar of the particular package. This worked for me /opt/homebrew/Cellar/postgresql@12/12.10_1/bin/createuser -s postgres

Upvotes: 2

stefanitsky
stefanitsky

Reputation: 483

With a new mac (M1) and latest postgres (14.0) installed via homebrew, nothing helped me from this topic, but i just reinstalled postgres and it helped:

brew services stop postgresql
rm -rf /opt/homebrew/var/postgres/*
brew reinstall postgresql
initdb --locale=C -E UTF-8 /opt/homebrew/var/postgres
brew services restart postgresql

So, it's a miracle or something like that...

Then just:

psql -d postgres

Upvotes: 3

Ananda G
Ananda G

Reputation: 2539

I've faced similar problem today, actually i was not sure what was the username. Here is the 2 thing, if you are under enterprise and don't have system admin access the postgres will create your enterprise username as the postgres admin username. If you install through Homebrew it will definitely happening. In that case simply run your psql service with brew and do an echo of the username

brew services start postgresql

then

echo $USER

You will see your username of the postgres user.

Upvotes: 4

palamunder
palamunder

Reputation: 2775

If you are experiencing this problem right after running a docker container try destroying the container and recreating it. That solved it for me:

docker-compose down
docker-compose up --force-recreate

This should recreate the db with postgresuser as default user

Upvotes: 2

Mba Gozpel
Mba Gozpel

Reputation: 213

This worked for me

createuser -s postgres

note: I'm using mac catalina

Upvotes: 9

kangkyu
kangkyu

Reputation: 6140

We have a db named postgres after brew install postgresql and brew services start postgresql. So we can open psql like this by default.

psql postgres

And then we can add users with any name like this in that psql console.

CREATE USER postgres

And if we want a super user, then we can add SUPERUSER at the end.

Upvotes: 9

user9347049
user9347049

Reputation: 2055

This article helped me to solve same issue psql: FATAL: role “postgres” does not exist.

I am using mac, so I entered this command in terminal:

createuser -s postgres

And it worked for me.

Upvotes: 10

SwiftPush
SwiftPush

Reputation: 181

If you installed postgres from Brew and are using an Apple Silicon (M1) mac, run this in your terminal:

/opt/homebrew/opt/postgresql/bin/createuser -s postgres

If you're using an Intel (x86) mac, run this in your terminal:

/usr/local/opt/postgres/bin/createuser -s postgres

Upvotes: 18

Saket Sinha
Saket Sinha

Reputation: 521

If you installed postgres from brew, run this in your terminal :

/usr/local/opt/postgres/bin/createuser -s postgres

Upvotes: 48

Bruno Medeiros
Bruno Medeiros

Reputation: 2399

If you're using docker, make sure you're NOT using POSTGRES_USER=something_else, as this variable is used by the standard image to know the name of the PostgreSQL admin user (default as postgres).

In my case, I was using this variable with the intent to set another user to my specific database, but it ended up of course changing the main PostgreSQL user.

Upvotes: 10

isapir
isapir

Reputation: 23590

This happens when you run initdb with a user whose ID is not postgres, without specifying the postgres username with --username=postgres or -U postgres.

The database cluster is then created with the system's user account that you used to run initdb, and it is given superuser permissions.

To fix it, simply create a new user named postgres with the option --superuser using the createuser utility that comes with Postgres. The utility can be found in the Postgres' bin directory. e.g.

createuser --superuser postgres

If you have a custom hostname or port then be sure to set the appropriate options.

Don't forget to delete the other user account that was created for you by initdb.

Upvotes: 66

Gaurav Neema
Gaurav Neema

Reputation: 166

On Ubuntu system, I purged the PostgreSQL and re-installed it. All the databases are restored. This solved the problem for me.

Advice - Take the backup of the databases to be on the safer side.

Upvotes: 1

Nitin
Nitin

Reputation: 7687

For MAC:

  1. Install Homebrew
  2. brew install postgres
  3. initdb /usr/local/var/postgres
  4. /usr/local/Cellar/postgresql/<version>/bin/createuser -s postgres or /usr/local/opt/postgres/bin/createuser -s postgres which will just use the latest version.
  5. start postgres server manually: pg_ctl -D /usr/local/var/postgres start

To start server at startup

  • mkdir -p ~/Library/LaunchAgents
  • ln -sfv /usr/local/opt/postgresql/*.plist ~/Library/LaunchAgents
  • launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

Now, it is set up, login using psql -U postgres -h localhost or use PgAdmin for GUI.

By default user postgres will not have any login password.

Check this site for more articles like this: https://medium.com/@Nithanaroy/installing-postgres-on-mac-18f017c5d3f7

Upvotes: 347

jwd630
jwd630

Reputation: 4675

The key is "I installed the postgres.app for mac." This application sets up the local PostgreSQL installation with a database superuser whose role name is the same as your login (short) name.

When Postgres.app first starts up, it creates the $USER database, which is the default database for psql when none is specified. The default user is $USER, with no password.

Some scripts (e.g., a database backup created with pgdump on a Linux systsem) and tutorials will assume the superuser has the traditional role name of postgres.

You can make your local install look a bit more traditional and avoid these problems by doing a one time: 

/Applications/Postgres.app/Contents/Versions/9.*/bin/createuser -s postgres

which will make those FATAL: role "postgres" does not exist go away.

Upvotes: 297

Michael
Michael

Reputation: 1079

The \du command return:

Role name = postgres@implicit_files

And that command postgres=# \password postgres return error:

ERROR: role "postgres" does not exist.

But that postgres=# \password postgres@implicit_files run fine.

Also after sudo -u postgres createuser -s postgres the first variant also work.

Upvotes: 0

bcb
bcb

Reputation: 2078

I needed to unset $PGUSER:

$ unset PGUSER
$ createuser -s postgres

Upvotes: 23

ruzenhack
ruzenhack

Reputation: 973

First you need create a user:

sudo -u postgres createuser --superuser $USER

After you create a database:

sudo -u postgres createdb $USER

Change $USER to your system username.

You can see the the complete solution here.

Upvotes: 24

CesareoAguirre
CesareoAguirre

Reputation: 1607

createuser postgres --interactive

or make a superuser postgresl just with

createuser postgres -s

Upvotes: 97

Related Questions