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.

OS: debian 10
password: postgres
(spelling mistake was mine)

//==========the pg_hba.conf relevant snippet ========================//

“local” is for Unix domain socket connections only

local all all peer

IPv4 local connections:

host all all 127.0.0.1/32 scram-sha-256

IPv6 local connections:

host all all ::1/128 scram-sha-256

Allow replication connections from localhost, by a user with the

replication privilege.

local replication all peer
host replication all 127.0.0.1/32 scram-sha-256
host replication all ::1/128 scram-sha-256

//=====================================================================//

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?):

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.

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?

Thanks alot

what I use is a shell alias for ls -alF

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=> 

1. My permissions dont look too bad..i chmod 777 pg_hba.conf and pg_ident.conf though,
i dont think its an issue..

drwxr-xr-x 2 postgres postgres 4096 Nov 10 16:52 ./
drwxr-xr-x 3 postgres postgres 4096 Nov 10 16:51 ../
-rw-r–r-- 1 postgres postgres 315 Nov 10 16:52 environment
-rw-r–r-- 1 postgres postgres 143 Nov 10 16:52 pg_ctl.conf
-rwxrwxrwx 1 postgres postgres 5002 Nov 10 16:52 pg_hba.conf*
-rwxrwxrwx 1 postgres postgres 1636 Nov 10 16:52 pg_ident.conf*
-rw-r–r-- 1 postgres postgres 28974 Nov 10 16:52 postgresql.conf
-rw-r–r-- 1 postgres postgres 317 Nov 10 16:52 start.conf

2. I already created a user and dbase manually from postgres console…

                               List of roles

Role name | Attributes | Member of
-----------±-----------------------------------------------------------±----------
pato | Superuser, Create role, Create DB | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Let me learn a little about clusters, may be i will get a solution.

All keep you posted.
Thanks alot

user postgres has no password after installation, so if you want to authenticate as this superuser, you need to
set it.

alter role postgres with encrypted password 'postgres';

777 on these two files makes no sense, set it to 700

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.