PostgreSQL 사용자 비밀번호를 변경하는 방법은 무엇입니까?

PostgreSQL 사용자 비밀번호를 변경하는 방법은 무엇입니까?

---

PostgreSQL 사용자의 비밀번호는 어떻게 변경합니까?

---

비밀번호가 적은 로그인의 경우 :

sudo -u user_name psql db_name

비밀번호를 잊은 경우 재설정하려면 :

ALTER USER user_name WITH PASSWORD 'new_password';

---

그런 다음 다음을 입력하십시오.

$ sudo -u postgres psql

그때:

\password postgres

그런 다음 종료합니다 psql.

\q

그래도 작동하지 않으면 인증을 다시 구성하십시오.

편집 /etc/postgresql/9.1/main/pg_hba.conf(경로가 다름) 및 변경 :

    local   all             all                                     peer

에:

    local   all             all                                     md5

그런 다음 서버를 다시 시작하십시오.

$ sudo service postgresql restart

---

사용자의 비밀번호를 암호화 할 수 있으며 암호화해야합니다.

ALTER USER username WITH ENCRYPTED PASSWORD 'password';

---

비밀번호를 변경하는 가장 좋은 방법은 다음을 사용하는 것입니다.

\password

Postgres 콘솔에서.

출처:

이 명령으로 암호화되지 않은 암호를 지정할 때는주의해야합니다. 암호는 일반 텍스트로 서버에 전송되며 클라이언트의 명령 기록 또는 서버 로그에도 기록 될 수 있습니다. psql에는 일반 텍스트 암호를 노출하지 않고 역할의 암호를 변경하는 데 사용할 수있는 \ password 명령이 있습니다.

에서 https://www.postgresql.org/docs/9.0/static/sql-alterrole.html .

---

Linux 명령 줄을 사용하여 암호를 변경하려면 다음을 사용하십시오.

sudo -u <user_name> psql -c "ALTER USER <user_name> PASSWORD '<new_password>';"

---

Postgresql 구성으로 이동하여 pg_hba.conf를 편집하십시오.

sudo vim /etc/postgresql/9.3/main/pg_hba.conf

그런 다음이 줄을 변경하십시오.

Database administrative login by Unix domain socket
local      all              postgres                                md5

받는 사람 :

Database administrative login by Unix domain socket
local   all             postgres                                peer

그런 다음 SUDO 명령을 통해 PostgreSQL 서비스를 다시 시작한 다음

psql -U postgres

이제 입력되고 Postgresql 터미널이 표시됩니다.

그런 다음 입력

\password

Postgres 기본 사용자의 새 암호를 입력하고 암호를 다시 성공적으로 변경 한 후 pg_hba.conf로 이동하여 변경 사항을 "md5"로 되돌립니다.

이제 다음으로 로그인됩니다.

psql -U postgres

새 비밀번호로.

모두 문제가 있으면 알려주세요.

---

postgres 사용자에 대한 새 암호를 요청하려면 (명령에 표시하지 않고) :

sudo -u postgres psql -c "\password"

---

이것은 사용자 이름을 바꾸는 방법을 찾고 있었을 때 Google의 첫 번째 결과였습니다.

ALTER USER <username> WITH PASSWORD '<new_password>';  -- change password
ALTER USER <old_username> RENAME TO <new_username>;    -- rename user

A couple of other commands helpful for user management:

CREATE USER <username> PASSWORD '<password>' IN GROUP <group>;
DROP USER <username>;

Move user to another group

ALTER GROUP <old_group> DROP USER <username>;
ALTER GROUP <new_group> ADD USER <username>;

---

Configuration that I've got on my server was customized a lot and I managed to change password only after I set trust authentication in the pg_hba.conf file:

local   all   all   trust

Don't forget to change this back to password or md5

---

To Change Password

 sudo -u postgres psql

then

\password postgres

now enter New Password and Confirm

then \q to exit

---

For my case on Ubuntu 14.04 installed with postgres 10.3. I need to follow the following steps

• su - postgres to switch user to postgres
• psql to enter postgres shell
• \password then enter your password
• \q to quit the shell session

• Then you switch back to root by executing exit and configure your pg_hba.conf (mine is at /etc/postgresql/10/main/pg_hba.conf) by making sure you have the following line

  local all postgres md5

• Restart your postgres service by service postgresql restart
• Now switch to postgres user and enter postgres shell again. It will prompt you with password.

---

use this:

\password

enter the new password you want for that user and then confirm it. If you don't remember the password and you want to change it, you can log in as postgres and then use this:

ALTER USER 'the username' WITH PASSWORD 'the new password';

---

Similar to other answers in syntax but it should be known that you can also pass a md5 of the password so you are not transmitting a plain text password.

Here are a few scenarios of unintended consequences of altering a users password in plain text.

1. If you do not have SSL and are modifying remotely you are transmitting the plain text password across the network.
2. If you have your logging configuration set to log DDL Statements log_statement = ddl or higher, then your plain text password will show up in your error logs.
   1. If you are not protecting these logs its a problem.
   2. If you collect these logs/ETL them and display them where others have access they could end up seeing this password, etc.
   3. If you allow a user to manage their password, they are unknowingly revealing a password to an admin or low level employee tasked with reviewing logs.

With that said here is how we can alter a user's password by building an md5 of the password.

• Postgres when hash a password as md5, salts the password with the user name then prepends the text "md5" to the resulting hash.

• ex: "md5"+md5(password + username)

• In bash:

    ~$ echo -n "passwordStringUserName" | md5sum | awk '{print "md5"$1}'
    md5d6a35858d61d85e4a82ab1fb044aba9d

• In PowerShell:

    [PSCredential] $Credential = Get-Credential

    $StringBuilder = New-Object System.Text.StringBuilder

    $null = $StringBuilder.Append('md5');

    [System.Security.Cryptography.HashAlgorithm]::Create('md5').ComputeHash([System.Text.Encoding]::ASCII.GetBytes(((ConvertFrom-SecureStringToPlainText -SecureString $Credential.Password) + $Credential.UserName))) | ForEach-Object {
        $null = $StringBuilder.Append($_.ToString("x2"))
    }

    $StringBuilder.ToString();

    ## OUTPUT
    md5d6a35858d61d85e4a82ab1fb044aba9d

• So finally our ALTER USER command will look like

    ALTER USER UserName WITH PASSWORD 'md5d6a35858d61d85e4a82ab1fb044aba9d';

• Relevant Links (Note I will only link to the latest versions of the docs for older it changes some but md5 is still support a ways back.)
• create role

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

• configuration setting for password_encryption
• postgres password authentication doc
• building postgres password md5

참고URL : https://stackoverflow.com/questions/12720967/how-to-change-postgresql-user-password