Re: Password setting having somewhat bizarre results.

I’m migrating some moderately sized DBs from Access to Postgres because I can’t deal with Access’ performance issues and ANSI SQL noncompliance.

 

I hit a snag at a rather unexpected place.  Before we get started I should state that I’m using PGAdmin 1.16.1 on a Windows 8 64 bit machine.  I’m aware that this last bit shows appalling judgment.  The server is on the same machine and is running PostGres 9.2, whichever flavor was stable circa 1/31/13 according to the installation date.

 

Now I need to make this into a secured web server using my own machine as the server.  This entailed making some nice little logins for the purpose of providing that access.  I used the PGAdmin UI and set the passwords for the new logins via the role creation UI.  I was fairly sure that I had set them to a nice high quality password, let’s pretend it was “CorrectHorseBatteryStaple”.  I tried using the logins via psqlODBC to shepherd the data from Access via SQLExpress and got a password failure.  Needing to keep things moving, I switched to the postgres default login for the data transfer.  Thinking that I must have just got the password wrong I tried correcting it through the UI.  So then I tried setting the master password through the UI.  Ooops.  I was able to regain access by setting the pg_hba.conf to trust all connections, but even when I used the ALTER ROLE SQL statement, I still could not reset the password.

I think I must be missing something fundamental, and I suspect it has to do with MD5 encryption, but I’m at a loss as to what.

 

Any idea how to set up security properly for someone who openly admits to being more analyst than DBA?