FATAL: password authentication failed for user postgres (postgresql 11 with pgAdmin 4)

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

enter

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 doesnt work, you can:

  • Find your pg_hba.conf, usually located under C:Program FilesPostgreSQL9.1datapg_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 or notepad++ in your start menu, right click, choose Run as administrator, then use File->Open to open pg_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 isnt 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 prefer

  • Run 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 FilesPostgreSQL9.2data or C:Program Files (x86)PostgreSQL9.xdata, youll 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.

FATAL: password authentication failed for user postgres (postgresql 11 with pgAdmin 4)

Change the password of default use
ALTER USER postgres WITH PASSWORD new_password;

Leave a Reply

Your email address will not be published.