WSL+Postgres: Failed Authentication

Hello,

I cannot get postgres to work… I am using WSL2 with Ubuntu 20.04 on Windows 10. I have installed erlang and elixir via ASDF and launched a phoenix app without ecto successfully.

Now I want to set up postgres:

sudo apt install postgresql postgresql-contrib
sudo passwd postgres

For testing purposes I just set the password to ‘postgres’.

sudo service postgresql start

In my config/dev.exs I use:

config :my_app, MyApp.Repo,
  username: "postgres",
  password: "postgres",
  database: "my_app_dev",
  hostname: "localhost",
  show_sensitive_data_on_connection_error: true,
  pool_size: 10

mix ecto.create brings following error message:

19:01:48.679 [error] GenServer #PID<0.284.0> terminating
** (Postgrex.Error) FATAL 28P01 (invalid_password) password authentication failed for user "postgres"
    (db_connection 2.4.0) lib/db_connection/connection.ex:100: DBConnection.Connection.connect/2
    (connection 1.1.0) lib/connection.ex:622: Connection.enter_connect/5
    (stdlib 3.16) proc_lib.erl:226: :proc_lib.init_p_do_apply/3
Last message: nil
State: Postgrex.Protocol

19:01:48.696 [error] GenServer #PID<0.291.0> terminating
** (Postgrex.Error) FATAL 28P01 (invalid_password) password authentication failed for user "postgres"
    (db_connection 2.4.0) lib/db_connection/connection.ex:100: DBConnection.Connection.connect/2
    (connection 1.1.0) lib/connection.ex:622: Connection.enter_connect/5
    (stdlib 3.16) proc_lib.erl:226: :proc_lib.init_p_do_apply/3
Last message: nil
State: Postgrex.Protocol
** (Mix) The database for MyApp.Repo couldn't be created: killed

Please help.

can you run psql?

psql --version
psql (PostgreSQL) 12.8 (Ubuntu 12.8-0ubuntu0.20.04.1)

psql
psql: error: FATAL: role “nefcairon” does not exist

what I mean is can you run psql to connect to your database.
If you are on linux, (WSL counts), and only ever want to connect from local machine, the easiest way is to grant yourself a database role and connect through unix domain socket, and skip all the username/password crap. My config is just:

config :roastidious, Roastidious.Repo,
  database: "roastidious_dev",
  socket_dir: "/var/run/postgresql",
  show_sensitive_data_on_connection_error: true,
  pool_size: 10

It works now.

I had to create another role that matches my ubuntu user name:

sudo -u postgres psql
CREATE ROLE nefcairon WITH SUPERUSER CREATEDB CREATEROLE LOGIN ENCRYPTED PASSWORD '****';

und then use this username in the config/dev.exs

You could have always used postgres as a username, if your server was isolated enough to not care about this security omission.

Also had some trouble with postgres. After installation you still have to change the password in psql:

ALTER USER postgres WITH PASSWORD 'password';

After changing it and updating it in config/dev.exs ecto.create works :smiley:

1 Like