1

I'm thinking to use PostgreSQL for a project because its free, people say its good, but I'll need some encryption in my db. I did a little search and I found these options in PostgreSQL:

  • bf
  • md5
  • xdes
  • des

I don't know what is the "most safe", Can you guys help me with this? In this case I prefer security with a little slowness than speed and a little security.

In this case I guess encryption is better than "just" hash the passwords, and really need this on db. I need a strong encryption for login information of my users.The difficulty is not how to store, but how to protect this with encryption.

0

1 Answer 1

7

If you want to validate your user's password, one of the best algorithms to use is bcrypt.

I'd recommend you implement the encryption and validation at your application layer, so you work with plain text data less time possible.

But if you are looking for one way to do that on PostgreSQL's side, you can use pgcrypto extension. So you have some steps:

  1. Install the contribs package on your system (if not done already).
  2. Connect to your database and create the extension:

    CREATE EXTENSION pgcrypto;
    
  3. Your users tables will need at least the following fields:

    CREATE TABLE users(
        login text PRIMARY KEY,
        passwd text NOT NULL
    );
    
  4. When you insert a new password, you do:

    INSERT INTO users(login, passwd)
    VALUES('my_login', crypt('my_password', gen_salt('bf')));
    

    being 'my_login' the user's login (e-mail, username, etc.), and 'my_password' the plain users password. And 'bf' the encryption algorithm you are using, being 'bf' the bcrypt.

  5. Once your user is there, to validate its password you do:

    SELECT count(*) AS match
    FROM users u
    WHERE
            u.login = 'my_login'
            AND u.passwd = crypt('my_password', u.passwd);
    

    If the above query returns 1, then the login was successful (login and passwd matched), if it returns 0, then the login failed (either login or passwd didn't match).

But again, I still recommend you use the same process in your application instead, because that way you don't ever need to send the plain password to the database, and work with encrypted password as soon as possible. To do on the application the idea is that your query returns the encrypted password, and you just validate it app-side, like (Python-based example):

import bcrypt
import psycopg2

def validate_login(user_login, user_passwd):
    con = psycopg2.connect('<your connection string>')
    cur = con.cursor()
    cur.execute("SELECT passwd FROM users WHERE login = %s", (user_login,))
    db_passwd = cur.fetchone()
    cur.close()
    con.close()

    if db_passwd == None:
        return false # login not found
    else:
        if bcrypt.hashpw(user_passwd, db_passwd[0]) == db_passwd[0]:
            return true # login successfully
        else:
            return false # login exists but passwd is incorrect

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.