Homebrew Postgresql role "postgres" does not exist

Phoenix Forum Questions / Help
,
1

(I have about 10 books of Phoenix and Elixir that I bought, so this ongoing headache right at the start is important for me to solve. I’ve been out of programming for several years.)

I tried 2 other ways of installing postgres. Nothing seems to solve my problem.

On home-brew I used this: brew install postgresql@17

And brew services start postgresql@17

hello/config/dev.exs

config :hello, Hello.Repo,
  username: "postgres",
  password: "postgres",
  hostname: "localhost",
  database: "hello_dev",
  stacktrace: true,
  show_sensitive_data_on_connection_error: true,
  pool_size: 10

Simply trying the hello app from the Phoenixframework.com website.

I get this:

**** (Mix) The database for Hello.Repo couldn't be created: killed**

with:

14:12:26.847 [error] :gen_statem #PID<0.336.0> terminating

** (Postgrex.Error) FATAL 28000 (invalid_authorization_specification) role "postgres" does not exist

(db_connection 2.7.0) lib/db_connection/connection.ex:104: DBConnection.Connection.handle_event/4

(stdlib 6.2) gen_statem.erl:3737: :gen_statem.loop_state_callback/11

(stdlib 6.2) proc_lib.erl:329: :proc_lib.init_p_do_apply/3

Queue: [internal: {:connect, :init}]

Postponed: []

State: Postgrex.Protocol

Callback mode: :handle_event_function, state_enter: false

**** (Mix) The database for Hello.Repo couldn't be created: killed**

Also role postgres does not exist I remember I used to encounter this in the past too.

Ok while typing: Elixir Phoenix and "role postgres does not exist" · Strange Leaflet

Worked! Happy Day!

2

I’m glad you got it working!

Just wanted to mention for MacOS there is Postgres.app https://postgresapp.com as an option. I used the homebrew Postgres a few years ago but ran into difficulties getting the PostGIS extension installed and stable through updates.

After going down a major rabbit hole I found this app which makes it dead simple to run multiple versions of Postgres including PostGIS simultaneously on different port numbers in development.

And of course many prefer using Docker Compose to run PG in development isolating the dev environment but I don’t mind having system-wide Postgres.app running supporting all my apps.

2 Likes
3

I’m pretty sure you can just delete the username and password from the repo config and you’ll be fine.

4

I’m curious whether you searched for the error (Postgrex.Error) FATAL 28000 (invalid_authorization_specification) role "postgres" does not exist as the first result for me is a thread on this forum:

Screenshot 2025-03-09 at 22.44.25
Screenshot 2025-03-09 at 22.44.251200×504 48.4 KB

For anyone coming to this thread in future this is how I install Postgres on a new machine:

# Install with:

brew install postgresql

# Then to start Postgres and on startup, run:

brew services start postgresql@14

# Then create your initial db:

createdb

# Then:

psql
CREATE ROLE postgres LOGIN CREATEDB;

# That will create your Postgres user (use CTRL Z to exit).
1 Like