Забыл пароль от postgresql windows

asked Jun 1, 2012 at 7:14

1. Find the file pg_hba.conf. It may be located, for example, in /etc/postgresql-9.1/pg_hba.conf.

   cd /etc/postgresql-9.1/

2. Back it up

   cp pg_hba.conf pg_hba.conf-backup

3. Place the following line (as either the first uncommented line, or as the only one):

   For all occurrence of below (local and host) , except replication
   section if you don’t have any it has to be changed as follow ,no MD5
   or Peer authentication should be present.

   local all all trust

4. Restart your PostgreSQL server (e.g., on Linux:)

   sudo /etc/init.d/postgresql restart

   If the service (daemon) doesn’t start reporting in log file:

   local connections are not supported by this build

   you should change

   local all all trust

   to

   host all all 127.0.0.1/32 trust

5. You can now connect as any user. Connect as the superuser postgres (note, the superuser name may be different in your installation. In some systems it is called pgsql, for example.)

   psql -U postgres

   or

   psql -h 127.0.0.1 -U postgres

   (note that with the first command you will not always be connected with local host)

6. Reset the password (‘replace my_user_name with postgres since you are resetting the postgres user)

   ALTER USER my_user_name with password 'my_secure_password';

7. Restore the old pg_hba.conf file as it is very dangerous to keep around

   cp pg_hba.conf-backup pg_hba.conf

8. Restart the server, in order to run with the safe pg_hba.conf file

   sudo /etc/init.d/postgresql restart

Further reading about that pg_hba file: 19.1. The pg_hba.conf File (official documentation)

When connecting to PostgreSQL from the command line, don’t forget to add -h localhost as a command line parameter. If not, PostgreSQL will try to connect using PEER authentication mode.

The below shows a reset of the password, a failed login with PEER authentication and a successful login using a TCP connection.

# sudo -u postgres psql

could not change directory to "/root"
psql (9.1.11)
Type "help" for help.

postgres=# \password
Enter new password:
Enter it again:
postgres=# \q

Failing:

# psql -U postgres -W

Password for user postgres:
psql: FATAL:  Peer authentication failed for user "postgres"

Working with -h localhost:

# psql -U postgres -W  -h localhost

Password for user postgres:
psql (9.1.11)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

postgres=#

The pg_hba.conf (C:\Program Files\PostgreSQL\9.3\data) file has changed since these answers were given. What worked for me, in Windows, was to open the file and change the METHOD from md5 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

Then, using pgAdmin III, I logged in without using a password and changed user postgres‘s password by going to menu File → Change Password.

For Windows (what has helped me):

This is the document I am referring to: How can I reset a PostgreSQL password?

1. Open your cmd and go to C:\Program Files\PostgreSQL\12\data.
   This is usually the right path. You might have it stored somewhere else. Note that, if you have a different PostgreSQL version, there will be a different number. That doesn’t matter.

2. Find a pg_hba.conf file and copy it to somewhere else (that way you will have an unmodified version of this file, so you will be able to look at it after we make some changes)

3. Open the pg_hba.conf file (not the backup, but the original)

4. Find the multiple lines that start with host near the bottom of the file:

   host    all             all             127.0.0.1/32            md5
   
   host    all             all             ::1/128                 md5
   
   host    replication     all             127.0.0.1/32            md5
   
   host    replication     all             ::1/128                 md5
   

5. Replace md5 with trust:

   host    all             all             127.0.0.1/32            trust
   
   host    all             all             ::1/128                 trust
   
   host    replication     all             127.0.0.1/32            trust
   
   host    replication     all             ::1/128                 trust
   

6. Close this file

7. Go to your search bar on windows and open Services app. Find postgres and restart it.

   Picture of services app

8. Write cd.. in cmd and then cd bin. Your path should be C:\Program Files\PostgreSQL\12\bin

9. Enter: psql -U postgres -h localhost

10. Enter: ALTER USER postgres with password '<your new password>';Make sure that you include ; at the end
    “ALTER ROLE” should be displayed as an indication that the previous line was executed successfully

