PgAdmin on Ubuntu 20.04 FATAL: password authentication failed for user

user_5815945 0
Viewed 3438

I've installed Postgres and PGAdmin4 on Ubuntu 20.04, but I'm not able to create a server and connect to the database

enter image description here

As I have seen in several solutions, I changed the method to md5 on my pg_hba.conf file

# Database administrative login by Unix domain socket
local   all             postgres                                md5

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

I have as well connected to psql, created new users, alter password and restart the server by running sudo systemctl restart postgresql

                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Any ideas?

pgadmin postgresql
3 Answers

Run the following commands:

sudo -i -u postgres

Input your sudo user password

psql
ALTER USER postgres PASSWORD 'new_password';
\q
exit

Now change config file to use this new password:

sudo subl /etc/postgresql/13/main/pg_hba.conf

(subl means subliime text, you can use any other text editor).

Edit this part of the file: change peer to md5

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# "local" is for Unix domain socket connections only
local   all             all                                     peer

restart the postgres service

Run this command to sign into postgres using the newly created password:

psql -U postgres
user_117579920
user_51910050

  1. First we have to get inside postgres by using

    sudo -u postgres psql postgres

  2. Enter password for user postgres

Now the terminal will be like this:

postgres=#

  1. Now enter the given line after postgres=# CREATE USER username WITH PASSWORD 'your password'; (put your password inside quotes('') and don't forget semicolon(;)
user_194234110
Related