PostgreSQL 사용법

ldd --version | head -1
systemctl is-active postgresql-18
sudo -iu postgres psql -Atc "SELECT version()" -c "SHOW server_encoding" -c "SELECT datname FROM pg_database WHERE datname = 'appdb'"
command -v gcc make curl sha256sum tar
rpm -q postgresql18-devel redhat-rpm-config

sudo dnf install -y --enablerepo=crb postgresql18-devel gcc make redhat-rpm-config

PG_CONFIG=/usr/pgsql-18/bin/pg_config
mkdir -p ~/strcrypt-postgresql && cd ~/strcrypt-postgresql
curl -fLO https://www.cubiware.co.kr/api/download/strcrypt/0.3.1/libstrcrypt.so
curl -fLO https://www.cubiware.co.kr/api/download/strcrypt/0.3.1/libstrcrypt.so.hmac
curl -fLO https://www.cubiware.co.kr/api/download/strcrypt/0.3.1/strcrypt-postgresql-0.3.1.tar.gz
curl -fLO https://www.cubiware.co.kr/api/download/strcrypt/0.3.1/SHA256SUMS
sha256sum -c --ignore-missing SHA256SUMS
tar xzf strcrypt-postgresql-0.3.1.tar.gz && cd strcrypt-postgresql-0.3.1
sudo install -m 755 ../libstrcrypt.so "$($PG_CONFIG --pkglibdir)/"
sudo install -m 644 ../libstrcrypt.so.hmac "$($PG_CONFIG --pkglibdir)/"
make PG_CONFIG="$PG_CONFIG" STRCRYPT_LIB_DIR="$($PG_CONFIG --pkglibdir)" STRCRYPT_INCLUDE_DIR=. with_llvm=no
sudo install -m 755 pg_strcrypt.so "$($PG_CONFIG --pkglibdir)/"
sudo install -m 644 pg_strcrypt.control pg_strcrypt--1.1.sql pg_strcrypt--1.0--1.1.sql "$($PG_CONFIG --sharedir)/extension/"
ldd "$($PG_CONFIG --pkglibdir)/pg_strcrypt.so" | grep strcrypt

sudo install -d -o postgres -g postgres -m 700 /etc/strcrypt
sudo -u postgres sh -c 'umask 077 && set -C && head -c 24 /dev/urandom | base64 | head -c 32 > /etc/strcrypt/strcrypt.key'
sudo -u postgres stat -c '%a %U %s %n' /etc/strcrypt/strcrypt.key

sudo mkdir -p /etc/systemd/system/postgresql-18.service.d
printf '[Service]\nEnvironment=STRCRYPT_KEY_FILE=/etc/strcrypt/strcrypt.key\n' | sudo tee /etc/systemd/system/postgresql-18.service.d/strcrypt.conf
sudo systemctl daemon-reload
sudo systemctl restart postgresql-18
systemctl show -p Environment postgresql-18 | grep -o 'STRCRYPT_[A-Z_]*=[^ ]*'

sudo -iu postgres psql -d appdb -v ON_ERROR_STOP=1 -c "CREATE EXTENSION IF NOT EXISTS pg_strcrypt" -c "\dx pg_strcrypt" -c "SELECT strcrypt_decrypt(strcrypt_encrypt('test', 'aria256gcm'))"

BEGIN;
REVOKE EXECUTE ON FUNCTION strcrypt_encrypt(text), strcrypt_encrypt(text, text),
                           strcrypt_decrypt(text), strcrypt_decrypt(text, text),
                           strcrypt_search_index(text, text),
                           strcrypt_algorithms(), strcrypt_hw_info() FROM PUBLIC;
GRANT EXECUTE ON FUNCTION strcrypt_encrypt(text), strcrypt_encrypt(text, text),
                          strcrypt_decrypt(text), strcrypt_decrypt(text, text),
                          strcrypt_search_index(text, text),
                          strcrypt_algorithms(), strcrypt_hw_info() TO APP_USER;
COMMIT;

SHOW server_encoding;
SELECT strcrypt_decrypt(strcrypt_encrypt('test', 'aria256gcm'));
SELECT count(*) AS checked,
       count(*) FILTER (WHERE strcrypt_decrypt(strcrypt_encrypt(v, 'aria256gcm')) IS DISTINCT FROM v) AS mismatches
