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?