FWIW - I just tried to set a quick test project up and I recalled the discussion here so I thought I’d check it. Therefore I left both user and password at the default “postgres” and encountered the same issue/errors OP described in his first post. The solution in my case:

silverdr$ sudo -u postgres psql
psql (12.9 (Ubuntu 12.9-0ubuntu0.20.04.1))
Type "help" for help.

postgres=# \password postgres
Enter new password: 
Enter it again: 
postgres=# \q
silverdr$ mix ecto.create
Compiling 29 files (.ex)
Generated testapp app
The database for Testapp.Repo has been created

IOW - altering the ROLE “postgres” to have password “postgres” helped immediately. This is basically the same what @inko mentioned: if you want to use the “postgres” role, give it the password you have cofigured in the Elixir application config.

You need to check what “ROLES” your Postgres installation has available, and possibly create a suitable one. PostgreSQL docs say:

In order to bootstrap the database system, a freshly initialized system always contains one predefined role. This role is always a “superuser”, and by default (unless altered when running initdb ) it will have the same name as the operating system user that initialized the database cluster. Customarily, this role will be named postgres .

This means it is neither guaranteed for every PostgreSQL installation to have the postgres “role” nor it is guaranteed for it to have “postgres” as password, even if it exists.

I have confirmed thru \du that indeed a user postgres exists, i created (i am not sure if actually ecto was the one supposed to create) a database with the same name in the dev.exs in the config directory and logged in using the password postgress and the log in was successful.

I returned to my elixir/phoenix app and run mix ecto.create and the same error “password authentication failed for user postgres”.

Any other hint or solution is welcome.

“postgress” or “postgres”? Make sure the spelling is correct.

What environment is that? OS? Showing the config section and the relevant parts of pg_hba.conf can help too.

Yes, mix ecto.create should create the database for you.

Here’s my config that works on a similar (Debian based) system. I am on v12 here but things should work well with earlier versions too. Certainly no need to go v14 (are you sure you didn’t mess anything up with your cluster’s config when mixing the versions?):

Config files/permissions

$ ll /etc/postgresql/12/main/
total 64
drwxr-xr-x 3 postgres postgres  4096 Sep 30 07:51 ./
drwxr-xr-x 3 postgres postgres  4096 Sep 30 07:42 ../
drwxr-xr-x 2 postgres postgres  4096 Sep 30 07:42 conf.d/
-rw-r--r-- 1 postgres postgres   315 Sep 30 07:42 environment
-rw-r--r-- 1 postgres postgres   143 Sep 30 07:42 pg_ctl.conf
-rw-r----- 1 postgres postgres  4933 Sep 30 07:42 pg_hba.conf
-rw-r----- 1 postgres postgres  1636 Sep 30 07:42 pg_ident.conf
-rw-r--r-- 1 postgres postgres 26864 Sep 30 07:42 postgresql.conf
-rw-r--r-- 1 postgres postgres   317 Sep 30 07:42 start.conf

pg_hba.conf

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

Example Phoenix app config:

# Configure your database
config :myapp, Myapp.Repo,
  username: "silverdr",
  password: "silverdr",
  database: "myapp_dev",
  hostname: "localhost",
  show_sensitive_data_on_connection_error: true,
  pool_size: 10
  [...]

Yes, I have my “system user” there - that’s what I typically do rather than running as “root” / “superuser” in Postgres terminology. But I believe it should work with “superuser” too. Yet - if everything else fails you could try adding your own user (“ROLE”) with appropriate password and privileges. In any case - that works (and always worked) for me flawlessly on a Debian based systems.