Postgres will not allow alter role superuser and my default user isn't superuser

Question

I cannot connect to Postgres using Elixir:

** (Mix) The database for PhoenixChat.Repo couldn't be created: FATAL 28P01 (invalid_password): password authentication failed for user "postgres"

00:08:59.053 [error] GenServer #PID<0.3214.0> terminating
** (Postgrex.Error) FATAL 28P01 (invalid_password): password 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

I cannot understand how a database could allow users to be made without one being a superuser. From my usual user, it does not allow giving superuser role:

$ psql -U thisone --password
Password for user thisone: 
psql: FATAL:  Peer authentication failed for user "thisone"

$ psql -U fakeuser
psql: FATAL:  Peer authentication failed for user "fakeuser"

cchilders=> alter user postgres superuser;
ERROR:  must be superuser to alter superusers
cchilders=> select rolname from pg_roles;
  rolname  
-----------
 postgres
 cchilders
 fakeuser
 thisone
(4 rows)

$ psql -U postgres --password
Password for user postgres: 
psql: FATAL:  Peer authentication failed for user "postgres"

How can I give a user superuser in Postgres if there is no superuser?

there's no other passwords except what I've tried. should I reinstall — codyc4321, Apr 29 '17 at 05:32
Correct answer `sudo -u postgres psql` http://stackoverflow.com/questions/2942485/psql-fatal-ident-authentication-failed-for-user-postgres?rq=1 — codyc4321, Apr 29 '17 at 05:42

score 2 · Accepted Answer · answered Apr 29 '17 at 08:31

psql: FATAL: Peer authentication failed for user "thisone" and same error for other user means, you try to connect on socket with different os user (eg. os user is cchilders and you try psql -U postgres), while peer means you have to be logged in same OS user. For example, to success in psql -U thisone you should before sudo su - thisone. If you dont have such OS user, you cant connect with peer authentication. So either change in hba_file peer to trust for passwordless or md5 for password auth to postgres not being same os user...

For you sudo -u postgres psql worked, because you have os user postgres (which is not the case with osx for example)..

And lastly, you list users, not checking if they are superusers. You should either \du metacommand with psql, or addrolsuper` column, so it would be:

select rolname from pg_roles where rolsuper;

Postgres will not allow alter role superuser and my default user isn't superuser

1 Answers1