Сброс пароля в PostgreSQL
Забыли пароль учетной записи postgres в PostgreSQL? Выполнить сброс не сложно. Для этого необходимо выполнить пару манипуляций.
1. Правим файл pg_hba.conf
Находим файл в папке Data директории установки PostgreSQL. В Windows путь выглядит примерно так c:\Program Files\PostgreSQL\9.2.4-1.1C\data\
В этом файле нужно найти такие строчки
# IPv4 local connections:
host all all 127.0.0.1/32 md5
host all all 0.0.0.0/0 md5
# IPv6 local connections:
host all all ::1/128 md5
Меняем md5 на trust.
2. Удаляем файл pgpass.conf
В Windows этот файл находится в c:\Users\Administrator\AppData\Roaming\postgresql\
Здесь хранится старый пароль от PostgreSQL. Простое изменение хранимого здесь пароля мне не помогло. Поэтому я его просто удалил.
3. Меняем пароль в pgAdmin
Запускаем pgAdmin и нам предлагается ввести пароль. Если отметить галочку сохранить, то пароль будет сохранен в pgpass.conf и больше программой запрашиваться не будет.
Чтобы обеспечить безопасность использования паролей необходимо вернуть алгоритм шифрования md5. Для этого в файле pg_hba.conf параметр trust обратно меняем на md5.
Для подключения на локальном компьютере к PostgreSQL с помощью psql, pg_dump в локальных адресах IPv4 127.0.0.1/32 и IPv6 ::1/128 значение trust нужно оставить.
3 thoughts on “ Сброс пароля в PostgreSQL ”
Ты не сменил и не сбросил пароль. Ты отключил все механизмы защиты, поэтому он теперь пускает не с новым паролем, а вообще с ЛЮБЫМ. Строка trust как раз и отключает проверку пароля. А файл pgpass.conf нужен для того что бы утилитка pgAdmin могла туда сохранить пароль и не спрашивать его больше. Именно поэтому он храниться в твоем профиле, что бы никто другой не мог туда зяглянуть.
Пароль пользователя меняется командой ALTER USER user_name WITH PASSWORD ‘new_password’;
Удали статью, а то кучи начинающих «АДМИНОВ» понаоткрывают голую жопу по твоим советам.
Прежде чем писать такой комментарий
Строка trust как раз и отключает проверку пароля.
..внимательней читайте статью, там написано
параметр trust обратно меняем на md5
Про файл pgpass.conf тоже в статье сказано.
Так что с голой жопой останутся только такие же невнимательные как ты.
Глохни, петух. Как я смогу изменить пароль, если не могу на сервер зайти без пароля, придурок!
Resetting password of PostgreSQL on Ubuntu [closed]
Want to improve this question? Update the question so it’s on-topic for Stack Overflow.
Closed 7 years ago .
In Ubuntu, I installed PostgreSQL database and created a superuser for the server.
If I forgot the password of the postgresql superuser, how can I reset it (the password) for that user?
I tried uninstalling it and then installing it again but the previously created superuser is retained.
1 Answer 1
Assuming you’re the administrator of the machine, Ubuntu has granted you the right to sudo to run any command as any user.
Also assuming you did not restrict the rights in the pg_hba.conf file (in the /etc/postgresql/9.1/main directory), it should contain this line as the first rule:
(About the file location: 9.1 is the major postgres version and main the name of your «cluster». It will differ if using a newer version of postgres or non-default names. Use the pg_lsclusters command to obtain this information for your version/system).
Anyway, if the pg_hba.conf file does not have that line, edit the file, add it, and reload the service with sudo service postgresql reload .
Then you should be able to log in with psql as the postgres superuser with this shell command:
Once inside psql, issue the SQL command:
In this command, postgres is the name of a superuser. If the user whose password is forgotten was ritesh , the command would be:
Keep in mind that you need to type postgres with a single S at the end
Сброс пароля в PostgreSQL на Windows платформах
Забыли пароль учетной записи postgres в PostgreSQL? Выполнить сброс не сложно. Для этого необходимо выполнить пару манипуляций.
1. Правим файл pg_hba.conf
Находим файл в папке Data директории установки PostgreSQL. В Windows путь выглядит примерно так c:\Program Files\PostgreSQL\9.2.4-1.1C\data\
В этом файле нужно найти такие строчки
Меняем md5 на trust.
2. Удаляем файл pgpass.conf
В Windows этот файл находится в c:\Users\Administrator\AppData\Roaming\postgresql\
Здесь хранится старый пароль от PostgreSQL. Простое изменение хранимого здесь пароля мне не помогло. Поэтому я его просто удалил.
3. Меняем пароль в pgAdmin
Запускаем pgAdmin и нам предлагается ввести пароль. Если отметить галочку сохранить, то пароль будет сохранен в pgpass.conf и больше программой запрашиваться не будет.
Чтобы обеспечить безопасность использования паролей необходимо вернуть алгоритм шифрования md5. Для этого в файле pg_hba.conf параметр trust обратно меняем на md5.
Для подключения на локальном компьютере к PostgreSQL с помощью psql, pg_dump в локальных адресах IPv4 127.0.0.1/32 и IPv6 ::1/128 значение trust нужно оставить.
I forgot the password I entered during postgres installation
I either forgot or mistyped (during the installation) the password to the default user of Postgres. I can’t seem to be able to run it and I get the following error:
Is there anyway to reset the password or how do I create a new user with superuser privileges?
I am new to Postgres and just installed it for the first time. I am trying to use it with Rails and I am running Mac OS X Lion.
13 Answers 13
find the file pg_hba.conf — it may be located, for example in /etc/postgresql-9.1/pg_hba.conf .
cp pg_hba.conf pg_hba.conf-backup
place the following line (as either the first uncommented line, or as the only one):
For all occurrence of below (local and host) , exepct replication section if you don’t have any it has to be changed as follow ,no MD5 or Peer autehication should be present.
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
local all all trust
host all all 127.0.0.1/32 trust
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 -h 127.0.0.1 -U postgres
(note that with the first command you will not always be connected with local host)
Reset password (‘replace my_user_name with postgres since you are resetting postgres user)
ALTER USER my_user_name with password ‘my_secure_password’;
Restore the old pg_hba.conf as it is very dangerous to keep around
cp pg_hba.conf-backup pg_hba.conf
restart the server, in order to run with the safe pg_hba.conf
sudo /etc/init.d/postgresql restart
When connecting to postgres from command line, don’t forget to add -h localhost as command line parameter. If not, postgres 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.
Working with -h localhost :
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, is to open the file and change the METHOD from md5 to trust :
Then, using pgAdmin III, I logged in using no password and changed user postgres’ password by going to File -> Change Password
Just a note, on Linux You can simply run sudo su — postgres to become the postgres user and from there change what required using psql.
Edit the file /etc/postgresql/ /main/pg_hba.conf and find the following line:
Edit the line and change md5 at the end to trust and save the file
Reload the postgresql service
This will load the configuration files. Now you can modify the postgres user by logging into the psql shell
Update the postgres user’s password
Edit the file /etc/postgresql/ /main/pg_hba.conf and change trust back to md5 and save the file
Reload the postgresql service
Verify that the password change is working
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.
Adding the answer for Windows User for the latest postgres version (>10),
Go to your postgres installation location, and search for pg_hba.conf , you will find it in ..\postgres\data\pg_hba.conf
Open that file with notepad, find this line,
Change the method from md5 to trust,
Now go to your SQL Shell(PSQL) and leave everything blank,
It will not ask for password this time, and you will be logged in,
Now run this line, ALTER USER yourusername WITH SUPERUSER
Now you can leave the shell with \q
Again go to the file pg_hba.conf and change METHOD from trust to md5 again, and save it.
Now login with your new user and password and you can check \du for its attributes.
For Windows installation, a Windows user is created. And «psql» use this user for connection to the port. If you change the PostgreSQL user’s password, it won’t change the Windows one. The commandline juste below works only if you have access to commandline.
Instead you could use Windows GUI application «c:\Windows\system32\lusrmgr.exe». This app manage users created by Windows. So you can now modify the password.
What I did to resolve the same problem was:
Open pg_hba.conf file with gedit editor from the terminal:
It will ask for password. Enter your admin login password. This will open gedit with the file. Paste the following line:
Save and close it. Close the terminal and open it again and run this command:
You will now enter the psql console. Now change the password by entering this:
If it says user does not exist then instead of ALTER use CREATE .
Lastly, remove that certain line you pasted in pg_hba and save it.
If you are in windows you can just run
and login in postgres with postgres/postgres as user/password
The file .pgpass in a user’s home directory or the file referenced by PGPASSFILE can contain passwords to be used if the connection requires a password (and no password has been specified otherwise). On Microsoft Windows the file is named %APPDATA%\postgresql\pgpass.conf (where %APPDATA% refers to the Application Data subdirectory in the user’s profile).
This file should contain lines of the following format:
(You can add a reminder comment to the file by copying the line above and preceding it with #.) Each of the first four fields can be a literal value, or *, which matches anything. The password field from the first line that matches the current connection parameters will be used. (Therefore, put more-specific entries first when you are using wildcards.) If an entry needs to contain : or \, escape this character with . A host name of localhost matches both TCP (host name localhost) and Unix domain socket (pghost empty or the default socket directory) connections coming from the local machine. In a standby server, a database name of replication matches streaming replication connections made to the master server. The database field is of limited usefulness because users have the same password for all databases in the same cluster.
On Unix systems, the permissions on .pgpass must disallow any access to world or group; achieve this by the command chmod 0600
/.pgpass. If the permissions are less strict than this, the file will be ignored. On Microsoft Windows, it is assumed that the file is stored in a directory that is secure, so no special permissions check is made.