FATAL: role “postgres” does not exist

💡 If you just want to know how to fix it, you should know that most probably during the creation of the database the DB superuser chosen was the OS username with which Postgres was installed. To fix it, you must create the postgres user with the following command: createuser -s postgres -U <os-username>. Read on if you want to know more.

After installing PostgreSQL in mac os x using brew, you may have encountered the next error when executing the psql command.

FATAL:  role "postgres" does not exist

According to the message, there’s no user named “postgres” in the DB. So, let’s try to create it with the createuser command installed together with “postgres”. Make sure the “postgres” username have admin permissions by providing the --superuser operator.

$ createuser --superuser postgres

But after pressing enter, to my surprise, the same error shows again.

Wait! What is happening here? How can I create a new user when the tools do not allow? Why the “postgres” user has not been created by default?

The investigation

Let’s take a step (or two) back.

Clearly, when postgres was installed something was missing: no “postgres” user has been created. So I feel curios about how the Postgres brew formula worked in the homebrew-core.

The version that interested me is the postgres 13. It is not a large file, it took me some minutes to figure it out what it does. Precisely the post_install function contained what I was looking for.

system "#{bin}/initdb", "--locale=C", "-E", "UTF-8", postgresql_datadir unless pg_version_exists?

initdb is the Postgres command that initiates the DB. Besides the pgdata argument, there’s no other hint about the user creation. So, I assumed the default user creation is taken care of by the initdb command.

Looking at the guts of initdb source. Right between the lines 3193 and 3195, we can appreciate if no username (--username operator) is provided, the one used by default is the current OS username with the get_id function.

effective_user = get_id();
if (!username)
		username = effective_user;

Obviously, as you might expect, my username is not postgres.

The fix

With this information at hand, it just took providing my username into createuser command to create the postgres username.

$ createuser -s postgres -U <os-username>

There you go. Error fixed.

A deeper insight

But, there’s still a pending question. If initdb assumes the OS user as the default DB superuser, why psql and createuser commands assume there is a “postgres” user? It seems there’s a gap here.

I checked the docs and look what I found in the initdb docs, under the --username operator.

Selects the user name of the database superuser. This defaults to the name of the effective user running initdb. It is really not important what the superuser’s name is, but one might choose to keep the customary name postgres, even if the operating system user’s name is different.

And, there’s also more info at the “First steps” article in the Postgres wiki:

typically initdb creates a table named “postgres” owned by user “current logged in user name” however, when you use pg_ctl it defaults to connecting as user “current user” to database “current user” so specify user postgres here so that it avoids confusion of having them named differently. $ /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data -U postgres

Therefore, the “postgres” username is just a convention in the Postgres community. And for obvious security reasons, it is up to us to create it.

Yet another question arises: Why does the psql command default to the user “postgres”? The reasoning behind this is that if no database name is specified, psql assumes the default is “postgres.” Additionally, if no username is provided, it automatically uses the same database name as the username.

I’m not quite sure about the database connection but this line in the psql codebase confirms the default database name is “postgres” when listing databases.

And here I get off. Now you know why “postgres” is the default username.