Узнать пароль postgres linux

Сбросить пароль на 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;

Источник

How to get a user’s password in PostgreSQL?

The password is encrypted according to the documentation:

The password is always stored encrypted in the system catalogs. The ENCRYPTED keyword has no effect, but is accepted for backwards compatibility. The method of encryption is determined by the configuration parameter password_encryption. If the presented password string is already in MD5-encrypted or SCRAM-encrypted format, then it is stored as-is regardless of password_encryption (since the system cannot decrypt the specified encrypted password string, to encrypt it in a different format). This allows reloading of encrypted passwords during dump/restore.

So, you won’t be able to get the original password of the normal user. What’s stored in the system is an encryption (e.g. MD5) of the original password. If you already know the password, its encrypted value will match. But you cannot get the password that generated the encrypted value. That’s the point of encrypting the password.

As admin, you can ALTER the user’s password, but cannot get the password from the encrypted version.

To get the encryptedvalue of the password for the user test , you can do:

SELECT * FROM pg_authid WHERE rolname='test'; 

SELECT rolname, rolpassword FROM pg_authid where rolname='test'; 

test | SCRAM-SHA-256$4096:O4JqOPBA9uDbytmsgvzcdA==$LN5pfo59nHr19nTDb1LX+21JK/UgQZoWDTFP8Tw2z3E=:Ciq8DY2pz8I2BxGGV2sq3VE6i1E30en0OdDD94Jlij4= 

Источник

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.

Источник

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 

Источник