psql: FATAL: role "postgres" does not exist
MacosPostgresqlTerminalMacos Problem Overview
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!
Macos Solutions
Solution 1 - Macos
NOTE: If you installed postgres using homebrew, see the comment from @user3402754 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.
Solution 2 - Macos
For MAC:
- Install Homebrew
brew install postgres
initdb /usr/local/var/postgres
/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.- 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
Solution 3 - Macos
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.
Solution 4 - Macos
createuser postgres --interactive
or make a superuser postgresl just with
createuser postgres -s
Solution 5 - Macos
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.
Solution 6 - Macos
If you installed postgres from brew, run this in your terminal :
/usr/local/opt/postgres/bin/createuser -s postgres
Solution 7 - Macos
And if you are here in 2022 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
Solution 8 - Macos
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.
Solution 9 - Macos
I needed to unset $PGUSER
:
$ unset PGUSER
$ createuser -s postgres
Solution 10 - Macos
For me, this code worked:
/Applications/Postgres.app/Contents/Versions/9.4/bin/createuser -s postgres
it came from here: http://talk.growstuff.org/t/fatal-role-postgres-does-not-exist/216/4
Solution 11 - Macos
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
Solution 12 - Macos
Running this on the command line should fix it
/Applications/Postgres.app/Contents/Versions/9.4/bin/createdb <Mac OSX Username Here>
Solution 13 - Macos
This worked for me
createuser -s postgres
note: I'm using mac catalina
Solution 14 - Macos
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.
Solution 15 - Macos
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.
Solution 16 - Macos
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.
Solution 17 - Macos
This is the only one that fixed it for me :
createuser -s -U $USER
Solution 18 - Macos
Dropping the postgres
database doesn't really matter. This database is initially empty and its purpose is simply for the postgres
user to have a kind of "home" to connect to, should it need one.
Still you may recreate it with the SQL command CREATE DATABASE postgres;
Note that the tutorial mentioned in the question is not written with postgres.app
in mind.
Contrary to PostgreSQL for Unix in general, postgres.app
tries to look like a normal application as opposed to a service that would be run by a dedicated postgres
user having different privileges than your normal user. postgres.app
is run and managed by your own account.
So instead of this command: sudo -u postgres psql -U postgres
, it would be more in the spirit of postgres.app to just issue: psql
, which automatically connects to a database matching your users's name, and with a db account of the same name that happens to be superuser, so it can do anything permissions-wise.
Solution 19 - Macos
For what it is worth, i have ubuntu and many packages installed and it went in conflict with it.
For me the right answer was:
sudo -i -u postgres-xc
psql
Solution 20 - Macos
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.
Solution 21 - Macos
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
Solution 22 - Macos
I don't think that sudo is needed here because psql -l returns a list of databases. This tells me that initdb was run under the user's current user, not under the postgres user.
You can just:
psql
And continue the tutorial.
I would suggest A.H's general points of creating the postgres user and db because many applications may expect this to exist.
A brief explanation:
PostgreSQL will not run with administrative access to the operating system. Instead it runs with an ordinary user, and in order to support peer authentication (asking the OS who is trying to connect) it creates a user and db with the user that runs the initialization process. In this case it was your normal user.
Solution 23 - Macos
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.
Solution 24 - Macos
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
Solution 25 - Macos
I became stuck on this issue having executed brew services stop postgresql
the day prior.
The day following: brew services start postgresql
would not work. This is because as is shown when you install using homebrew. postgresql uses a launchd ... which loads when your computer is powered on.
resolution:brew services start postgresql
Restart your computer.
Solution 26 - Macos
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.
Solution 27 - Macos
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