Сбросить пароль пользователя postgres linux

I forgot the password I entered during PostgreSQL installation

I either forgot or mistyped (during the installation) the password to the default user of PostgreSQL. I can’t seem to be able to run it, and I get the following error:

psql: FATAL: password authentication failed for user "hisham" hisham-agil: hisham$ psql 

Is there a way to reset the password or how do I create a new user with superuser privileges? I am new to PostgreSQL and just installed it for the first time. I am trying to use it with Ruby on Rails and I am running Mac OS X v10.7 (Lion).

I cannot comment on answers yet, so have to do it this way. I did what SaiyanGirl said, however I still needed to enter a password, which was ‘postgres’ to log in, then I could reset the password

16 Answers 16

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.

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

this answer did NOT work for me, after I do step 3, the service won´t start, I don´t know, maybe it´s the OS(W8), it just won´t.

Keep in mind order of entries in pg_hba.conf is important. If you add «local all all trust» to the end of the file, it’ll not work as you expect, as previous records will be matched first. So put that at the top of the file to have what you expect.

If you’re on windows with this error, keep scrolling to @SaiyanGirl ‘s answer. Just modfiy the METHOD columns of the existing entries to ‘trust’, then change it back when you’re done

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 

# psql -U postgres -W Password for user postgres: psql: FATAL: Peer authentication failed for user "postgres" 

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

This doesn’t work because PgAdmin still asks for the current password. I reset to ‘trust’ and restarted PgAdmin. Still can’t reset without typing in the CURRENT password.

Interesting note: Running PostgreSQL Server 13 on Windows, even after making the aforementioned changes, the server will still prompt for a password when trying to connect (when using pgAdmin). You can just hit «OK» (w/o specifying a password) and the server will let you in. I did not anticipate being prompted.

Just to add to the above interesting note, I had to click «Cancel»; clicking «Okay» just got another demand for a password. And whenever I tried to actually do anything, I got a further demand, so it was not much help.

For Windows (what has helped me):

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 

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 

Источник

Сбросить пароль на PostgreSQL в Debian

Очень часто при появлении нового системного администратора, есть сервера, к которым нет доступа. Пароль от сервера есть у предыдущего администратора, но говорить он его по какой-то причине отказывается. Так произошло и у нас. Есть сервер PostgreSQL. Он работает, а пароля от суперпользователя ни у кого нет.

В PostgreSQL можно с легкостью все восстановить.

В любой базе данных есть режим так называемого «локального доступа». К примеру, в MySQL восстановить пароль можно так.

Перевести PostgreSQL в режим «локального доступа»

В Debian все конфигурационные файлы PostgreSQL находится в директории /etc/postgresql/9.6/main/, где 9.6 — это версия. У Вас она, возможно, будет другой.

sudo nano /etc/postgresql/9.6/main/pg_hba.conf

Комментируем одну строку и ниже добавляем другую:

# local all postgres peer local all postgres trust

sudo service postrgresql restart

Далее заходим под пользователем без пароля:

Чтобы вернуть все как было, нужно выполнить все шаги в обратном порядке.

Первое знакомство с «новым» сервером PostgreSQL

Посмотреть список всех БД

\l Список баз данных Имя | Владелец | Кодировка | LC_COLLATE | LC_CTYPE | Права доступа -----------+----------+-----------+-------------+-------------+----------------------- CB | postgres | UTF8 | ru_RU.UTF-8 | ru_RU.UTF-8 | postgres | postgres | UTF8 | ru_RU.UTF-8 | ru_RU.UTF-8 | template0 | postgres | UTF8 | ru_RU.UTF-8 | ru_RU.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | ru_RU.UTF-8 | ru_RU.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres (4 строки)

В листинге выше «боевая» база данных – это CB.

Посмотреть список пользователей

select * from pg_user; usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls | passwd | valuntil | useconfig ----------+----------+-------------+----------+---------+--------------+----------+----------+----------- postgres | 10 | t | t | t | t | ******** | | (1 строка)

Меняем пароль пользователю postgres

ALTER USER postgres WITH PASSWORD 'new_password';

Добавляем нового пользователя в Postgre

Для дальнейшего администрирования, необходимо иметь полный доступ к базам данных PostgreSQL. Для этого правильнее будет создать свою учетную запись.

CREATE USER user WITH PASSWORD 'myPassword'; CREATE ROLE

Добавляем права на доступ к базе данных. В нашем примере это база CB. Добавляем нашему пользователю доступ:

GRANT ALL PRIVILEGES ON DATABASE "CB" to user; GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO user;

Если требуется права суперпользователя, то:

ALTER USER user WITH SUPERUSER;

Источник

PostgreSQL: Resetting password of PostgreSQL on Ubuntu [closed]

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:

# Database administrative login by Unix domain socket local all postgres peer 

(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:

ALTER USER postgres PASSWORD 'newpassword'; 

In this command, postgres is the name of a superuser. If the user whose password is forgotten was ritesh , the command would be:

ALTER USER ritesh PASSWORD 'newpassword'; 

Keep in mind that you need to type postgres with a single S at the end

If leaving the password in clear text in the history of commands or the server log is a problem, psql provides an interactive meta-command to avoid that, as an alternative to ALTER USER . PASSWORD :

It asks for the password with a double blind input, then hashes it according to the password_encryption setting and issue the ALTER USER command to the server with the hashed version of the password, instead of the clear text version.

Источник

Reset the password of the PostgreSQL superuser

This article describes the detailed steps to reset the password of the PostgreSQL superuser.

In PostgreSQL, postgres is the superuser. If you have forgotten the password of postgres , you can reset it by the following steps.

1. Locate the configuration file pg_hba.conf for the PostgreSQL database server. On Windows, the configuration files for the PostgreSQL database server are located in the data directory of the PostgreSQL installation directory, for example: C:\Program Files\PostgreSQL\14\data . On Linux, the configuration file for the PostgreSQL database server is located at /etc/postgresql/14/main/pg_hba.conf .
2. Back up the configuration file before modifying it so that you can restore it later.

cp pg_hba.conf pg_hba.conf.bak 

local all all peer # 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. local replication all peer host replication all 127.0.0.1/32 trust host replication all ::1/128 trust

ALTER USER postgres WITH PASSWORD 'new_password'; 

Conclusion

This article explains the detailed steps to reset the password of superuser postgres .

Источник

How to reset your forgotten password in PostgreSQL

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:

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 

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

sudo systemctl restart postgresql 

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/

Источник