[macos] 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!

This question is related to macos postgresql terminal

The answer is


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.


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.


createuser postgres --interactive

or make a superuser postgresl just with

createuser postgres -s

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.


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


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.


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


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.


Running this on the command line should fix it

/Applications/Postgres.app/Contents/Versions/9.4/bin/createdb <Mac OSX Username Here>


I needed to unset $PGUSER:

$ unset PGUSER
$ createuser -s postgres

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

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

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.


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.


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

This is the only one that fixed it for me :

createuser -s -U $USER

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.


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.


Examples related to macos

Problems with installation of Google App Engine SDK for php in OS X dyld: Library not loaded: /usr/local/opt/openssl/lib/libssl.1.0.0.dylib dyld: Library not loaded: /usr/local/opt/icu4c/lib/libicui18n.62.dylib error running php after installing node with brew on Mac Could not install packages due to an EnvironmentError: [Errno 13] How do I install Java on Mac OSX allowing version switching? Git is not working after macOS Update (xcrun: error: invalid active developer path (/Library/Developer/CommandLineTools) Can't compile C program on a Mac after upgrade to Mojave You don't have write permissions for the /Library/Ruby/Gems/2.3.0 directory. (mac user) How can I install a previous version of Python 3 in macOS using homebrew? Could not install packages due to a "Environment error :[error 13]: permission denied : 'usr/local/bin/f2py'"

Examples related to postgresql

Subtracting 1 day from a timestamp date pgadmin4 : postgresql application server could not be contacted. Psql could not connect to server: No such file or directory, 5432 error? How to persist data in a dockerized postgres database using volumes input file appears to be a text format dump. Please use psql Postgres: check if array field contains value? Add timestamp column with default NOW() for new rows only Can't connect to Postgresql on port 5432 How to insert current datetime in postgresql insert query Connecting to Postgresql in a docker container from outside

Examples related to terminal

Git is not working after macOS Update (xcrun: error: invalid active developer path (/Library/Developer/CommandLineTools) Can't compile C program on a Mac after upgrade to Mojave Flutter command not found VSCode Change Default Terminal How to switch Python versions in Terminal? How to open the terminal in Atom? Color theme for VS Code integrated terminal How to edit a text file in my terminal How to open google chrome from terminal? Switch between python 2.7 and python 3.5 on Mac OS X