Author: Rick Robino <unread-spambox@lemoncurry.com>
----
IMHO, it would be helpful in some contexts if the procedure for setting passwords could be presented in a bit more
detail. The following expanded procedure yields identical results to the directive presented above "CREATE USER foo
WITHPASSWORD 'secret';"
More detail: When setting the password method to "md5" (for example) in pg_hba.conf as described above, here is an
expandedprocedure:
1. Connect to the database as the postgresql super-user.
sudo -u postgres psql template1
2. Turn on password encryption.
ALTER USER <username> SET password_encryption TO 'true';
3. Store an encrypted form of the password. ***NOTE*** if you provide an unencrypted password here and use the
keywordUNENCRYPTED instead of ENCRYPTED, the password will _not_ be encrypted as stored in pg_shadow. This may or may
notbe intuitive.
ALTER USER <username> WITH ENCRYPTED PASSWORD '<password>';
-- Of course the default was just set above, so you can leave out the keyword ENCRYPTED and an md5 hash of the password
willstill be set.;
4. Check that the password has been set and is stored encrypted:
SELECT usename,passwd FROM pg_shadow WHERE usename = '<username>';
Obviously this procedure assumes you have created the user beforehand.
Why might a person supply an encrypted password using keyword UNENCRYPTED? Because that way passwords can be set with
ascript, something that reads /etc/master.passwd for example. Or, this is how one might set a crypt(3) password.
Defaults therefore can vary from a default install unexpectedly (probably by other staff), this procedure might help
onediscover such a case and work around it temporarily. See postgresql.conf::password_encryption for your local
default.
----
Manual page: http://wwwmaster.postgresql.org/docs/7.4/interactive/auth-methods.html
Reject: http://wwwmaster.postgresql.org/admin/comments.php?action=reject&id=1980
Edit: http://wwwmaster.postgresql.org/admin/comment-edit.php?id=1980
Delete: http://wwwmaster.postgresql.org/admin/comments.php?action=delete&id=1980
