I thought the database creates the default user postgres are created automatically, why would it fail
authentication. The password according to the config files is postgres
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”.
May be it may help to add i have tried three postgresql databases, i.e v11, v12 and now v14
without success. its been super stressful. I have purged all the others to download v14.
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?):
# 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
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.
Actually after downloading v14,i checked etc/postgre/14/main and both
pg_hba.cong and pg_ident.conf could only be accessed by root user.
I had to chmod 777 both.
I accidentally upgraded my OS which broke everything especially when
i realised most programs i had in /opt file where inaccessible only to find
out the upgrade had changed permissions arbitrarily (thats how it seemed
to me) to the /opt file and probably others random directories too.
What is that linux command that shows the permissions of the files in
the path?
The upgrade may have messed the config clusters. Am not an expert i don’t know where
the config clusters are or what am supposed to with them.
You have offered some valuable info, but am not sure what to next. Can i deleted my pg_hba.conf
and replace it with your snippet? What course of action can i take?
PostgreSQL runs in “clusters”. I’d suggest you familiarise yourself a little with appropriate pg_* commands. Starting probably with pg_lsclusters:
$ pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file
12 main 5432 online postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log
I’d start with checking that the DB cluster is running correctly, possibly verifying log files for potential abnormalities. If nothing suspicious there, I would try to create a new “ROLE” with appropriate privileges (basically CREATEDB privilege). Here an extract from my setup used for development:
$ psql myapp_dev
psql (12.8 (Ubuntu 12.8-0ubuntu0.20.04.1))
Type "help" for help.
myapp_dev=> \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
silverdr | Create DB | {}
myapp_dev=>
I am so used to always add own, non-superuser “ROLE” that I wouldn’t think of that. BUT - I recall the OP wrote he was able to use the password (from command line I presume) for the postgres user.
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.
I was getting this error when trying to access docker-based postgres from locally compiled phoenix app. The issue was the presence of an another running postgres service (locally on windows). Stopping local postgres resolved the issue.
OS: Debian 11.6, DB Version: postgres (PostgreSQL) 13.9 (Debian 13.9-0+deb11u1)
I first wrote sudo -u postgres psql then wrote alter role postgres with encrypted password 'postgres' and received a prompt that roles were changed.
I created a new project by doing mix phx.new hello_db
I received the error when I was doing mix ecto.create
The exact error was how the OP started this thread but today when I tried to create a new project, I am receiving a ‘DB Connection refused error’, I checked out other threads on the forum and they said my postgres service might not be running but I did check using sudo systemctl status postgres and it was active.
Well, then I can’t really help. As discussed here and in a few other places - the default installation of PostgreSQL does not have password set for the “Superuser” postgres, while the default Phoenix dev.exs configuration file expects it to have - example below
This is solved either by adding password to postgres “ROLE” or by creating and using a “non-superuser” role (with password) for your development purposes and adapting the dev.exs section responsible for connecting to the database. The latter is my preferred approach.
What I’d suggest checking is your dev.exs and whether your Phoenix application and your psql connect to the same PostgreSQL instance. Might you be having more than one PostgreSQL somewhere around your machine(s)?
Having a password set for the postgres account is a necessary step, but not the only one you need to take on Debian based distro (if memory serves). pg_hba.conf also needs to be configured to allow you to authenticate with a password. I didn’t see that discussed so it might yet be part of the problem.
By default PostgreSQL only listens on localhost though from what I can see that probably won’t be an issue for you. If it becomes a problem, it’s worthwhile being aware of the listen_addresses configuration point in postgresql.conf.
