FATAL: password authentication failed for user "postgres" (postgresql 11 with pgAdmin 4)
The default authentication mode for PostgreSQL is set to ident.
You can access your pgpass.conf via pgAdmin -> Files -> open pgpass.conf
That will give you the path of pgpass.conf
at the bottom of the window (official documentation).
After knowing the location, you can open this file and edit it to your liking.
If that doesn't work, you can:
Find your
pg_hba.conf
, usually located underC:\Program Files\PostgreSQL\9.1\data\pg_hba.conf
If necessary, set the permissions on it so that you can modify it. Your user account might not be able to do so until you use the security tab in the properties dialog to give yourself that right by using an admin override.
Alternately, find
notepad
ornotepad++
in your start menu, right click, choose "Run as administrator", then useFile->Open
to openpg_hba.conf
that way.Edit it to set the "host" line for user "postgres" on host "127.0.0.1/32" to "trust". You can add the line if it isn't there; just insert
host all postgres 127.0.0.1/32 trust
before any other lines. (You can ignore comments, lines beginning with #).Restart the PostgreSQL service from the Services control panel (
start->run->services.msc
)Connect using
psql
or pgAdmin4 or whatever you preferRun
ALTER USER postgres PASSWORD 'fooBarEatsBarFoodBareFoot'
Remove the line you added to pg_hba.conf or change it back
Restart PostgreSQL again to bring the changes to effect.
Here is an example of the pg_hba.conf
file (METHOD is already set to trust):
# TYPE DATABASE USER ADDRESS METHOD# IPv4 local connections:host all all 127.0.0.1/32 trust# IPv6 local connections:host all all ::1/128 trust
NOTE: Remember to change the METHOD back to md5
or other auth-methods listed here after changing your password (as stated above).
For Windows variant - I too experienced this nasty bug because of pgAdmin for my Windows x64 install of version 9.2. It left my production paralyzed.
In folder C:\Program Files\PostgreSQL\9.2\data or C:\Program Files (x86)\PostgreSQL\9.x\data, you'll find the pg_hba.conf text file.
Find the following lines:
# TYPE DATABASE USER ADDRESS METHOD# IPv4 local connections:host all all 127.0.0.1/32 md5# IPv6 local connections:host all all ::1/128 md5
and change METHOD md5 to "trust" like this:
# TYPE DATABASE USER ADDRESS METHOD# IPv4 local connections:host all all 127.0.0.1/32 trust# IPv6 local connections:host all all ::1/128 trust
From Windows>Run type "services.msc" and enter find the right PostgreSQL instance and restart it.
Your DB security is now blown wide open! Heed the warning to return it back to md5 after changing the user password expiry time to say year 2099 for all the relevant users.
Note: CREATE USER is the same as CREATE ROLE except that it implies LOGIN.
$ psql postgrespostgres=# create user postgres with superuser password 'postgres';