PostgreSQL pgcrypto
▌Introduction
This
article shows how to support API versioning on ASP.NET Core Web API.
We will
learn how to use HASH, symmetric/asymmetric encryption/decryption.
▌Environment
▋PostgreSQL 11.6
▌Implement
▋Install pgcrypto
// Install
create extension pgcrypto;
// Confirm
select * from pg_available_extensions where "name"='pgcrypto';
Before the walk-thru, lets create a table for
testing encryption and decryption with pgcrypto.
CREATE TABLE public."Users"
(
"Name" text,
"Phone" bytea, --Encrpted phone number
"Password" text --Hashed pwd
);
ALTER TABLE public."Users" OWNER to postgres;
INSERT INTO public."Users"("Name") VALUES('JB');
▋HASH
The data type for Hashed data is TEXT.
pgcrypto’s crypto()
supports Hash
algorithms: bf, md5, xdes, des.
|
(From PostgreSQL
document)
▋Hash password
UPDATE public."Users"
SET "Password" = crypt('!@$%qwer4567RTYU', gen_salt('md5'))
WHERE "Name"='JB'
▋Check if password
matched
SELECT ("Password" = crypt('!@$%qwer4567RTYU', "Password")) AS IsMatch FROM public."Users";
▋PGP: Symmetric
The data type for encrypted data is BYTEA.
The Symmetric encryption/decryption uses a same Secret.
|
▋Encrypt
UPDATE public."Users"
SET "Phone" = pgp_sym_encrypt('0912345678', 'my-secret')
▋Decrypt
SELECT pgp_sym_decrypt("Phone",'my-secret') FROM public."Users"
▋PGP: Asymmetric Public key/Private(secret) key
The data type for encrypted data is BYTEA.
Asymmetric encryption/decryption uses a public key to encrypt, and uses a private(secret) key to decrypt.
|
We will
generate a key pair by GPG for
encryption n decryption.
▋GPG: Generate
key pair
$ gpg --gen-key
Enter name and email
address as following,
Set the passphrase to
protect the key or just click Enter to bypass it.
We can check the key
information with below commands:
$ gpg --list-keys
$ gpg --list-secret-keys
$ gpg --list-public-keys
For example,
The key
id: 6C3ECF8AD70E41E7E6362C880E473D67D1C6E811,
will be used when exporting the key.
▋GPG: Export the key
pair
Since we
will directly use the public/private key in SQL, we need to export them under PostgreSQL’s
/data directory.
We can
use the following SQL to get the right path:
SELECT setting FROM pg_settings where name='data_directory';
The
export command:
$ gpg -a --export <key_id> > /var/lib/postgresql/data/public.key
$ gpg -a --export-secret-key <key_id> > /var/lib/postgresql/data/private.key
Now lets
export the key pair to /var/lib/postgresql/data/keys,
$ cd /var/lib/postgresql/data
$ mkdir keys
$ gpg -a --export 6C3ECF8AD70E41E7E6362C880E473D67D1C6E811 > /var/lib/postgresql/data/keys/public.key
$ gpg -a --export-secret-key 6C3ECF8AD70E41E7E6362C880E473D67D1C6E811 > /var/lib/postgresql/data/keys/private.key
▋GPG: (Optional)
Set key expiry
The key is good to use even is expired. The expiry is meant
to tell users that do not use an expired key.
|
$ gpg --edit-key 6C3ECF8AD70E41E7E6362C880E473D67D1C6E811
$ gpp> expire
$ Key is valid for? <Set the expiry with the following options>
Value
|
|
0
|
key does not expire
|
<n>
|
key expires in n days
|
<n>w
|
key expires in n weeks
|
<n>m
|
key expires in n months
|
<n>y =
|
key expires in n years
|
<yyyy>-<MM>-<dd>
|
e.q. 2025-12-31
|
▋Encrypt
UPDATE public."Users"
SET "Phone" = pgp_pub_encrypt('0912345678', dearmor(pg_read_file('keys/public.key')))
▋Decrypt
SELECT pgp_pub_decrypt("Phone",dearmor(pg_read_file('keys/private.key'))) AS "Phone" FROM public."Users"
▋DB functions
I
created some DB functions for reference.
/* HASH */
--Hash
CREATE OR REPLACE FUNCTION my_hash(t text) RETURNS text AS $$
BEGIN
RETURN crypt(t, gen_salt('md5'));
END;
$$ LANGUAGE plpgsql;
--Usage sample
SELECT my_hash('this_is_not_my_pwd');
--Is Match
CREATE OR REPLACE FUNCTION my_hash_match(t text, hashed text) RETURNS boolean AS $$
BEGIN
RETURN (hashed = crypt(t, hashed)) AS IsMatch;
END;
$$ LANGUAGE plpgsql;
--Usage sample
SELECT my_hash_match('this_is_not_my_pwd', '$1$pSFXakbG$XgX9GvfcQCiIRjOYek7O4/')
/* PGP: Symmetric */
--Encrypt
CREATE OR REPLACE FUNCTION my_sym_encrypt(t text, secret text) RETURNS bytea AS $$
BEGIN
RETURN pgp_sym_encrypt(t, secret);
END;
$$ LANGUAGE plpgsql;
--Decrypt
CREATE OR REPLACE FUNCTION my_sym_decrypt(t bytea, secret text) RETURNS text AS $$
BEGIN
RETURN pgp_sym_decrypt(t, secret);
END;
$$ LANGUAGE plpgsql;
--Usage sample
SELECT my_sym_decrypt(my_sym_encrypt('something_not_important', 'my-secret'), 'my-secret');
/* PGP: Asymmetric */
--Encrypt
CREATE OR REPLACE FUNCTION my_pub_encrypt(t text) RETURNS bytea AS $$
BEGIN
RETURN pgp_pub_encrypt(t, dearmor(pg_read_file('keys/public.key')));
END;
$$ LANGUAGE plpgsql;
--Decrypt
CREATE OR REPLACE FUNCTION my_pvt_decrypt(t bytea) RETURNS text AS $$
BEGIN
RETURN pgp_pub_decrypt(t,dearmor(pg_read_file('keys/private.key')));
END;
$$ LANGUAGE plpgsql;
--Usage sample
SELECT my_pvt_decrypt(my_pub_encrypt('something_not_important'))
▌Reference
