Database Reference
In-Depth Information
PGCrypto
This extension provides encryption functions that allow you to store encrypted data in your
database. The
pgcrypto
extension is often used to manage user authentication in web ap-
plications.
In the following example, you will create a user table with two columns: username and
password. Now you will insert a user with an encrypted password. Then, through the ap-
propriate SQL, you will search the user with the access credentials.
$ heroku pg:psql --app your-app-name
CREATE EXTENSION pgcrypto;
CREATE TABLE users (username text, password bytea);
In the following
insert
statement, you will encrypt the user password via the
digest
function. This function accepts two parameters: the first one is the string to be encrypted
and the second one is the type of algorithm that is used. The algorithm can be
md5
,
sha1
,
sha224
,
sha256
,
sha384
, or
sha512
.
INSERT INTO users (username, password) VALUES
('patrickespake', digest('pass123', 'md5'));
Finally, you are able to search users via the username and password.
SELECT * FROM users WHERE username = 'patrickespake' AND
password = digest('pass123', 'md5') LIMIT 1;
username | password
---------------+------------------------------------------
patrickespake | 2%\001p\240\334\251-S\354\226$\3636\312$
In the documentation at
http://www.postgresql.org/docs/current/static/pgcrypto.html
, you
can find many other interesting functions.