Обсуждение: role passwords and md5()
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 -- To verify the digital signature, you need to load the following certificate: https://pki.pca.dfn.de/uh-ca/pub/cacert/rootcert.crt
Вложения
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
Looks like the password gets cleared when you rename a role. Regards, Ben "Ben Trewern" <ben.trewern@_nospam_mowlem.com> wrote in message news:evnt7k$14td$1@news.hub.org... >I thought I read this be for I sent it. :-( > > What I meant to say was: > Does the password hash change (and how?) Or is the original username kept > somewhere is the system tables? > > Regards, > > Ben > > "Ben Trewern" <ben.trewern@_nospam_mowlem.com> wrote in message > news:evnpgi$md3$1@news.hub.org... >> 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@sprocks.gotdns.com> wrote in message >> news:461E27BA.7020001@sprocks.gotdns.com... >>> 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 >>> >> >> > >
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@sprocks.gotdns.com> wrote in message news:461E27BA.7020001@sprocks.gotdns.com... > 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 >
I thought I read this be for I sent it. :-( What I meant to say was: Does the password hash change (and how?) Or is the original username kept somewhere is the system tables? Regards, Ben "Ben Trewern" <ben.trewern@_nospam_mowlem.com> wrote in message news:evnpgi$md3$1@news.hub.org... > 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@sprocks.gotdns.com> wrote in message > news:461E27BA.7020001@sprocks.gotdns.com... >> 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 >> > >