August 8th, 2013, 11:03 PM
So I was building an increasingly complex Access database, since I didn't feel like paying for SQL Server on my home PC, and we were very rapidly reaching a point where Access SQL's Union contract specified that it no longer had to deal with this nested query BS.
I also needed to share the data with a friend who is an exclusive Mac user.
So I says to myself, I says "OK, we'll migrate to PostGres, I've used it before, how hard can it be?"
Oh what a fool I was...
Before I forget I'm using some sub-version of PostGres 9.2 64 bit on my Windows 8 box, and initially was using pgAdmin 3.
So after getting most of this stuff imported, I go to create a login for my friend via pgAdmin, and set a password through the interface, along with a superuser account for myself over and above the Postgres account.
The logins are created and the passwords are not recognized. Interesting. I try changing the Postgres user password... BIG MISTAKE. Now I have a brick.
As near as I can figure the problem has something to do with md5 encryption. I was ultimately able to get access again by switching my pg_hga.conf file to read as follows:
But that kind of defeats the purpose of security now doesn't it?
# IPv4 local connections:
host all postgres 127.0.0.1/32 trust
# IPv6 local connections:
host all postgres ::1/128 trust
It also doesn't seem to just be a cunning trap to weed out Windows users who have a naive expectation that changing a password via GUI should reset it to the password you specified. Ditching the UI and just sending the simple command:
...executes properly and doesn't actually set the password to the expected value. (Password redacted.)
ALTER ROLE postgres PASSWORD 'CorrectHorseBatteryStaple';
Clearly there's something going on with md5 encryption that's too complex for my puny Microsoft addled brain.
Could someone clue me in on just how the hell this is supposed to work? Please?