How does this work when you rename a role? Does the is the password hash changed (and how?) or is the original username kept somewhere in the system tables? Regards, Ben "Andrew Kroeger" <andrew@xxxxxxxxxxxxxxxxxx> wrote in message news:461E27BA.7020001@xxxxxxxxxxxxxxxxxxxxx > Lutz Broedel wrote: >> Dear list, >> >> I am trying to verify the password given by a user against the system >> catalog. Since I need the password hash later on, I can not just use the >> authentication mechanism for verification, but need to do this in SQL >> statements. >> Unfortunately, even if I set passwords to use MD5 encryption in >> pg_hba.conf, the SQL function MD5() returns a different hash. >> >> A (shortened) example: >> CREATE ROLE my_user WITH ENCRYPTED PASSWORD 'my_password'; >> >> SELECT * FROM pg_authid >> WHERE rolname='my_user' AND rolpassword=MD5('my_password'); >> >> Any ideas, what to do to make this work? >> Best regards, >> Lutz Broedel > > A quick look at the source shows that the hashed value stored in > pg_authid uses the role name as a salt for the hashing of the password. > Moreover, the value in pg_authid has the string "md5" prepended to the > hash value (I imagine to allow different hash algorithms to be used, but > I haven't personally seen anything but "md5"). > > Given your example above, the following statement should do what you are > looking for: > > SELECT * FROM pg_authid WHERE rolname='my_user' AND rolpassword = 'md5' > || md5('my_password' || 'my_user'); > > Hope this helps. > > Andrew > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings >
