Re: Password setting having somewhat bizarre results.

OK. I have done a quick test in my local windows 7.

1. I have logged into PG 9.2 as a super user("postgres").
2. Created new login role by performing right click on "Login Role" - >
"Create New Login".

3. Entered the new login name and password.

4. Disconnected from pgAdmin and re-logged in the pgAdmin using above
created logins, and i am able to connect.

Kindly let me know, if this approach what i have followed is improper.

This is exactly the method I followed. We are talking about Windows 8 on my
end, so that may throw a monkey wrench in the works. I think I had the new
OS option "cause confusing errors with non MS products" set to no. Then and
again, they somehow thought this iPad emulation nonsense was a good thing in
a desktop OS, so. (I'm actually considering using Valve's Steam to emulate
the programs menu.)

I am now using trusted connection, with the following being the relevant
lines in the pg_hba.conf:

# Trusted IPv4 local connections:

host all postgres 127.0.0.1/32 trust

# Trusted IPv6 local connections:

host all postgres ::1/128 trust

# IPv4 local connections:

#host all all 127.0.0.1/32 md5

# IPv6 local connections:

#host all all ::1/128 md5

I can login with postgres with any old password. Swapping back to MD5 and
using the original password or what I thought I replaced it with both don't
work. So I'm deducing that the password reset functionality is working,
it's just setting it to something other than the string I expected. Hence
my suspicion of the MD5 encryption, specifically that working with Microsoft
too long has left me with little idea what it's doing.

As a test I used the steps you outline above to create user dkumar with the
password "swordfish". I grabbed the SQL command pgAdmin generated and it is
as follows:

CREATE ROLE dkumar LOGIN ENCRYPTED PASSWORD
'md5efa0693f2cdca3392667d2961c3e2412'

VALID UNTIL 'infinity';

Switching out to md5 authentication again, the following happens when I
login via these credentials.

SUCCESS???

OK, now that's weird. It's always the non-reproducible errors that are the
most annoying. You get no functionality and look like a hallucinating
idiot.

Let's see if we can fix postgres with the same extremely questionable
password. dkumar was made as punyuser, so I have to go back to trusted for
a second.

ALTER ROLE postgres ENCRYPTED PASSWORD
'md5397e0cb3e3821859b1e9cb12438f3778';

The password authentication failed on login.

Let's try that again on the off chance that I've gone stark raving mad and
can't spell "swordfish" consistently.

ALTER ROLE postgres ENCRYPTED PASSWORD
'md5397e0cb3e3821859b1e9cb12438f3778';

Just for kicks let's try altering dkumar to "swordfish", just because I'm
curious as to what the hash will look like.

ALTER ROLE dkumar ENCRYPTED PASSWORD 'md5efa0693f2cdca3392667d2961c3e2412'

VALID UNTIL '1969-12-31 00:00:00';

OK then.

There was a superuser I made for myself, let's see if the hash is consistent
between users or usertypes.

ALTER ROLE jfoelster ENCRYPTED PASSWORD
'md534dce0915caecb1ff675ec5648f39a6e';

OK then. The hash must come from the combination of user and password.

Let's turn md5 back on and see who we can log in with now.

Now all three fail out on password authentication.

I think that merits a good healthy bit of puzzlement from my end. It seems
to like CREATE ROLE and hate ALTER ROLE, maybe? Or perhaps it's some kind
of gypsy curse?