Comment 1980 added to page auth-methods.html of version 7.4

Author: Rick Robino <unread-spambox(at)lemoncurry(dot)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 WITH PASSWORD 'secret';"

More detail: When setting the password method to "md5" (for example) in pg_hba.conf as described above, here is an expanded procedure:

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 keyword UNENCRYPTED instead of ENCRYPTED, the password will _not_ be encrypted as stored in pg_shadow. This may or may not be 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 will still 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 a script, 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 one discover 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