Unable to run `mix ecto.create` without password, but `mix ecto.migrate` works #1207

Frost · 2016-01-18T15:51:45Z

I am running postgresql locally, and I allow my user to pretty much do everything with any local database, out of convenience.

The command line psql command automatically picks up my username, and tries to connect using that, and I would expect ecto to do the same if I don't provide one.

However, this does not seem to be the case.

Running mix ecto.create results in the following error:

** (Mix) The database for Phoenixtest.Repo couldn't be created, reason given: psql: fe_sendauth: no password supplied

However, if I create the database myself, using createdb my_app_dev, then running mix ecto.migrate works fine.

How to reproduce this:

configure some user to have full access to your local postgresql
create a new phoenix project (for example mix phoenix.new foo)
remove the database credentials from a config file (e.g. config/dev.exs)
run mix ecto.create and watch it fail
run createdb foo_dev
run mix ecto.migrate and it will reply with Already up

This means I cannot run mix test, because that task recreates and migrates the database, which in turn means that I have to have working database credentials, at least in my test config, if I ever want to be able to run mix test.

I have tried digging around in the ecto source code, and I noticed that migrations and storage creation seem to be run in different parts of the code, but I couldn't manage to find where to change anything to allow this.

The text was updated successfully, but these errors were encountered:

Frost · 2016-01-18T15:54:25Z

The versions I used in my repro was:

phoenix 1.1.2
phoenix_ecto 2.0.0
ecto 1.1.1
posgrex 0.10.0

Frost · 2016-01-18T16:01:13Z

Oh, nevermind.

My database config (pg_hba.conf) was apparently wrong.

For anyone else encountering this:

host all my_user 127.0.0.1/32 trust will not work
host all my_user localhost trust will work

josevalim · 2016-01-18T17:10:10Z

Also, Ecto master changed to use the same connections as ecto.migrate instead of using psql exactly because of those issues. So we hope this won't be a blocker to others soon. :D

Sam-esien · 2016-10-26T04:00:48Z

I am having the same problem please.

03:59:37.098 [error] GenServer #PID<0.2959.0> terminating
** (DBConnection.ConnectionError) tcp connect: connection refused - :econnrefused
    (db_connection) lib/db_connection/connection.ex:148: DBConnection.Connection.connect/2
    (connection) lib/connection.ex:622: Connection.enter_connect/5
    (stdlib) proc_lib.erl:247: :proc_lib.init_p_do_apply/3
Last message: nil
State: Postgrex.Protocol
** (Mix) The database for Hello.Repo couldn't be created: an exception was raised:
    ** (DBConnection.ConnectionError) tcp connect: connection refused - :econnrefused
        (db_connection) lib/db_connection/connection.ex:148: DBConnection.Connection.connect/2
        (connection) lib/connection.ex:622: Connection.enter_connect/5
        (stdlib) proc_lib.erl:247: :proc_lib.init_p_do_apply/3

what do I do. new to elixir and phoenix, new to web programming.

sevenseacat · 2016-10-26T05:06:25Z

It's not the same problem - it's a different error. Is your database server running?

Sam-esien · 2016-10-26T05:43:17Z

Tried running that and it only produced another string of errors.

