Postgresql – Crytography in PostgreSQL

encryptionpostgresqlSecurity

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.

Best Answer

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
Related Question