Learning PostgreSQL 11 - Third Edition

One-way encryption

In one-way encryption, retrieving data in a clear text form is not important. The encrypted text (digest) is used to verify that the user knows the secret text. One-way encryption is often used to store passwords. PostgreSQL supports out-of-the-box MD5 encryption; however, as MD5 can be cracked easily, MD5 could be used with salt, as seen in the preceding pg_shadow example.

The common scenario of validating a password is comparing the generated MD5 digest with the stored one, as follows:

CREATE TABLE account_md5 (id INT, password TEXT);INSERT INTO account_md5 VALUES (1, md5('my password'));SELECT (md5('my password') = password) AS authenticated FROM account_md5; authenticated --------------- t(1 row)

In PostgreSQL 11, new ...