Thread: Encrypted column
Hello, I need to store users and passwords on a table and I want to store it encrypted, but I don't found documentation about it, how can I create a table with columns "user" and "password" with column "password" encrypted and how can I check if "user" and "password" are correct using a sql query ? I appreciate any help Thanks Ranieri Mazili
> I need to store users and passwords on a table and I want to store it > encrypted, but I don't found documentation about it, how can I create a Take a look at the pgcrypto user-contributed module. -- Gary Chambers // Nothing fancy and nothing Microsoft!
Ranieri Mazili wrote: > Hello, > > I need to store users and passwords on a table and I want to store it > encrypted, but I don't found documentation about it, how can I create a > table with columns "user" and "password" with column "password" > encrypted and how can I check if "user" and "password" are correct using > a sql query ? Many people consider two-way encryption to be insecure; two-way encryption means you can decrypt a value if you know the key, and it is insecure because you usually have to put the key into the source code. That means at least one person in your company, the programmer maintaining the source code, can learn all of your users' passwords. One way around that is to hash the value instead. Then to validate, at runtime you hash the user-entered password using the same hash function, and validate that it matches the stored hash. No one in your company ever knows end-user passwords. -- Guy Rouillier
Ranieri Mazili schrieb: > Hello, > > I need to store users and passwords on a table and I want to store it > encrypted, but I don't found documentation about it, how can I create a > table with columns "user" and "password" with column "password" > encrypted and how can I check if "user" and "password" are correct using > a sql query ? Passwords are usually not encrypted but hashed instead. A common hash function is available in postgres w/o any additional extension: md5() The rule is, if two hashes compare equal, then the original data must be equal (yes, there are chances for collisions, but practically very low. See also sha1 and friends in the pgcrypto contrib module) Regards Tino
On 06/04/07 17:54, Guy Rouillier wrote: > Ranieri Mazili wrote: >> Hello, >> >> I need to store users and passwords on a table and I want to store it >> encrypted, but I don't found documentation about it, how can I create >> a table with columns "user" and "password" with column "password" >> encrypted and how can I check if "user" and "password" are correct >> using a sql query ? > > Many people consider two-way encryption to be insecure; two-way > encryption means you can decrypt a value if you know the key, and it is > insecure because you usually have to put the key into the source code. > That means at least one person in your company, the programmer > maintaining the source code, can learn all of your users' passwords. Two-way encryption is needed for companies that store customer credit cards. But yes, I've always worried about that. > One > way around that is to hash the value instead. Then to validate, at > runtime you hash the user-entered password using the same hash function, > and validate that it matches the stored hash. No one in your company > ever knows end-user passwords. > -- Ron Johnson, Jr. Jefferson LA USA Give a man a fish, and he eats for a day. Hit him with a fish, and he goes away for good!
On 6/5/07, Tino Wildenhain <tino@wildenhain.de> wrote: > Ranieri Mazili schrieb: > > Hello, > > > > I need to store users and passwords on a table and I want to store it > > encrypted, but I don't found documentation about it, how can I create a > > table with columns "user" and "password" with column "password" > > encrypted and how can I check if "user" and "password" are correct using > > a sql query ? > > Passwords are usually not encrypted but hashed instead. A common hash > function is available in postgres w/o any additional extension: > > md5() > > The rule is, if two hashes compare equal, then the original data must > be equal (yes, there are chances for collisions, but practically very > low. See also sha1 and friends in the pgcrypto contrib module) > > Regards > Tino > Remember, you would also want to add some sort of salt before you ran the hash, otherwise your password list is vulnerable to a simple brute-force attack.
Ron Johnson wrote: > On 06/04/07 17:54, Guy Rouillier wrote: > >Many people consider two-way encryption to be insecure; two-way > >encryption means you can decrypt a value if you know the key, and it is > >insecure because you usually have to put the key into the source code. > >That means at least one person in your company, the programmer > >maintaining the source code, can learn all of your users' passwords. > > Two-way encryption is needed for companies that store customer > credit cards. I thought that the advice for companies storing customer CCs was: don't. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
On 6/5/07, Tino Wildenhain <tino@wildenhain.de> wrote: > Ranieri Mazili schrieb: > > Hello, > > > > I need to store users and passwords on a table and I want to store it > > encrypted, but I don't found documentation about it, how can I create a > > table with columns "user" and "password" with column "password" > > encrypted and how can I check if "user" and "password" are correct using > > a sql query ? > > Passwords are usually not encrypted but hashed instead. A common hash > function is available in postgres w/o any additional extension: > > md5() > > The rule is, if two hashes compare equal, then the original data must > be equal (yes, there are chances for collisions, but practically very > low. See also sha1 and friends in the pgcrypto contrib module) Both md5 and sha1 are bad for passwords, no salt and easy to bruteforce - due to the tiny amount of data in passwords. Proper ways is to use crypt() function from pgcrypto module. Due to historical accident is has bad name which hints at encryption, actually its only purpose is to hash passwords. Read more in pgcrypto doc. -- marko
On 6/5/07, Marko Kreen <markokr@gmail.com> wrote: > On 6/5/07, Tino Wildenhain <tino@wildenhain.de> wrote: > > Ranieri Mazili schrieb: > > > Hello, > > > > > > I need to store users and passwords on a table and I want to store it > > > encrypted, but I don't found documentation about it, how can I create a > > > table with columns "user" and "password" with column "password" > > > encrypted and how can I check if "user" and "password" are correct using > > > a sql query ? > > > > Passwords are usually not encrypted but hashed instead. A common hash > > function is available in postgres w/o any additional extension: > > > > md5() > > > > The rule is, if two hashes compare equal, then the original data must > > be equal (yes, there are chances for collisions, but practically very > > low. See also sha1 and friends in the pgcrypto contrib module) > > Both md5 and sha1 are bad for passwords, no salt and easy to > bruteforce - due to the tiny amount of data in passwords. > > Proper ways is to use crypt() function from pgcrypto module. > Due to historical accident is has bad name which hints at > encryption, actually its only purpose is to hash passwords. > Read more in pgcrypto doc. > > -- > marko > If you salt them yourself, there's no problem with md5 or sha1, and they are arguably more secure than the old "crypt" call. Most modern linuxes use md5 for password storage.
On 06/05/07 08:59, Alvaro Herrera wrote: > Ron Johnson wrote: >> On 06/04/07 17:54, Guy Rouillier wrote: > >>> Many people consider two-way encryption to be insecure; two-way >>> encryption means you can decrypt a value if you know the key, and it is >>> insecure because you usually have to put the key into the source code. >>> That means at least one person in your company, the programmer >>> maintaining the source code, can learn all of your users' passwords. >> Two-way encryption is needed for companies that store customer >> credit cards. > > I thought that the advice for companies storing customer CCs was: don't. Sometimes you "must". An example from my industry: transponder "toll tags" and toll roads. The customer pre-pays a certain amount based on expected usage, and every time he drives thru a plaza, his balance decreases. Once it drops to a certain threshold, more money needs to be added to the account. If he is a CASH/CHEK customer, a light at the lane flashes yellow and (depending on the Agency) a message pops up saying, "Balance low", so he drives over to the customer service center, stands in line and pays his cash. If he is a CC customer, the system (which I am DBA of) bills his card directly, saving the customer much time and effort. -- Ron Johnson, Jr. Jefferson LA USA Give a man a fish, and he eats for a day. Hit him with a fish, and he goes away for good!
On 6/5/07, Brian Mathis <brian.mathis@gmail.com> wrote: > On 6/5/07, Marko Kreen <markokr@gmail.com> wrote: > > Both md5 and sha1 are bad for passwords, no salt and easy to > > bruteforce - due to the tiny amount of data in passwords. > > > > Proper ways is to use crypt() function from pgcrypto module. > > Due to historical accident is has bad name which hints at > > encryption, actually its only purpose is to hash passwords. > > Read more in pgcrypto doc. > > If you salt them yourself, there's no problem with md5 or sha1, and > they are arguably more secure than the old "crypt" call. Most modern > linuxes use md5 for password storage. No, both md5 and sha1 are actually easier to bruteforce than the old DES-based crypt. Ofcourse that does not mean that old DES-crypt is good idea. Pgcrypto's crypt() supports bit more modern md5crypt and bf-crypt algoriths which give much higher security margin. It can be argued that bf-crypt is the "state-of-the-art" algorithm for password hashing. -- marko
On 6/5/07, Marko Kreen <markokr@gmail.com> wrote: > both md5 and sha1 are actually easier to bruteforce than > the old DES-based crypt. If this statement seems weird - the problem is the speed. MD5 and SHA1 are just faster algorithms than des-crypt. And there's nothing wrong with fast general-purpose algorithms, as long their cryptographic properties hold. Starting from 20-30 bytes the bruteforce is really not an option. But if you have under 10 bytes (let be honest - you have 6 bytes...) the speed start to matter, because it is possible on random laptop to simply try all combinations. -- marko
On 6/5/07, Marko Kreen <markokr@gmail.com> wrote: > On 6/5/07, Marko Kreen <markokr@gmail.com> wrote: > > both md5 and sha1 are actually easier to bruteforce than > > the old DES-based crypt. > > If this statement seems weird - the problem is the speed. > MD5 and SHA1 are just faster algorithms than des-crypt. > > And there's nothing wrong with fast general-purpose algorithms, > as long their cryptographic properties hold. Starting from > 20-30 bytes the bruteforce is really not an option. > > But if you have under 10 bytes (let be honest - you have > 6 bytes...) the speed start to matter, because it is possible > on random laptop to simply try all combinations. > > -- > marko > pgcrypto also supports md5, so I'm not sure what you're referring to here. As I already mentioned, *salting* before you hash is a very important step. I'm not sure if you saw that in my post. Without a salt, it's trivial to generate a list of all combinations of md5'd strings and their results, up to reasonable lengths. Then it would be very simple to look up each hash and get the original text. With a salt, you need to generate all possible md5s for all possible salts -- a much harder task. In any case, pgcrypto seems to be a nice and full featured tool, so one should use that instead of rolling their own.
On Tue, Jun 05, 2007 at 09:28:00AM -0500, Ron Johnson wrote:
>
> If he is a CC customer, the system (which I am DBA of) bills his
> card directly, saving the customer much time and effort.
So surely what you have is a completely separate system that has
exactly one interface to it, that is signaled to provide a
transaction number and that only ever returns such a transaction
number to the "online" system, and that is very tightly secured,
right?
It is possible to make trade-offs in an intelligent manner, for sure,
but you sure as heck don't want that kind of data stored online with
simple reversible encryption.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
The whole tendency of modern prose is away from concreteness.
--George Orwell
On Jun 5, 2007, at 7:28 AM, Ron Johnson wrote: > On 06/05/07 08:59, Alvaro Herrera wrote: >> Ron Johnson wrote: >>> On 06/04/07 17:54, Guy Rouillier wrote: >>>> Many people consider two-way encryption to be insecure; two-way >>>> encryption means you can decrypt a value if you know the key, >>>> and it is insecure because you usually have to put the key into >>>> the source code. That means at least one person in your company, >>>> the programmer maintaining the source code, can learn all of >>>> your users' passwords. >>> Two-way encryption is needed for companies that store customer >>> credit cards. >> I thought that the advice for companies storing customer CCs was: >> don't. > > Sometimes you "must". > > An example from my industry: transponder "toll tags" and toll > roads. The customer pre-pays a certain amount based on expected > usage, and every time he drives thru a plaza, his balance > decreases. Once it drops to a certain threshold, more money needs > to be added to the account. > > If he is a CASH/CHEK customer, a light at the lane flashes yellow > and (depending on the Agency) a message pops up saying, "Balance > low", so he drives over to the customer service center, stands in > line and pays his cash. > > If he is a CC customer, the system (which I am DBA of) bills his > card directly, saving the customer much time and effort. Public key encryption can help here. Encrypt with the public key when it goes into the database, keep the private key on a separate, well protected system that's just used for recurring CC billing. Cheers, Steve
Marko Kreen schrieb: > On 6/5/07, Tino Wildenhain <tino@wildenhain.de> wrote: >> Ranieri Mazili schrieb: >> > Hello, >> > >> > I need to store users and passwords on a table and I want to store it >> > encrypted, but I don't found documentation about it, how can I create a >> > table with columns "user" and "password" with column "password" >> > encrypted and how can I check if "user" and "password" are correct >> using >> > a sql query ? >> >> Passwords are usually not encrypted but hashed instead. A common hash >> function is available in postgres w/o any additional extension: >> >> md5() >> >> The rule is, if two hashes compare equal, then the original data must >> be equal (yes, there are chances for collisions, but practically very >> low. See also sha1 and friends in the pgcrypto contrib module) > > Both md5 and sha1 are bad for passwords, no salt and easy to > bruteforce - due to the tiny amount of data in passwords. > Err. I did not mention salt but nobody prevents you from using a salt with md5 and sha. Regards Tino
On 6/5/07, Brian Mathis <brian.mathis@gmail.com> wrote:
> pgcrypto also supports md5, so I'm not sure what you're referring to
> here.
digest(psw, 'md5') vs. crypt(psw, gen_salt('md5'))
> As I already mentioned, *salting* before you hash is a very
> important step. I'm not sure if you saw that in my post. Without a
> salt, it's trivial to generate a list of all combinations of md5'd
> strings and their results, up to reasonable lengths. Then it would be
> very simple to look up each hash and get the original text. With a
> salt, you need to generate all possible md5s for all possible salts --
> a much harder task.
I dont think its practical method tho'. Rather, when doing
dictionary-based or bruteforce attack, then if hashes do not
have salts you attack them all at once.
But if they have salts then for each word you try you need to
hash it for each salt. Which basically gives the effect that
each hash needs to be attacked separately.
In case of attacking one hash the salt does not matter,
only the algorithm counts then. In that case as i said,
event salted md5 is weaker than des-crypt.
--
marko
Marko Kreen wrote:
> On 6/5/07, Brian Mathis <brian.mathis@gmail.com> wrote:
>> pgcrypto also supports md5, so I'm not sure what you're referring to
>> here.
>
> digest(psw, 'md5') vs. crypt(psw, gen_salt('md5'))
>
>> As I already mentioned, *salting* before you hash is a very
>> important step. I'm not sure if you saw that in my post. Without a
>> salt, it's trivial to generate a list of all combinations of md5'd
>> strings and their results, up to reasonable lengths. Then it would be
>> very simple to look up each hash and get the original text. With a
>> salt, you need to generate all possible md5s for all possible salts --
>> a much harder task.
>
> I dont think its practical method tho'. Rather, when doing
> dictionary-based or bruteforce attack, then if hashes do not
> have salts you attack them all at once.
>
> But if they have salts then for each word you try you need to
> hash it for each salt. Which basically gives the effect that
> each hash needs to be attacked separately.
>
> In case of attacking one hash the salt does not matter,
> only the algorithm counts then. In that case as i said,
> event salted md5 is weaker than des-crypt.
The best method as far as I understand it is HMAC
(http://www.faqs.org/rfcs/rfc2104.html).
It has some significant cryptanalysis behind it to ensure it does not
leak information that would compromise the password. Even MD5 and SHA1,
which have been shown to have certain weaknesses, are not at issue when
used with HMAC (see, for example, section 3.1.1 of
http://www.apps.ietf.org/rfc/rfc4835.html)
The way you would use HMAC is:
1. generate a random token, whatever length you want (the salt)
2. use HMAC (implemented with either md5 or sha1 or something newer) to
hash the salt with the password
3. store the salt and the resulting HMAC hash
4. on login, calculate the HMAC of the token using the provide password,
and compare to the stored hash
pgcrypto appears to support HMAC. It is also relatively easy to
implement on top of the built in md5 function if you'd rather not
install pgcrypto. And I'm sure there are HMAC functions available that
could be used in PL/Perl and/or PL/Python.
Joe