The pgcrypto extension adds features to the postgres that allow many security-related operations to be performed directly inside the database. This extension provides functions for generating hashes, encrypting and decrypting data, creating values, and generating UUIDs. These functions help to protect information such as passwords, API keys, and confidential application data. The operations are handled within postgres applications, which can perform cryptographic tasks without depending on external libraries, for basic database-level security. The pgcrypto extension is widely used in applications that need to store or process sensitive data while keeping the implementation simple and efficient.
Check if the extension is available in your postgres.
select * from pg_available_extensions where name = 'pgcrypto';
Result :
name | default_version | installed_version | comment
----------+-----------------+-------------------+-------------------------
pgcrypto | 1.4 | | cryptographic functions
(1 row)
Now, create the extension.
create extension pgcrypto ;
Check the functionalities of this extension.
\dx+ pgcrypto
Result :
Objects in extension "pgcrypto"
Object description
-------------------------------------------------------
function armor(bytea)
function armor(bytea,text[],text[])
function crypt(text,text)
function dearmor(text)
function decrypt(bytea,bytea,text)
function decrypt_iv(bytea,bytea,bytea,text)
function digest(bytea,text)
function digest(text,text)
function encrypt(bytea,bytea,text)
function encrypt_iv(bytea,bytea,bytea,text)
function fips_mode()
function gen_random_bytes(integer)
function gen_salt(text)
function gen_salt(text,integer)
function hmac(bytea,bytea,text)
function hmac(text,text,text)
function pgp_armor_headers(text)
function pgp_key_id(bytea)
function pgp_pub_decrypt(bytea,bytea)
function pgp_pub_decrypt_bytea(bytea,bytea)
function pgp_pub_decrypt_bytea(bytea,bytea,text)
function pgp_pub_decrypt_bytea(bytea,bytea,text,text)
function pgp_pub_decrypt(bytea,bytea,text)
function pgp_pub_decrypt(bytea,bytea,text,text)
function pgp_pub_encrypt_bytea(bytea,bytea)
function pgp_pub_encrypt_bytea(bytea,bytea,text)
function pgp_pub_encrypt(text,bytea)
function pgp_pub_encrypt(text,bytea,text)
function pgp_sym_decrypt_bytea(bytea,text)
function pgp_sym_decrypt_bytea(bytea,text,text)
function pgp_sym_decrypt(bytea,text)
function pgp_sym_decrypt(bytea,text,text)
function pgp_sym_encrypt_bytea(bytea,text)
function pgp_sym_encrypt_bytea(bytea,text,text)
function pgp_sym_encrypt(text,text)
function pgp_sym_encrypt(text,text,text)
function public.gen_random_uuid()
(37 rows)
You can also check the installed version of this extension after the extension creation.
select * from pg_available_extensions where name = 'pgcrypto';
Result :
name | default_version | installed_version | comment
----------+-----------------+-------------------+-------------------------
pgcrypto | 1.4 | 1.4 | cryptographic functions
(1 row)
There is one guc parameter in postgres related to the pgcrypto extension.
show pgcrypto.builtin_crypto_enabled ;
Result :
pgcrypto.builtin_crypto_enabled
---------------------------------
on
(1 row)
Check the metadata of this parameter from the pg_settings catalogue.
select * from pg_settings where name = 'pgcrypto.builtin_crypto_enabled';
Result :
-[ RECORD 1 ]---+------------------------------------------------------------------------------------------------------------------------------
name | pgcrypto.builtin_crypto_enabled
setting | on
unit |
category | Customized Options
short_desc | Sets if builtin crypto functions are enabled.
extra_desc | "on" enables builtin crypto, "off" unconditionally disables and "fips" will disable builtin crypto if OpenSSL is in FIPS mode
context | superuser
vartype | enum
source | default
min_val |
max_val |
enumvals | {on,off,fips}
boot_val | on
reset_val | on
sourcefile |
sourceline |
pending_restart | f
We can use the bytea datatype in postgres to convert a string to binary format like this.
SELECT ('postgres'::bytea);Result :
bytea
--------------------
\x706f737467726573
(1 row)
armor()
SELECT armor('postgres'::bytea);Result :
armor
-----------------------------
-----BEGIN PGP MESSAGE-----+
+
cG9zdGdyZXM= +
=a2k4 +
-----END PGP MESSAGE----- +
(1 row)
The armor() function converts binary data into an ASCII-armored text format. This makes binary data safe to store or transmit in places that expect plain text, such as emails, log files, etc.
SELECT armor(
'hello'::bytea,
ARRAY['Version','Comment'],
ARRAY['PostgreSQL','Demo']
);
Result :
armor
-----------------------------
-----BEGIN PGP MESSAGE-----+
Version: PostgreSQL +
Comment: Demo +
+
aGVsbG8= +
=R/WK +
-----END PGP MESSAGE----- +
(1 row)
We can also add the custom headers in the ascii - armored text output like this.
dearmor()
SELECT dearmor(
$$-----BEGIN PGP MESSAGE-----
cG9zdGdyZXM=
=a2k4
-----END PGP MESSAGE-----$$
););
Result :
dearmor
--------------------
\x706f737467726573
(1 row)
We can also convert the ascii - armored text format into binary format by using the function named dearmor().
SELECT convert_from(
dearmor(
$$-----BEGIN PGP MESSAGE-----
cG9zdGdyZXM=
=a2k4
-----END PGP MESSAGE-----$$
),
'UTF8');
Result :
convert_from
--------------
postgres
(1 row)
We can use the utf-8 format and convert the ASCII-armored text format directly to the text.
crypt()
SELECT crypt('mypassword', gen_salt('bf'));Result :
crypt
--------------------------------------------------------------
$2a$06$stdhdh1CejpN15La8B4zdugsZQosikh.H8IY01.7uxKLL.tnwr4NK
(1 row)
The crypt() is mainly used for hashing passwords. The gen_salt() is used to generate a random salt that will be added to the password before hashing.
gen_salt()
SELECT gen_salt('bf');Result :
gen_salt
-------------------------------
$2a$06$z/GYXEot6mHpRt3XWqU2Ue
(1 row)
Here, we pass the bf into the gen_salt(). It means that it uses the blowfish algorithm for generating the random salt.
SELECT gen_salt('md5');Result :
gen_salt
-------------
$1$IoX733J.
(1 row)
We can also pass md5 to the gen_salt() like this.
digest()
SELECT encode(digest('postgres','md5'),'hex');Result :
encode
----------------------------------
e8a48653851e28c69d0506508fb27fc5
(1 row)
The digest() is used to generate the cryptographic hash of the passed value. Here, we use the encode() with the digest().
So, here it actually uses the digest(), we pass the string as postgres, we use the md5 algorithm to hash the string, and at the end we pass the hex inside the encode(). The encode() function converts the binary (bytea) value returned by digest() into a hexadecimal string.
encrypt()
SELECT encode(
encrypt(
'hello'::bytea,
'1234567890123456'::bytea,
'aes'
),
'hex');
Result :
encode
----------------------------------
ebb7c703e675db3da397038b4c17823c
(1 row)
The encrypt function takes some data and uses a secret key to scramble it. This scrambled data is what the encrypt function gives back to you. You can then use the function to change this scrambled binary data into a hexadecimal string. This makes the scrambled data easier to look at. The encrypt function uses a way to scramble the binary data, which is called an encryption algorithm. The encrypt function needs this encryption algorithm and the secret key to do its job.
Here, the string we passed is hello, we use this 1234567890123456 as an encryption key, and we aes ( advanced encryption standard ) algorithm for this encryption.
decrypt()
SELECT convert_from(
decrypt(
encrypt(
'hello'::bytea,
'1234567890123456'::bytea,
'aes'
),
'1234567890123456'::bytea,
'aes'
),
'UTF8');
Result :
convert_from
--------------
hello
(1 row)
The decrypt() function decrypts data that was previously encrypted using the encrypt() function. It requires the same encryption key and algorithm that were used during encryption. Since the decrypted result is returned as binary (bytea), the convert_from() function is commonly used to convert it back into readable text.
encrypt_iv()
SELECT encrypt_iv(
'hello'::bytea,
'1234567890123456'::bytea,
'abcdefghijklmnop'::bytea,
'aes-cbc'
);
Result :
encrypt_iv
------------------------------------
\x64ce95f8de13ac86e586ba4df857e463
(1 row)
The encrypt_iv function is used to secure data. This binary data is made up of bytes. The function uses a way to encrypt the data. It needs a key and something called an Initialization Vector. The Initialization Vector is important because it helps keep the data safe. When you use the key to encrypt the same data, the Initialization Vector makes sure the encrypted data is different each time. This is what the encrypt_iv function does with the Initialization Vector and the secret key to keep the data secure.
decrypt_iv()
SELECT convert_from(
decrypt_iv(
encrypt_iv(
'hello'::bytea,
'1234567890123456'::bytea,
'abcdefghijklmnop'::bytea,
'aes-cbc'
),
'1234567890123456'::bytea,
'abcdefghijklmnop'::bytea,
'aes-cbc'
),
'UTF8');
Result :
convert_from
--------------
hello
(1 row)
The decrypt_iv() function takes data that was previously encrypted with the decrypt_iv() function. Turns it back into its original form. It needs the encryption key, Initialization Vector (IV), and encryption algorithm that were used when the data was encrypted.
In this example, the text hello is first encrypted using the AES-CBC algorithm, a key, and an Initialization Vector (IV). The encrypted data that is created is then given to decrypt_iv(), which uses the key and IV to get back the original binary data. After that, the convert_from() function takes the decrypted binary (bytea) value. Change it into a readable UTF-8 text, giving the result hello.
gen_random_bytes()
SELECT gen_random_bytes(16);
Result :
gen_random_bytes
------------------------------------
\xea1e9150f5bbc910c0229e57a94de9b7
(1 row)
The gen_random_bytes function makes many really secure random bytes. We tell it to make 16 bytes. So postgres makes 16 bytes and gives them back to us as a bytea value. This is what the gen_random_bytes function does. It is very good at making random bytes, like these 16 bytes.
gen_random_uuid()
SELECT gen_random_uuid();
Result :
gen_random_uuid
--------------------------------------
1ea5aaed-7ad0-4e12-bc84-894292ba93bc
(1 row)
The function gen_random_uuid() makes a UUID. This UUID is a number that is 128 bits long.
pgp_sym_encrypt()
SELECT pgp_sym_encrypt(
'This is secret',
'mypassword'
);
Result :
pgp_sym_encrypt
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
\xc30d04070302716e140ae1c68b827ed23f015a42b6c770419b9df5d2591528b69d1ac73847b78c75c2d193da5aa1c33a58a16ea3cfea2405a4735719072de7be073f5fa2a588615a95bcf688228b3ac7
(1 row)
The function pgp_sym_encrypt() takes data and encrypts it using encryption. With encryption, the same password is used to both lock and unlock the data.
In this case, the text "This is secret" is encrypted using the password "mypassword". The function gives back the encrypted data as a bytea type. As the result is encrypted data, it is not possible to read it directly. To get the text back, the same password needs to be given to the function pgp_sym_decrypt().
pgp_sym_decrypt()
SELECT pgp_sym_decrypt(
pgp_sym_encrypt(
'This is secret',
'mypassword'
),
'mypassword'
);
Result :
pgp_sym_decrypt
-----------------
This is secret
(1 row)
The pgp_sym_decrypt function is used to unlock data that was locked with the pgp_sym_encrypt function. To unlock the data, you need to use the password that you used when you locked it.
In this example, the words “This is secret” are first locked with a password, which is “mypassword”. Then, the locked data is given to the pgp_sym_decrypt function. It uses the same password to unlock the data and give you back the original words.
pgp_sym_encrypt_bytea()
SELECT pgp_sym_encrypt_bytea(
'hello'::bytea,
'mypassword'
);
Result :
pgp_sym_encrypt_bytea
--------------------------------------------------------------------------------------------------------------------------------------------------
\xc30d04070302f1a6c3b8f9719b7c7dd23601a14035ac9b11a87f10b9ab4236081072412938c8768d23235069013b0d8a953b048001a6a9aba44708781b1fb0aeee6850074dae2c
(1 row)
The pgp_sym_encrypt_bytea function is used to encrypt data. This binary data is encrypted using a password. To get the data back, you need to use the same password.
In this example, the word hello is first changed into data. Then the pgp_sym_encrypt_bytea function encrypts this data using the password mypassword. The result of this encryption is a value that looks like a hexadecimal code. If you want to get the binary data back, you can use the pgp_sym_decrypt_bytea function with the same password mypassword.
pgp_sym_decrypt_bytea()
SELECT convert_from(
pgp_sym_decrypt_bytea(
pgp_sym_encrypt_bytea(
'hello'::bytea,
'mypassword'
),
'mypassword'
),
'UTF8');
Result :
convert_from
--------------
hello
(1 row)
The pgp_sym_decrypt_bytea() function decrypts binary (bytea) data that was previously encrypted using the pgp_sym_encrypt_bytea() function. It requires the same password that was used during encryption.
In this example, the text 'hello' is first converted to the bytea data type and encrypted using the password 'mypassword'. The encrypted binary data is then decrypted using pgp_sym_decrypt_bytea() with the same password. Since the decrypted result is returned as a bytea value, the convert_from() function converts it back into readable UTF-8 text.
The pgcrypto extension offers a set of functions for hashing, encryption, decryption, creating data, and generating UUIDs inside PostgreSQL. These functions let you do usual security tasks without the need for outside tools or libraries. Here, you know when to use hashing, encryption, salts, random values, and UUIDs, and you can create database applications that manage information more securely. Looking at these functions with examples is a great way to see how PostgreSQL helps with cryptographic tasks in actual use.