Как сбросить пароль на postgres windows - Доктор Windows

Забыли пароль учетной записи postgres в PostgreSQL? Выполнить сброс не сложно. Для этого необходимо выполнить пару манипуляций.

1. Правим файл pg_hba.conf

Находим файл в папке Data директории установки PostgreSQL. В Windows путь выглядит примерно так c:Program FilesPostgreSQL9.2.4-1.1Cdata

В этом файле нужно найти такие строчки

# 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:UsersAdministratorAppDataRoamingpostgresql

Здесь хранится старый пароль от 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 нужно оставить.

Источник

For a Windows user for the latest PostgreSQL version (greater than 10):

Go to your PostgreSQL installation location, and search for pg_hba.conf, you will find it in ..postgresdatapg_hba.conf.

Open that file with Notepad, and find this line:

# 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
#..

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
# ...

Now go to your SQL shell (PSQL) and leave everything blank,

Server [localhost]:
Database [postgres]:
Port [8000]:
Username [postgres]:

It will not ask for a 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 log in with your new user and password, and you can check du for its attributes.

Источник

1. Правим файл pg_hba.conf

Находим файл в папке Data директории установки PostgreSQL. В Windows путь выглядит примерно так c:Program FilesPostgreSQL9.2.4-1.1Cdata

В этом файле нужно найти такие строчки

# 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:UsersAdministratorAppDataRoamingpostgresql

3. Меняем пароль в pgAdmin

Спасибо софт-сетап

Join @AdmNtsRu on Telegram

Смотрите также:

Источник

For a Windows user for the latest PostgreSQL version (greater than 10):

Go to your PostgreSQL installation location, and search for pg_hba.conf, you will find it in ..postgresdatapg_hba.conf.

Open that file with Notepad, and find this line:

# 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
#..

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
# ...

Now go to your SQL shell (PSQL) and leave everything blank,

Server [localhost]:
Database [postgres]:
Port [8000]:
Username [postgres]:

It will not ask for a 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 log in with your new user and password, and you can check du for its attributes.

Источник

For a Windows user for the latest PostgreSQL version (greater than 10):

Go to your PostgreSQL installation location, and search for pg_hba.conf, you will find it in ..postgresdatapg_hba.conf.

Open that file with Notepad, and find this line:

# 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
#..

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
# ...

Now go to your SQL shell (PSQL) and leave everything blank,

Server [localhost]:
Database [postgres]:
Port [8000]:
Username [postgres]:

It will not ask for a 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 log in with your new user and password, and you can check du for its attributes.

Источник

Summary: in this tutorial, we will show you step by step how to reset the password of the postgres user in PostgreSQL.

For some reason, after installing PostgreSQL, you may forget the password of the postgres user. In this case, you need to know how to reset the password.

PostgreSQL uses the pg_hba.conf configuration file stored in the database data directory (e.g., C:Program FilesPostgreSQL12data on Windows) to control the client authentication. The hba in pg_hba.conf means host-based authentication.

To reset the password for the postgres user, you need to modify some parameters in this configuration file, login as postgres without a password, and reset the password.

The following steps show you how to reset a password for the postgres user:

Step 1. Backup the pg_hba.conf file by copying it to a different location or just rename it to pg_hba.conf.bk

Step 2. Edit the pg_dba.conf file and change all local connections from md5 to trust. By doing this, you can log in to the PostgreSQL database server without using a password.

# 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 # Allow replication connections from localhost, by a user with the # replication privilege. host replication all 127.0.0.1/32 trust host replication all ::1/128 trust

Code language: PHP (php)

Step 3. Restart the PostgreSQL server. If you are on Windows, you can restart the PostgreSQL from Services:

Or run the following command from the window terminal:

pg_ctl -D "C:Program FilesPostgreSQL12data" restart

Code language: JavaScript (javascript)

The "C:Program FilesPostgreSQL12data" is the data directory.

Step 4. Connect to PostgreSQL database server using any tool such as psql or pgAdmin:

psql -U postgres

PostgreSQL will not require a password to login.

Step 5. Execute the following command to set a new password for the postgres user.

postgres=# ALTER USER postgres WITH PASSWORD 'new_password';
Code language: SQL (Structured Query Language) (sql)

Step 6. Restore the pg_dba.conf file, restart the PostgreSQL database server and connect to the PostgreSQL database server with the new password.

In this tutorial, you have learned how to reset the password of the postgres user.