hello mix phoenix.server
[error] Postgrex.Protocol (#PID<0.221.0>) failed to connect: ** (DBConnection.ConnectionError) tcp connect: connection refused - :econnrefused
[error] Postgrex.Protocol (#PID<0.215.0>) failed to connect: ** (DBConnection.ConnectionError) tcp connect: connection refused - :econnrefused
[error] Postgrex.Protocol (#PID<0.223.0>) failed to connect: ** (DBConnection.ConnectionError) tcp connect: connection refused - :econnrefused
[error] Postgrex.Protocol (#PID<0.214.0>) failed to connect: ** (DBConnection.ConnectionError) tcp connect: connection refused - :econnrefused
[error] Postgrex.Protocol (#PID<0.217.0>) failed to connect: ** (DBConnection.ConnectionError) tcp connect: connection refused - :econnrefused
[error] Postgrex.Protocol (#PID<0.216.0>) failed to connect: ** (DBConnection.ConnectionError) tcp connect: connection refused - :econnrefused
[error] Postgrex.Protocol (#PID<0.222.0>) failed to connect: ** (DBConnection.ConnectionError) tcp connect: connection refused - :econnrefused
[error] Postgrex.Protocol (#PID<0.220.0>) failed to connect: ** (DBConnection.ConnectionError) tcp connect: connection refused - :econnrefused
[error] Postgrex.Protocol (#PID<0.219.0>) failed to connect: ** (DBConnection.ConnectionError) tcp connect: connection refused - :econnrefused
[error] Postgrex.Protocol (#PID<0.218.0>) failed to connect: ** (DBConnection.ConnectionError) tcp connect: connection refused - :econnrefused
[info] Running Hello.Endpoint with Cowboy using http://localhost:4000
[error] Postgrex.Protocol (#PID<0.219.0>) failed to connect: ** (DBConnection.ConnectionError) tcp connect: connection refused - :econnrefused
[error] Postgrex.Protocol (#PID<0.217.0>) failed to connect: ** (DBConnection.ConnectionError) tcp connect: connection refused - :econnrefused
26 Oct 04:32:34 - info: compiled 6 files into 2 files, copied 3 in 1.8 sec
[error] Postgrex.Protocol (#PID<0.215.0>) failed to connect: ** (DBConnection.ConnectionError) tcp connect: connection refused - :econnrefused
[error] Postgrex.Protocol (#PID<0.222.0>) failed to connect: ** (DBConnection.ConnectionError) tcp connect: connection refused - :econnrefused
[error] Postgrex.Protocol (#PID<0.220.0>) failed to connect: ** (DBConnection.ConnectionError) tcp connect: connection refused - :econnrefused
[error] Postgrex.Protocol (#PID<0.216.0>) failed to connect: ** (DBConnection.ConnectionError) tcp connect: connection refused - :econnrefused
[error] Postgrex.Protocol (#PID<0.221.0>) failed to connect: ** (DBConnection.ConnectionError) tcp connect: connection refused - :econnrefused

Sam-esien · 2016-10-26T05:46:48Z

The book I'm reading says:

If you receive database errors when running mix ecto.create, double-check your Hello.Repo username and password values in config/dev.exs and match your system settings where necessary.

I did and saw the following inside the file:


use Mix.Config

# For development, we disable any cache and enable
# debugging and code reloading.
#
# The watchers configuration can be used to run external
# watchers to your application. For example, we use it
# with brunch.io to recompile .js and .css sources.
config :hello, Hello.Endpoint,
  http: [port: 4000],
  debug_errors: true,
  code_reloader: true,
  check_origin: false,
  watchers: [node: ["node_modules/brunch/bin/brunch", "watch", "--stdin",
                    cd: Path.expand("../", __DIR__)]]


# Watch static and templates for browser reloading.
config :hello, Hello.Endpoint,
  live_reload: [
    patterns: [
      ~r{priv/static/.*(js|css|png|jpeg|jpg|gif|svg)$},
      ~r{priv/gettext/.*(po)$},
      ~r{web/views/.*(ex)$},
      ~r{web/templates/.*(eex)$}
    ]
  ]

# Do not include metadata nor timestamps in development logs
config :logger, :console, format: "[$level] $message\n"

# Set a higher stacktrace during development. Avoid configuring such
# in production as building large stacktraces may be expensive.
config :phoenix, :stacktrace_depth, 20

# Configure your database
config :hello, Hello.Repo,
  adapter: Ecto.Adapters.Postgres,
  username: "postgres",
  password: "postgres",
  database: "hello_dev",
  hostname: "localhost",
  pool_size: 10

don't know what to make of it or how to fix the error via it

Sam-esien · 2016-10-26T05:50:13Z

don't understand what they mean by

match your system settings where necessary.

sevenseacat · 2016-10-26T05:50:34Z

Okay, but that doesn't answer the question. You're trying to connect to Postgres - is Postgres installed and running on your machine?

Sam-esien · 2016-10-26T05:54:48Z

yes it is

Sam-esien · 2016-10-26T06:05:35Z

Problem solved. Took your advice and just downloaded another version of postgres and installed. then ran the mix ecto.create again:

hello mix ecto.create                 
Compiling 12 files (.ex)
Generated hello app
The database for Hello.Repo has been created
➜  hello

Sam-esien · 2016-10-26T06:06:14Z

Thanks once again @sevenseacat your question was the answer.

josevalim · 2016-10-26T10:03:41Z

❤️ 💚 💙 💛 💜

jamesjahraus · 2016-11-10T01:56:46Z

stack overflow:
ecto-postgres-install-error-password-authentication-failed

links this issue #1207 causing much confusion

The solution to:

 mix ecto.create
18:16:06.298 [error] GenServer #PID<0.178.0> terminating
** (Postgrex.Error) FATAL (invalid_password): password authentication failed for user "postgres"

is:

sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'postgres';"
sudo /etc/init.d/postgresql restart

working now:

$ mix ecto.create
The database for Hello.Repo has been created

reference:
solve-mix-ecto-create-postgresql-password-issue-with-phoenix

aside:
messing with pg_hba.conf as stack overflow above sugested led me to red herring error (DBConnection.ConnectionError) tcp connect: connection refused - :econnrefused

reinstall could fix ** (Postgrex.Error) FATAL (invalid_password): password authentication failed for user "postgres"

I think the key is config/dev.exs needs to match vaild postgres user

I installed postgres on debian jessie with:
sudo apt-get install postgresql postgresql-client
psql --version
psql (PostgreSQL) 9.4.9

each new install I need to fix postgres user password to match config config/dev.exs to work with

sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'postgres';"
sudo /etc/init.d/postgresql restart

Frost · 2016-11-10T08:51:09Z

@jjahraus That's a great post describing solutions to some issues.

However, your solution to have the config/dev.exs username key match a valid postgres means that everyone who works on a project will have to have a postgres user with a specific username set up, since config/dev.exs tends to be checked into source control.

The reason I created this issue from the start is that I am lazy and didn't want to have to enter database credentials when running in dev, since I have configured my local postgresql to allow my user to access most of it... or so I thought, at least. As it turned out I had failed to configure pg_hba.conf properly.

Also, as @Gazler points out in the StackOverflow thread you linked to, in ecto 2.0, ecto won't use psql to create the database anymore, so my original issue here will no longer exist.

jamesjahraus · 2016-11-14T05:20:18Z

@Frost good points thanks for the info! Ill keep this in mind next time I setup a system, for now I am happy I can contiune working through Programming Phoenix book.

Fedreg · 2017-05-01T20:13:56Z

@jjahraus Thanks for that! Now I can proceed.

roberthopman · 2017-05-15T14:23:43Z

This worked for me:

in pg_hba.conf, I changed the 127.0.0.1/32 to localhost

Then in the terminal:

psql postgres
CREATE ROLE postgres;
ALTER ROLE postgres WITH LOGIN;
ALTER USER postgres CREATEDB;

m13m · 2017-06-13T00:39:53Z

$ mix ecto.create     
Compiling 12 files (.ex)
Generated hello app
** (Mix) The database for Hello.Repo couldn't be created: FATAL 28000 (invalid_authorization_specification): Ident authentication failed for user "postgres"

06:08:13.522 [error] GenServer #PID<0.312.0> terminating
** (Postgrex.Error) FATAL 28000 (invalid_authorization_specification): Ident authentication failed for user "postgres"
    (db_connection) lib/db_connection/connection.ex:148: DBConnection.Connection.connect/2
    (connection) lib/connection.ex:622: Connection.enter_connect/5
    (stdlib) proc_lib.erl:247: :proc_lib.init_p_do_apply/3
Last message: nil
State: Postgrex.Protocol

tried everything above
OS : Fedora 25 (Gnome)
@josevalim

Frost · 2017-06-13T07:41:08Z

@m13m That is probably because you are trying to use ident authentication and you are not running a local ident server on port 113. I am also running Fedora 25 (but the XFCE spin) and I don't seem to even have an ident server installed.

I am guessing that this happens because Ecto nowadays tries to connect using TCP instead of a local socket (as explained earlier in this thread) combined with you not running an ident server locally.

If you have netcat installed, you can run nc localhost 113 to see if you get any response. If it only prints "Ncat: connection refused." then there is no ident server running.

I think the solution to your problem is to either install and start an ident server or edit your pg_hba.conf to either change the authentication scheme for your "postgres" user to something other than ident, or to have Ecto use another user to connect and make sure that user has an authentication scheme that works on your machine (password should work).

Here's the PostgreSQL docs page on authentication methods if you want more reading: https://www.postgresql.org/docs/9.0/static/auth-methods.html

m13m · 2017-06-13T13:43:18Z

Thanks @Frost To Fix the above Issue
Operating System : Fedora 25(Gnome Env)

Replace ident with md5 the file /var/lib/pgsql/data/pg_hba.conf

# 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
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                peer
#host    replication     postgres        127.0.0.1/32            md5
#host    replication     postgres        ::1/128                 md5

as of writing, these commands were needed to unbork postgres/ecto: sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'postgres';" sudo /etc/init.d/postgresql restart see elixir-ecto/ecto#1207 jjahraus's 9 Nov 2016 comment

inoas · 2018-03-05T22:14:59Z

@roberthopman thank you :) that's the thing missing!

On Mac OS to get up and running fast (with say Phoenix) simply run:

brew install postgresql && brew services start postgresql && psql postgres

then type:

CREATE ROLE postgres;
ALTER ROLE postgres WITH LOGIN;
ALTER USER postgres CREATEDB;

then CTRL & D
then mix ecto.create

GettyOrawo · 2018-11-13T09:44:49Z

@jjahraus your answer deserves a medal

ks1venberg · 2020-01-19T20:13:27Z

Thanks @Frost To Fix the above Issue
Operating System : Fedora 25(Gnome Env)

Replace ident with md5 the file /var/lib/pgsql/data/pg_hba.conf

@m13m thank you too!
At first I`ve created alter user postgres, as described above,
and then using "md5" method instead of "ident" has solved the trouble. Thank you all guys!
Main stack is:
postgresql 11.6
elixir 1.9
otp 21.2.3

GunnarPDX · 2020-04-24T09:04:35Z

Getting the same issue.

I'm currently on macOS Catalina with Postgres 12.2, elixir 1.10.2, phoenix 1.5.1
I tried reinstalling a couple of other Postgres versions as well going as far back as 9.6 thinking the issue could be related to postgrex dependencies, and made sure to recreate the 'postgres' user each time.

Here is my error message:

01:20:16.327 [error] GenServer #PID<0.352.0> terminating
** (Postgrex.Error) FATAL 28P01 (invalid_password) password authentication failed for user "postgres"
    (db_connection 2.2.2) lib/db_connection/connection.ex:87: DBConnection.Connection.connect/2
    (connection 1.0.4) lib/connection.ex:622: Connection.enter_connect/5
    (stdlib 3.12.1) proc_lib.erl:249: :proc_lib.init_p_do_apply/3
Last message: nil
State: Postgrex.Protocol
** (Mix) The database for MyApp.Repo couldn't be created: killed

I have the 'postgres' user setup w/password set to 'postgres' aswell and this is present when I run \du:
postgres | Superuser, Create role, Create DB | {}

dev.exs is set up like this:

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

pg_hba.conf looks like this:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust

I tried changing 127.0.0.1/32 and ::1/128 to localhost, but got the same issue. I'm totally stumped.

Frost · 2020-04-24T09:55:00Z

Getting the same issue.

I'm currently on macOS Catalina with Postgres 12.2, elixir 1.10.2, phoenix 1.5.1
I tried reinstalling a couple of other Postgres versions as well going as far back as 9.6 thinking the issue could be related to postgrex dependencies, and made sure to recreate the 'postgres' user each time.

Here is my error message:
01:20:16.327 [error] GenServer #PID<0.352.0> terminating
** (Postgrex.Error) FATAL 28P01 (invalid_password) password authentication failed for user "postgres"
    (db_connection 2.2.2) lib/db_connection/connection.ex:87: DBConnection.Connection.connect/2
    (connection 1.0.4) lib/connection.ex:622: Connection.enter_connect/5
    (stdlib 3.12.1) proc_lib.erl:249: :proc_lib.init_p_do_apply/3
Last message: nil
State: Postgrex.Protocol
** (Mix) The database for MyApp.Repo couldn't be created: killed
I have the 'postgres' user setup w/password set to 'postgres' aswell and this is present when I run \du:
postgres | Superuser, Create role, Create DB | {}

dev.exs is set up like this:
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
pg_hba.conf looks like this:
# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust
I tried changing 127.0.0.1/32 and ::1/128 to localhost, but got the same issue. I'm totally stumped.

Usually, there is one line above that part of pg_hba.conf as well, and on my Linux installs, it looks like this:

local   all             postgres                                     peer

Are you able to log in to your local postgresql instance with the postgres user and postgres as password? The command for that should be something like psql --user postgres --password=postgres postgres.

I am guessing that will not work either, since your error message has (invalid_password) in it.

So I'm guessing that if you want to use the postgres user for authenticating, you either need to run your Elixir app as the postgres user locally, or set the password for the postgres accordingly.

If you want to just want any local account to access the database, try adding a line like this instead:

host    all             all             localhost            trust

This is pretty much what I did in my own first reply to this issue as well, except that I specified that I only trust my own user. If you do that, you shouldn't need the password field in the config at all.

GunnarPDX · 2020-04-24T20:13:07Z

tried both at the top of pg_hba.conf:

local   all             postgtres                               peer
host    all             all             localhost               trust

Also, running psql --user postgres --password postgres lets me sign in using 'postgres' as the password and it works totally fine.

SOLVED
I just noticed that I got the same error message when I stopped Postgres and ran mix ecto.create so it looks like ecto was connecting to a different instance of Postgres that I unknowingly had running in the background.

I checked what was running on port 5432
lsof -i :5432
and then killed the instance.
kill -9 [PID_NUMBER]
Once I did that and restarted postgres I was able to succesfully run mix ecto.create

GermaVinsmoke · 2020-08-30T17:19:55Z

I checked in SQL Shell provided by PostgreSQL. The default username is -> postgres and the password was set at the time of PostgreSQL installation.
I used the same password in dev.exs file and it worked.

scratchoo · 2021-09-15T12:24:09Z

weird that such issue still exist in 2021

Here is what worked for us

create a user with a password (without password it won't work)

CREATE USER yourusername WITH SUPERUSER PASSWORD 'yourpassword';

in case you have already a user created, you can alter the password like so:

ALTER USER yourusername WITH PASSWORD 'yournewpassword';

grand superuser privileges to the user:

ALTER USER yourusername WITH SUPERUSER;

Now go and run:

mix ecto.migrate

Have fun!

Frost closed this as completed Jan 18, 2016

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Unable to run `mix ecto.create` without password, but `mix ecto.migrate` works #1207

Unable to run `mix ecto.create` without password, but `mix ecto.migrate` works #1207

Frost commented Jan 18, 2016

Frost commented Jan 18, 2016

Frost commented Jan 18, 2016

josevalim commented Jan 18, 2016

Sam-esien commented Oct 26, 2016

sevenseacat commented Oct 26, 2016

Sam-esien commented Oct 26, 2016

Sam-esien commented Oct 26, 2016

Sam-esien commented Oct 26, 2016

sevenseacat commented Oct 26, 2016

Sam-esien commented Oct 26, 2016

Sam-esien commented Oct 26, 2016

Sam-esien commented Oct 26, 2016

josevalim commented Oct 26, 2016

jamesjahraus commented Nov 10, 2016 •

edited

Frost commented Nov 10, 2016

jamesjahraus commented Nov 14, 2016

Fedreg commented May 1, 2017

roberthopman commented May 15, 2017

m13m commented Jun 13, 2017

Frost commented Jun 13, 2017

m13m commented Jun 13, 2017 •

edited

inoas commented Mar 5, 2018 •

edited

GettyOrawo commented Nov 13, 2018

ks1venberg commented Jan 19, 2020

GunnarPDX commented Apr 24, 2020

Frost commented Apr 24, 2020

GunnarPDX commented Apr 24, 2020 •

edited

GermaVinsmoke commented Aug 30, 2020

scratchoo commented Sep 15, 2021 •

edited

Unable to run mix ecto.create without password, but mix ecto.migrate works #1207

Unable to run mix ecto.create without password, but mix ecto.migrate works #1207

Comments

Frost commented Jan 18, 2016

Frost commented Jan 18, 2016

Frost commented Jan 18, 2016

josevalim commented Jan 18, 2016

Sam-esien commented Oct 26, 2016

sevenseacat commented Oct 26, 2016

Sam-esien commented Oct 26, 2016

Sam-esien commented Oct 26, 2016

Sam-esien commented Oct 26, 2016

sevenseacat commented Oct 26, 2016

Sam-esien commented Oct 26, 2016

Sam-esien commented Oct 26, 2016

Sam-esien commented Oct 26, 2016

josevalim commented Oct 26, 2016

jamesjahraus commented Nov 10, 2016 • edited

Frost commented Nov 10, 2016

jamesjahraus commented Nov 14, 2016

Fedreg commented May 1, 2017

roberthopman commented May 15, 2017

m13m commented Jun 13, 2017

Frost commented Jun 13, 2017

m13m commented Jun 13, 2017 • edited

inoas commented Mar 5, 2018 • edited

GettyOrawo commented Nov 13, 2018

ks1venberg commented Jan 19, 2020

GunnarPDX commented Apr 24, 2020

Frost commented Apr 24, 2020

GunnarPDX commented Apr 24, 2020 • edited

GermaVinsmoke commented Aug 30, 2020

scratchoo commented Sep 15, 2021 • edited

Unable to run `mix ecto.create` without password, but `mix ecto.migrate` works #1207

Unable to run `mix ecto.create` without password, but `mix ecto.migrate` works #1207

jamesjahraus commented Nov 10, 2016 •

edited

m13m commented Jun 13, 2017 •

edited

inoas commented Mar 5, 2018 •

edited

GunnarPDX commented Apr 24, 2020 •

edited

scratchoo commented Sep 15, 2021 •

edited