11. Open original pg_hba.conf file and change back from trust to md5

12. Restart the server with Services app as before

I was just having this problem on Windows 10 and the issue in my case was that I was just running psql and it was defaulting to trying to log in with my Windows username («Nathan»), but there was no PostgreSQL user with that name, and it wasn’t telling me that.

So the solution was to run psql -U postgres rather than just psql, and then the password I entered at installation worked.

answered Jun 26, 2019 at 21:29

1. Edit the file /etc/postgresql/<version>/main/pg_hba.conf and find the following line:

   local  all  postgres  md5
   

2. Edit the line and change md5 at the end to trust and save the file

3. Reload the postgresql service

   sudo service postgresql reload
   

4. This will load the configuration files. Now you can modify the postgres user by logging into the psql shell

   psql -U postgres
   

5. Update the postgres user’s password

   alter user postgres with password 'secure-passwd-here';
   

6. Edit the file /etc/postgresql/<version>/main/pg_hba.conf and change trust back to md5 and save the file

7. Reload the postgresql service

   sudo service postgresql reload
   

8. Verify that the password change is working

   psql -U postgres -W
   

answered Feb 3, 2019 at 13:40

answered Mar 16, 2020 at 15:22

For a Windows installation, a Windows user is created. And «psql» uses this user for connection to the port. If you change the PostgreSQL user’s password, it won’t change the Windows one.
The command line just below works only if you have access to the command line.

Instead, you could use the Windows GUI application «c:\Windows\system32\lusrmgr.exe». This application manages users created by Windows. So you can now modify the password.

answered Nov 14, 2017 at 12:08

sudo -u postgres psql

ALTER USER user_name WITH PASSWORD 'new_password';

If you are on Windows you can just run

net user postgres postgres

And log in in PostgreSQL with postgres/postgres as the user/password.

answered Jun 27, 2022 at 11:12

answered Nov 23, 2022 at 15:31

answered Feb 26, 2021 at 10:47

asked Jun 1, 2012 at 7:14

1. Find the file pg_hba.conf. It may be located, for example, in /etc/postgresql-9.1/pg_hba.conf.

   cd /etc/postgresql-9.1/

2. Back it up

   cp pg_hba.conf pg_hba.conf-backup

3. Place the following line (as either the first uncommented line, or as the only one):

   For all occurrence of below (local and host) , except replication
   section if you don’t have any it has to be changed as follow ,no MD5
   or Peer authentication should be present.

   local all all trust

4. Restart your PostgreSQL server (e.g., on Linux:)

   sudo /etc/init.d/postgresql restart

   If the service (daemon) doesn’t start reporting in log file:

   local connections are not supported by this build

   you should change

   local all all trust

   to

   host all all 127.0.0.1/32 trust

5. You can now connect as any user. Connect as the superuser postgres (note, the superuser name may be different in your installation. In some systems it is called pgsql, for example.)

   psql -U postgres

   or

   psql -h 127.0.0.1 -U postgres

   (note that with the first command you will not always be connected with local host)

6. Reset the password (‘replace my_user_name with postgres since you are resetting the postgres user)

   ALTER USER my_user_name with password 'my_secure_password';

7. Restore the old pg_hba.conf file as it is very dangerous to keep around

   cp pg_hba.conf-backup pg_hba.conf

8. Restart the server, in order to run with the safe pg_hba.conf file

   sudo /etc/init.d/postgresql restart

Further reading about that pg_hba file: 19.1. The pg_hba.conf File (official documentation)

When connecting to PostgreSQL from the command line, don’t forget to add -h localhost as a command line parameter. If not, PostgreSQL will try to connect using PEER authentication mode.

The below shows a reset of the password, a failed login with PEER authentication and a successful login using a TCP connection.

# sudo -u postgres psql

could not change directory to "/root"
psql (9.1.11)
Type "help" for help.

postgres=# \password
Enter new password:
Enter it again:
postgres=# \q

