Re: [GENERAL] role passwords and md5()
Looks like the password gets cleared when you rename a role. Regards, Ben Ben Trewern [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] 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 [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] 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 [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] role passwords and md5()
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 [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] role passwords and md5()
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 [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] 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 [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] 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 smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] role passwords and md5()
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