Was this tutorial helpful ?

Источник

(Note: Not much of this is relevant to readers using PostgreSQL 9.2 or above from the EDB installers, which now have a greatly simplified default install using the NETWORK SERVICE, though you can still configure other accounts).

I have used net user postgres postgres
to reset the password for my database but instead of a success message I am getting "System error 5 has occurred. Access is denied."

You’ve reset (or tried to reset) the service account password. PostgreSQL won’t run as Administrator for security reasons and the installer generally sets it up with a «postgres» user account in PostgreSQL 9.1 and older¹. On Windows you can’t start a service as a user without saving the password of the user in the registry, so that’s what the installer does.

If you change the password for the Windows user account postgres, the PostgreSQL service can no longer start. So don’t do that, you’ll have to fix the service configuration to store the updated password.

Thankfully I think another mistake prevented you from doing that. It looks like you’re probably running your command prompt without using «Run as Administrator» on an unprivileged Windows user account or a machine with UAC, so it isn’t running with the access permissions required to change the password for the postgres user.

Before you try to change that password, make sure it’s really what you want to do. What’s the problem you’re trying to solve here? Are you attempting to install a database update or something else that’s asking for the password for the postgres Windows user?

Most likely you’re just trying to log in to the database. For that, you use the (unfortunately completely unrelated) password stored in the database its self. Since you’ve lost/forgotten it you’ll have to reset it:

Find your pg_hba.conf, usually in 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 / 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 isn’t there; just insert:
```
host all postgres 127.0.0.1/32 trust
host all postgres ::1/128      trust # if IPv6 is in use
```
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 PgAdmin-III or whatever you prefer
ALTER USER postgres PASSWORD 'postgres'
remove the line you added to pg_hba.conf or change it back
restart PostgreSQL again.

See: How do I reset the postgres password for PostgreSQL on Windows?

^1. 9.2 now uses the NETWORKSERVICE account, which doesn’t require a password, so this problem goes away.

Источник

Забыли пароль учетной записи postgres в PostgreSQL? Выполнить сброс не сложно. Для этого необходимо выполнить пару манипуляций. Останавливаем службу Postgres в службах windows. Переходим в каталог C:Program FilesPostgreSQL9.5data и правим там файл pg_hba.conf:

# 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
# Allow replication connections from localhost, by a user with the
# replication privilege.
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust

Меняем md5 на trust.

Удаляем файл pgpass.conf. В Windows этот файл находится в С:UsersAdministratorAppDataRoamingpostgresql. Здесь хранится старый пароль от PostgreSQL.

Запускаем службу Postgres.
Запускаем cmd и вводим там команду:

В командной строке PG, которая появляется:

ALTER USER Postgres WITH PASSWORD '<newpassword>';

Сохраните это, набрав wq enter, чтобы выйти из запроса PG
Если возникает ошибка при вводе команды, что команда не определена добавляем путь в path C:Program FilesPostgreSQL9.5bin
Возможно, вы захотите вернуться к изменению MD5 → Trust позже в pg_hba.conf.
Если изменения не сработали, тогда еще раз перезапустите службу Postgres и можете заходить под новым паролем.

Источник

The moment you realize the problem

It’s a good practice even for your local DB server to provide a password for each user. PostgreSQL’s default user (who is also an admin) is postgres.

A standard method to get access to our DB is using psql command via terminal (I’m running Manjaro Linux). So, we type:

psql -U postgres

Enter fullscreen mode

Exit fullscreen mode

We enter our password and we get the message

Don’t Panic!

First of all, we must find the «PostgreSQL Client Authentication Configuration File», which has the name pg_hba.conf. In Manjaro, it lives in path /var/lib/postgres/data/pg_hba.conf. Be careful that it is required to have root permissions.

sudo nano /var/lib/postgres/data/pg_hba.conf

Enter fullscreen mode

Exit fullscreen mode

We change the md5 authentication in «local» connection to trust

local   all             all                                     trust

Enter fullscreen mode

Exit fullscreen mode

and we restart our server

sudo systemctl restart postgresql

Enter fullscreen mode

Exit fullscreen mode

Now PostgreSQL doesn’t ask a password and we are allowed to connent with user postgres

Next step is to reset the password

We exit psql, we turn back pg_hba.conf to it’s previous state (md5 authentication) and we restart the server. We are able to connent using our new password for user postgres.

Originally published at https://www.codingnotebook.eu/postgresql-reset-password/

Источник