How to insert an encrypted field into a table in Postgresql?

Question

I wanted to know how to insert an encrypted field into a table in the database.
To illustrate my doubt let’s assume that I have the following java class:

package pessoa;

public class Pessoa {

    private String email;
    private String senha ;
    private String nomecompleto ;   
    ..
    ..
    ..

}

Who will own the next entity in the bank:

CREATE TABLE usuario
    (
      email character varying(50) NOT NULL,
      senha character varying(200) NOT NULL,
      nomecompleto character varying(100) NOT NULL,
     PRIMARY KEY (email)
    )

The question is: I encrypt the field in the java application or in the database? And how can I do that? I have to create a method to encrypt in java code or something is already ready to use?

Answer 1

Answers to individual questions:

1. I encrypt the field in the java application or in the database?

The best option is to encrypt on the application side as soon as you receive the information to be encrypted.

2. And how can I do that?

It is not so simple, but there are several things ready for known algorithms, safe and recommended on the internet, just adapt to your specific use case.

For example, you could use PBKDF2 to store passwords. See an example using common Java libraries: https://gist.github.com/jtan189/3804290

3. I have to create a method to encrypt in java code or something is already ready to use?

Behold 2. =]

Security

Password encryption nay must be reversible. The answer link cited in the comments of the question, should not be considered for password encryption, as it is reversible, and even has security problems in some of the answers.

It is recommended that a strong password "encryption" algorithm be used, such as the bcrypt or the pbkdf2. Java has implementations for these algorithms, so just adapt your table (in case usuario) to store the additional values the chosen algorithm needs.

Modern computers have in the processor instructions that allow very fast execution of hash functions such as SHA and MD5; in addition, the GPU allows multiple hashes to be generated simultaneously, increasing much the amount of tests per second in a brute force attack. These algorithms are made so that, in addition to being mathematically safe, they are slow to be executed especially in a GPU, which has the advantage of parallelism.

In any case, if you want to use something really simple, use something like the following:

1. Add a column salt on your table usuario.

2. Choose a known collision-free hash, such as SHA2 or SHA3 (and use the 256-bit version or later).

3. In the password column, store (in pseudo-code):

usuario.senha = hash(concatena(salt, senha_digitada));

4. To validate the password, use the expression (in pseudo-code):

senha_valida = usuario.senha == hash(concatena(salt, senha_digitada));