Failing:

# psql -U postgres -W

Password for user postgres:
psql: FATAL:  Peer authentication failed for user "postgres"

Working with -h localhost:

# psql -U postgres -W  -h localhost

Password for user postgres:
psql (9.1.11)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

postgres=#

The pg_hba.conf (C:\Program Files\PostgreSQL\9.3\data) file has changed since these answers were given. What worked for me, in Windows, was to open the file and change the METHOD from md5 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

Then, using pgAdmin III, I logged in without using a password and changed user postgres‘s password by going to menu File → Change Password.

For Windows (what has helped me):

This is the document I am referring to: How can I reset a PostgreSQL password?

1. Open your cmd and go to C:\Program Files\PostgreSQL\12\data.
   This is usually the right path. You might have it stored somewhere else. Note that, if you have a different PostgreSQL version, there will be a different number. That doesn’t matter.

2. Find a pg_hba.conf file and copy it to somewhere else (that way you will have an unmodified version of this file, so you will be able to look at it after we make some changes)

3. Open the pg_hba.conf file (not the backup, but the original)

4. Find the multiple lines that start with host near the bottom of the file:

   host    all             all             127.0.0.1/32            md5
   
   host    all             all             ::1/128                 md5
   
   host    replication     all             127.0.0.1/32            md5
   
   host    replication     all             ::1/128                 md5
   

5. Replace md5 with trust:

   host    all             all             127.0.0.1/32            trust
   
   host    all             all             ::1/128                 trust
   
   host    replication     all             127.0.0.1/32            trust
   
   host    replication     all             ::1/128                 trust
   

6. Close this file

7. Go to your search bar on windows and open Services app. Find postgres and restart it.

   Picture of services app

8. Write cd.. in cmd and then cd bin. Your path should be C:\Program Files\PostgreSQL\12\bin

9. Enter: psql -U postgres -h localhost

10. Enter: ALTER USER postgres with password '<your new password>';Make sure that you include ; at the end
    “ALTER ROLE” should be displayed as an indication that the previous line was executed successfully

11. Open original pg_hba.conf file and change back from trust to md5

12. Restart the server with Services app as before

I was just having this problem on Windows 10 and the issue in my case was that I was just running psql and it was defaulting to trying to log in with my Windows username («Nathan»), but there was no PostgreSQL user with that name, and it wasn’t telling me that.

So the solution was to run psql -U postgres rather than just psql, and then the password I entered at installation worked.

answered Jun 26, 2019 at 21:29

1. Edit the file /etc/postgresql/<version>/main/pg_hba.conf and find the following line:

   local  all  postgres  md5
   

2. Edit the line and change md5 at the end to trust and save the file

3. Reload the postgresql service

   sudo service postgresql reload
   

4. This will load the configuration files. Now you can modify the postgres user by logging into the psql shell

   psql -U postgres
   

5. Update the postgres user’s password

   alter user postgres with password 'secure-passwd-here';
   

6. Edit the file /etc/postgresql/<version>/main/pg_hba.conf and change trust back to md5 and save the file

7. Reload the postgresql service

   sudo service postgresql reload
   

8. Verify that the password change is working

   psql -U postgres -W
   

answered Feb 3, 2019 at 13:40

answered Mar 16, 2020 at 15:22

For a Windows installation, a Windows user is created. And «psql» uses this user for connection to the port. If you change the PostgreSQL user’s password, it won’t change the Windows one.
The command line just below works only if you have access to the command line.

Instead, you could use the Windows GUI application «c:\Windows\system32\lusrmgr.exe». This application manages users created by Windows. So you can now modify the password.

answered Nov 14, 2017 at 12:08

sudo -u postgres psql

ALTER USER user_name WITH PASSWORD 'new_password';

If you are on Windows you can just run

net user postgres postgres

And log in in PostgreSQL with postgres/postgres as the user/password.

answered Jun 27, 2022 at 11:12

answered Nov 23, 2022 at 15:31

answered Feb 26, 2021 at 10:47