Extentions

Used to backup whole databases / tables

Basic usage

Use \dx to check the current installed extentions

SQL Terminal


\dx
 
users=# \dx
                  List of installed extensions
   Name   | Version |   Schema   |         Description          
----------+---------+------------+------------------------------
 pgcrypto | 1.3     | public     | cryptographic functions
 plpgsql  | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

`pgcrypto`

Let use pgcrypto as an examples

Create a databse for examples

SQL Terminal


# Create DB
CREATE DATABASE enc_test;
 
# Goto this DB
\c enc_test
 
# Enable pgcrypto for this DB
CREATE EXTENSION IF NOT EXISTS pgcrypto;
 
# Create Table for demo
CREATE TABLE users_data (
    users_id SERIAL PRIMARY KEY,
    username varchar(50),
    password text
);

- pgp (Reversible Encrypt)

Used for data that need’s to be decrypt to original data.

Let’s use pgp_sym_encrypt to encrypt input data:

SQL Terminal


# Insert Dummy Data
INSERT INTO users_data (username, password) VALUES 
    ('peter', PGP_SYM_ENCRYPT('123', 'my_sEcRet_K0y')),
    ('may'  , PGP_SYM_ENCRYPT('abc3od', 'my_sEcRet_K0y'));
 
# View Table
Select * FROM users_data;
 
# Get decrypt Data with query
SELECT username, 
pgp_sym_decrypt(password::bytea, 'my_sEcRet_K0y') as password
FROM users_data;

For Drizzle Usage:

server.ts


import { db } from "../../db/db";
import { sql } from "drizzle-orm";
import { usersData } from "../../db/schema";
import { randomUUID } from "crypto";
 
export async function readUserByInfoSelect() {
    const userFound = await db.select(
        {
            username: usersData.username,
            password : sql`pgp_sym_decrypt(password::bytea, 'my_sEcRet_K0y')`.as('password')
        }
    ).from(usersData)
 
    return userFound;
}
 
export async function readUserByInfo() {
    const userFound = await db.query.usersData.findMany({
        with: {
            password: false
        },
        extras: {
            password : sql`pgp_sym_decrypt(password::bytea, 'my_sEcRet_K0y')`.as('password'),
        },
    });
 
    return userFound;
}
 
export async function insertUserByInfo() {
    await db.insert(usersData)
        .values({
            "username": randomUUID(),
            "password": sql`PGP_SYM_ENCRYPT(${randomUUID()}, 'my_sEcRet_K0y')` 
        })
}

- crypt (Un-Reversible Encrypt)

Using crypt() and gen_salt() to hash the password.

SQL Terminal


# Insert Dummy Data
INSERT INTO users_data (username, password) VALUES 
    ('peter', crypt('123', gen_salt('md5'))),
    ('may'  , crypt('ab3e2', gen_salt('md5')));
 
# View Table
Select * FROM users_data;
 
## Result
users_id | username |              password              
----------+----------+------------------------------------
       10 | peter    | $1$wjEW2rT1$lc8rjwRA32oZPYD1SpC0e/
       11 | may      | $1$AxLdYCVA$efK.pxj7w4wL9KejqfRA..

Check PW with crypt().

SQL Terminal


# Check PW with crypt functions
SELECT *
FROM users_data
WHERE username = 'peter'
AND password = crypt('123', password);

References:
https://www.postgresql.org/docs/current/pgcrypto.html#PGCRYPTO-PASSWORD-HASHING-FUNCS-GEN-SALT