Storage encryption

pgcrypto provides two types of data encryption algorithms: one-way encryption and two-way encryption, which are used to realize data encryption storage at the database level and ensure the secure use of data.

One-way encryption is irreversible encryption, and the plain text cannot be decrypted based on the ciphertext. Suitable for data verification, such as login password verification. Commonly used one-way encryption algorithms include MD5, SHA, HMAC, etc.
Bidirectional encryption is reversible encryption, and the plain text can be decrypted based on the ciphertext and key. Suitable for the secure transmission of data, such as electronic payments, digital signatures, etc. Commonly used bidirectional encryption algorithms include AES, DES, RSA, ECC, SM4, etc.

One-way encryption

Ordinary hash function

The digest() function can generate binary hash values of data according to different algorithms. This type of encryption algorithm has the same result after encrypting the same data.

#Syntax Structure
digest(data text, type text) returns bytea
digest(data bytea, type text) returns bytea
#type is the algorithm to be used, and the standard algorithms include md5, sha1, sha224, sha256, sha384, and sha512.

#Example of usage

INSERT INTO users(username, password) 
VALUES ('tony', encode(digest('123456','md5'), 'hex'));

INSERT INTO users(username, password) 
VALUES ('anne', encode(digest('123456','md5'), 'hex'));

SELECT * FROM users;
id|username|password                        |
--|------------------------------------------------------------------------------------------------------------------------------
 1|tony    |e10adc3949ba59abbe56e057f20f883e|
 2|anne    |e10adc3949ba59abbe56e057f20f883e|

#When matching, you need to use the encode function to perform sixteen prohibition conversion.
SELECT id FROM users
WHERE username = 'tony' AND password = encode(digest('123456','md5'), 'hex');
id|
--|
 1|

The hmac() function can generate binary hash values of data with the key pws according to different algorithms. This is similar to digest(), but the hash can only be recalculated with the same encryption result if the key is the same.
```
#Syntax Structure
hmac(data text, psw text, type text) returns bytea
hmac(data bytea, psw text, type text) returns bytea
#type is the algorithm to be used, and the standard algorithms include md5, sha1, sha224, sha256, sha384, and sha512.
```
Two-way encryption

PGP encryption function

The PGP encryption function implements encryption functions in the OpenPGP (RFC4880) standard, including symmetric key encryption (private key encryption) and asymmetric key encryption (public key encryption).

  #encryption
  pgp_sym_encrypt(data text, psw text [, options text ]) returns bytea
  pgp_sym_encrypt_bytea(data bytea, psw text [, options text ]) returns bytea
  #Decryption
  pgp_sym_decrypt(msg bytea, psw text [, options text ]) returns text
  pgp_sym_decrypt_bytea(msg bytea, psw text [, options text ]) returns bytea


  #Application example

  select pgp_sym_encrypt('abc','key_value');
                                                                 pgp_sym_encrypt                                                               
  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   \xc30d04070302f93fbd59b40bf7fd71d2340175c19d234d275f5b8ae668fecbbdfd80f0e94185f07dee15cb6d2b0dfbfdf08c98648e07da8f3d8902bb3dd349fdb36860a1ff
  (1 row)

  select pgp_sym_decrypt(pgp_sym_encrypt('abc','key_value'),'key_value');             
   pgp_sym_decrypt
  ----------------------------------------------------------------------------------------------------------------------------------
   abc
  (1 row)

  # Common options are:
  # cipher-algo, the password algorithm used can be bf, aes128 (default), aes192, aes256;
  # compress-algo, the compression algorithm used, is only available when compiling PostgreSQL. Can be: 0, uncompressed, default value; 1, ZIP compression; 2, ZLIB compression (ZIP plus metadata and CRC)

SM4 encryption function

SM4 is a symmetric encryption algorithm, which is a two-way encryption. It is the commercial cryptographic algorithm standard issued by the China State Cryptography Administration. It adopts 128-bit packets and keys to achieve efficient data encryption and decryption through multiple rounds of iterations, and is widely used in mobile communications, Internet of Things, finance and other fields.

  #encryption
  encrypt(data text, psw text [, options text ]) returns bytea;
  #Decryption
  decrypt(msg bytea, psw text [, options text ]) returns bytea;

  #Application example
  create table t1 (c1 bytea);

  insert into t1 select encrypt('123456', '1462846455', 'sm4');

   select * from t1;
                   c1                 
  ----------------------------------------------------------------------------------------------------------------------------------
   \xaf8aac27b4721d733852c18e9a6dd126
  (1 row)

  select convert_from(public.decrypt(c1, '1462846455', 'sm4'),'utf-8') from t1;
   convert_from 
  --------------
   123456
  (1 row)

  # Common options are:
  # cipher-algo, the password algorithm used is sm4

Storage encryption

One-way encryption

Two-way encryption