Postgresql linux смена пароля

How can I change a PostgreSQL user password?

Confusingly, the (literal) name of the default user is «postgres». What is the default username and password for PostgreSQL?.

It’s worth noting that «postgres» is the default registered user. The default user used when running psql is the current shell user ( echo $USER ). To login to psql as «postgres» when the current shell user is not «postgres», we need to run psql as «postgres» with sudo -u postgres psql . Also, note that we can’t switch the current shell user to «postgres» since that account is locked by default.

26 Answers 26

To log in without a password:

sudo -u user_name psql db_name 

To reset the password if you have forgotten:

ALTER USER user_name WITH PASSWORD 'new_password'; 

off topic but if anyone looking for «how to change name of user» than do ALTER USER myuser RENAME TO newname; . for some reason google was pointing me here when I was googling that 🙂

Why are you using both » and ‘ quotes? I mean, there’s a difference, and in a DML query you have to use ‘ when dealing with strings, but is there a special reason to use both of them here?

The user is an object, not a string. Compare with ALTER TABLE «table_name» or even SELECT * FROM «table_name». You couldn’t use single quotes in these contexts with tables, and it’s the same with users/roles.

To change the PostgreSQL user’s password, follow these steps:

log in into the psql console:

postgres=# \password postgres Enter new password: postgres=# \q 

ALTER USER postgres PASSWORD ''; 

sudo -u postgres psql -c "ALTER USER postgres PASSWORD '';" 

Note:

If that does not work, reconfigure authentication by editing /etc/postgresql/9.1/main/pg_hba.conf (the path will differ) and change:

local all all peer # change this to md5 

local all all md5 # like this 

sudo service postgresql restart 

(on psql 9.2) if I type in \p , it gives me the password; if I type in \password postgres it gives the password and then warnings \p extra argument «assword» ignored; \p extra argument «postgres» ignored

You can and should have the users’ password encrypted:

ALTER USER username WITH ENCRYPTED PASSWORD 'password'; 

This keyword doesn’t matter for the current version. From postgresql.org/docs/current/static/sql-createrole.html The password is always stored encrypted in the system catalogs. The ENCRYPTED keyword has no effect, but is accepted for backwards compatibility.

Beware! @John29 comment is only true from Postgresql 10 and above. For all other versions the ENCRYPTED flag matters.

the unencrypted password is rememberd by psql history or shell. How to use hashed (ex. SHA1) password?

I believe the best way to change the password is simply to use:

Caution must be exercised when specifying an unencrypted password with this command. The password will be transmitted to the server in cleartext, and it might also be logged in the client’s command history or the server log. psql contains a command \password that can be used to change a role’s password without exposing the cleartext password.

Note: ALTER USER is an alias for ALTER ROLE

@Andy: yes, but psql didnt connected to DB for whatever reason. I´ve installed a new version with new passwords, now it is ok. Thank you

To change the password using the Linux command line, use:

sudo -u psql -c "ALTER USER PASSWORD '';" 

In bash, if HISTCONTROL is set to ignorespace or ignoreboth , then a command line starting with a space is not added to the history.

Now enter the new password and confirm.

Go to your PostgreSQL configuration and edit file 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 

Then restart the PostgreSQL service via the ‘sudo’ command. Then

You will be now entered and will see the PostgreSQL terminal.

And enter the new password for the PostgreSQL default user. After successfully changing the password again, go to the pg_hba.conf and revert the change to «md5».

Now you will be logged in as

It doesn’t work : user@user-NC10:~$ psql -U postgres psql: FATAL: Peer authentication failed for user «postgres»

Ok, Do another method sudo su — postgres psql You will enter the terminal and then change the password there, This is an alternate way for this. Let me know if this works for you or you need a full explanation

mm i have tried but I have another error:/usr/bin/psql: line 19: use: command not found /usr/bin/psql: line 21: use: command not found /usr/bin/psql: line 23: use: command not found /usr/bin/psql: line 24: use: command not found /usr/bin/psql: psql: line 26: syntax error near unexpected token $version,’ /usr/bin/psql: psql: line 26: my ($version, $cluster, $db, $port, $host);’ thanks for your help!

Setting up a password for the postgres role

You will get a prompt like the following:

Change password to PostgreSQL for user postgres

ALTER USER postgres WITH ENCRYPTED PASSWORD 'postgres'; 

You will get something as follows:

To do this we need to edit the pg_hba.conf file.

(Feel free to replace nano with an editor of your choice.)

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

Update in the pg_hba.conf file

Look for an uncommented line (a line that doesn’t start with #) that has the contents shown below. The spacing will be slightly different, but the words should be the same.

 local postgres postgres peer 

 local postgres postgres md5 

Now we need to restart PostgreSQL, so the changes take effect

sudo service postgresql restart 

To request a new password for the postgres user (without showing it in the command):

sudo -u postgres psql -c "\password" 

This was the first result on google, when I was looking how to rename a user, so:

ALTER USER WITH PASSWORD ''; -- change password ALTER USER RENAME TO ; -- rename user 

A couple of other commands helpful for user management:

CREATE USER PASSWORD '' IN GROUP ; DROP USER ; 

Move user to another group

ALTER GROUP DROP USER ; ALTER GROUP ADD USER ; 

Open pg_hba.conf file and change from md5 to peer .

Open cmd and type psql postgres postgres .

Then type \password to be prompted for a new password.

Refer to this Medium post for further information & granular steps.

While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. — From Review

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

Don’t forget to change this back to password or md5.

you also need to restart your postgres service for changes to take effect sudo systemctl restart postgresql.service

For my case on Ubuntu 14.04 (Trusty Tahr), installed with PostgreSQL 10.3: I need to follow the following steps

• su — postgres to switch the user to postgres
• psql to enter the PostgreSQL shell
• \password and 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 PostgreSQL service by service postgresql restart
• Now switch to the postgres user and enter the PostgreSQL shell again. It will prompt you for a password.

I don’t think you really need to restart the postgresql service after changing the password. I have been able to reset the password with restarting it. \password is the quickest way. Or else you need the ALTER USER command.

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'; 

On many systems, a user’s account often contains a period, or some sort of punctuation (user: john.smith, horise.johnson). In these cases, a modification will have to be made to the accepted answer above. The change requires the username to be double-quoted.

Example

ALTER USER "username.lastname" WITH PASSWORD 'password'; 

PostgreSQL is quite picky on when to use a ‘double quote’ and when to use a ‘single quote’. Typically, when providing a string, you would use a single quote.

This is similar to other answers in syntax, but it should be known that you can also pass the MD5 hash value 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.
3. If you are not protecting these logs, it’s a problem.
4. If you collect these logs/ETL them and display them where others have access, they could end up seeing this password, etc.
5. If you allow a user to manage their password, they are unknowingly revealing a password to an administrator 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 hash value of the password.

• PostgreSQL, when hashing a password as MD5, salts the password with the user name and then prepends the text «md5» to the resulting hash.
• Example: «md5″+md5(password + username)
• In Bash:

echo -n "passwordStringUserName" | md5sum | awk '' 

md5d6a35858d61d85e4a82ab1fb044aba9d 

[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 

ALTER USER UserName WITH PASSWORD 'md5d6a35858d61d85e4a82ab1fb044aba9d'; 

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.

Источник

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.

Источник