FROM (SELECT 'row-' || g || '-홍길동' AS v FROM generate_series(1, 1000) g) s;

CREATE TABLE customers (id bigint PRIMARY KEY, name text, ssn text, ssn_enc text);
INSERT INTO customers (id, name, ssn_enc)
VALUES (1, '홍길동', strcrypt_encrypt('900101-1234567', 'aria256gcm'));
SELECT id, name, strcrypt_decrypt(ssn_enc) AS ssn FROM customers;
SELECT strcrypt_encrypt('alice@example.com', 'seed128gcm');

mkdir -p ~/strcrypt && cd ~/strcrypt
curl -fLO https://www.cubiware.co.kr/api/download/strcrypt/0.3.1/strcrypt
curl -fLO https://www.cubiware.co.kr/api/download/strcrypt/0.3.1/strcrypt.hmac
curl -fLO https://www.cubiware.co.kr/api/download/strcrypt/0.3.1/SHA256SUMS
sha256sum -c --ignore-missing SHA256SUMS
chmod +x strcrypt
sudo install -o "$USER" -m 600 /etc/strcrypt/strcrypt.key ~/strcrypt/strcrypt.key

cd ~/strcrypt
export STRCRYPT_KEY_FILE="$HOME/strcrypt/strcrypt.key"
export PGHOST=localhost PGDATABASE=appdb PGUSER=APP_USER
set -o pipefail
umask 077
last=0
while :; do
  psql -X -At -F $'\t' -v ON_ERROR_STOP=1 -c "
    SELECT id, translate(encode(convert_to(ssn, 'UTF8'), 'base64'), E'\n', '')
      FROM customers
     WHERE id > $last AND ssn IS NOT NULL AND ssn_enc IS NULL
     ORDER BY id LIMIT 1000" > chunk.tsv || break
  [ -s chunk.tsv ] || break
  cut -f2 chunk.tsv | ./strcrypt encrypt-batch -a aria256gcm -o chunk.enc || break
  paste <(cut -f1 chunk.tsv) chunk.enc > chunk.load
  psql -X -q -v ON_ERROR_STOP=1 <<'SQL' || break
BEGIN;
CREATE TEMP TABLE enc_chunk (id bigint PRIMARY KEY, ssn_enc text) ON COMMIT DROP;
\copy enc_chunk FROM 'chunk.load'
UPDATE customers c SET ssn_enc = e.ssn_enc FROM enc_chunk e WHERE c.id = e.id;
COMMIT;
SQL
  last=$(tail -n 1 chunk.tsv | cut -f1)
  echo "encrypted up to id $last"
done
rm -f chunk.tsv chunk.enc chunk.load

SELECT count(*) AS total,
       count(ssn_enc) AS encrypted,
       count(*) FILTER (WHERE ssn IS NOT NULL AND strcrypt_decrypt(ssn_enc) IS DISTINCT FROM ssn) AS mismatches
FROM customers;

cd ~/strcrypt
export STRCRYPT_KEY_FILE="$HOME/strcrypt/strcrypt.key"
export PGHOST=localhost PGDATABASE=appdb PGUSER=APP_USER
set -o pipefail
umask 077
psql -X -At -F $'\t' -v ON_ERROR_STOP=1 -c "SELECT id, ssn_enc FROM customers WHERE ssn_enc IS NOT NULL ORDER BY id" > enc.tsv
cut -f2 enc.tsv | ./strcrypt decrypt-batch | paste <(cut -f1 enc.tsv) - > dec.load
psql -X -v ON_ERROR_STOP=1 <<'SQL'
CREATE TEMP TABLE dec_rows (id bigint PRIMARY KEY, ssn_b64 text);
\copy dec_rows FROM 'dec.load'
SELECT count(*) AS checked,
       count(*) FILTER (WHERE convert_from(decode(d.ssn_b64, 'base64'), 'UTF8') IS DISTINCT FROM c.ssn) AS mismatches
FROM dec_rows d JOIN customers c USING (id);
SQL
rm -f enc.tsv dec.load