Resolving "FATAL: role 'postgres' does not exist" Error in PostgreSQL on macOS

Introduction

When setting up or troubleshooting a PostgreSQL installation, you might encounter an error like psql: FATAL: role "postgres" does not exist. This usually indicates that the default superuser role named "postgres" is missing. In this tutorial, we’ll explore how to resolve this issue on macOS, particularly when using Postgres.app or Homebrew.

Understanding Roles in PostgreSQL

In PostgreSQL, roles are similar to users and can own databases, manage permissions, and more. The postgres role is traditionally created during installation as a superuser. If it’s missing, some operations may fail because scripts or tutorials assume its existence.

Common Causes of the Error

Installation Method: Using different methods like Postgres.app or Homebrew can affect how roles are initialized.
Accidental Deletion: Dropping databases or roles can lead to this error if not correctly restored.
Configuration Differences: Variations in default settings between installation methods.

Steps to Resolve the Error

Method 1: Using Postgres.app on macOS

If you installed PostgreSQL using Postgres.app, follow these steps:

Open Terminal and navigate to the PostgreSQL binaries directory:
```
cd /Applications/Postgres.app/Contents/Versions/latest/bin/
```
Create the postgres Role:
Execute the following command to create a superuser role named postgres:
```
./createuser -s postgres
```
Verify Creation:
Launch psql and check for the postgres role:
```
psql -U your_user_name
\du
```
You should see postgres listed as a superuser.

Method 2: Using Homebrew on macOS

For those who installed PostgreSQL via Homebrew, follow these steps:

Install PostgreSQL (if not already installed):
```
brew install postgresql
```
Initialize the Database Cluster:
This step is crucial if you haven’t initialized it yet.
```
initdb /usr/local/var/postgres
```
Create the postgres Role:
Run this command to create a superuser named postgres:
```
brew postgresql services restart
createuser -s postgres
```
Start PostgreSQL Service:
Ensure the service is running:
```
brew services start postgresql
```
Verify Role Creation:
Log in to psql and check for the postgres role:
```
psql -U your_user_name
\du
```

Additional Tips

Automating Start on Boot: If you want PostgreSQL to start automatically, link it with macOS’s launch services (for Homebrew installations):
```
ln -sfv /usr/local/opt/postgresql/*.plist ~/Library/LaunchAgents
launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
```
Using Docker: If you’re running PostgreSQL in a Docker container, ensure environment variables are set for user credentials:
```
export PGUSER="youruser"
export PGHOST="127.0.0.1"
export PGPASSWORD="yourpassword"
```

Conclusion

By understanding the role structure of PostgreSQL and following the appropriate steps based on your installation method, you can resolve the FATAL: role "postgres" does not exist error efficiently. Whether using Postgres.app or Homebrew, creating the necessary superuser role will help you continue with database operations smoothly.