PostgreSQL Guide

System requirements

PostgreSQL

Tested on 18 (official PostgreSQL PGDG packages). Paths and the service name in the commands follow PGDG 18.

Database settings

Database encoding UTF8

Database server OS

Linux x86_64, glibc 2.28 or later (for example RHEL or Rocky Linux 8 or later)

Build tools

gcc, make, redhat-rpm-config, postgresql18-devel (needs the CRB repository), curl, tar, sha256sum (coreutils 8.25 or later)

Privileges

An OS account with sudo on the database server, and a database superuser (postgres)

PostgreSQL versions other than 18, Debian and Ubuntu packages, and SELinux enforcing configurations were not tested.

Where to run each step

The PostgreSQL backend process loads the extension library pg_strcrypt.so, and the extension calls libstrcrypt.so in the same directory. The libraries, key file, and settings all live on the database server. Nothing needs to be installed on the application server. The extension is installed separately in each database. Replace appdb in the commands with the application database name and APP_USER with the role that will encrypt and decrypt.

1. Pre-checks

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

This prints the glibc version, the service state, the PostgreSQL version, the database encoding (UTF8), and the target database name in order. command -v prints only the tools that are installed, and the last line prints is not installed for any missing build package.

Common errors

2. Build and install the libraries

Only if the build tools are missing

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

Download, verify, build, and install

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

The extension has to be built against the PostgreSQL headers on the database server, so it ships as source. The build runs under ~/strcrypt-postgresql; the three libraries go to pg_config --pkglibdir (/usr/pgsql-18/lib) and the two extension definition files go to the extension directory. The step succeeded if the last line prints libstrcrypt.so => /usr/pgsql-18/lib/libstrcrypt.so. This block can be run again.

Common errors

3. Generate the key file

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

This creates a 32-byte key in a file only the postgres account can read. The last line should print 600 postgres 32 /etc/strcrypt/strcrypt.key. If a key file already exists, the command stops instead of overwriting it. The key is never passed through SQL arguments or tables. If you lose the key, the ciphertext cannot be recovered, so keep a separate backup.

Common errors

Except for the last row, key file problems appear after ERROR: strcrypt: ... 환경 변수 오류: when SQL is called in step 5 or later.

4. Pass the key path to the server

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_]*=[^ ]*'

A systemd drop-in file passes the environment variable STRCRYPT_KEY_FILE to the PostgreSQL service. It is applied when the last line prints STRCRYPT_KEY_FILE=/etc/strcrypt/strcrypt.key. Database connections drop briefly during the restart. A value exported in your shell does not reach the server. To use algorithms outside the Korean standards, add the line Environment=STRCRYPT_MODE=non-approved.

Common errors

5. Install the extension

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'))"

This creates the extension in appdb and checks a round trip with the final query. The step succeeded if the output ends with test. Running it again adds extension "pg_strcrypt" already exists, skipping with the same result.

Common errors

6. Restrict execute privileges

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;

Extension 1.1 installs with PUBLIC execute revoked on the encryption and decryption functions. To also restrict the utility functions and grant access only to the application user, connect with sudo -iu postgres psql -d appdb -v ON_ERROR_STOP=1 and run this block. The output is BEGIN, REVOKE, GRANT, COMMIT. For several users, list them after TO separated by commas. This block can be run again, and must be run again if the extension is dropped and reinstalled. Superusers skip privilege checks and can read the key file through SQL, so do not use a superuser account for the application.

Common errors

7. Verify the installation

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;

Run this as the application user in the same database (for example psql -h localhost -d appdb -U APP_USER). The results should be UTF8, test, and checked 1000 | mismatches 0. The last query encrypts and decrypts 1,000 rows and compares them without creating a table.

Common errors

SQL functions

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');

Use them like ordinary functions in SELECT, INSERT, and UPDATE. NULL input gives NULL output. All functions can be used in parallel queries. Always name the algorithm when encrypting. Ciphertext from strcrypt_encrypt(input) without an algorithm has no header, so it no longer decrypts if the server operating mode (STRCRYPT_MODE) changes.

Ciphertext differs every time, even for the same value, so it cannot be indexed. For exact-match search, store strcrypt_search_index(value, 'table.column') in an indexed search column and compare it with the index of the search term. Do not use an unkeyed SHA-256 hash: guessable values such as national ID numbers can be recovered by hashing every candidate. A search column reveals which rows share a value, so add one only where search is needed, and grant execute on the search index function only to users who need it.

Bulk processing

PostgreSQL has no SQL function that takes many rows at once. Most of the time in a bulk UPDATE goes to table and index writes, so grouping calls inside SQL does not make it faster. For many rows, query chunks in primary key order outside the database, encrypt or decrypt each chunk with the CLI in one call, and apply the results by primary key. Ciphertext from the CLI can be decrypted with the SQL functions, and ciphertext from the SQL functions can be decrypted with the CLI.

Prepare the CLI

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

The CLI must use the same key as the database. The last line makes a copy of the key readable by the current account when you run the CLI on the database server. Delete the copy when you are done.

Encrypt an existing column in bulk

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

Each pass queries 1,000 rows of plaintext as Base64, encrypts them with encrypt-batch, loads them into a temporary table, applies them by primary key, and commits the chunk. Each chunk prints encrypted up to id .... Because of the ssn_enc IS NULL condition, running the same command again resumes where it stopped. Supply the password with ~/.pgpass or PGPASSWORD.

• encode(..., 'base64') inserts a line break every 76 characters. Without translate removing them, one row splits into several records and rows shift, and the CLI reports no error.
• An empty line is encrypted as an empty string, so the ssn IS NOT NULL condition cannot be dropped.
• The chunk files hold plaintext as Base64, so umask 077 makes them readable only by the current account, and they are deleted at the end.

Check the results (run as APP_USER)

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;

If mismatches is 0, all ciphertext produced by the CLI decrypted with the SQL function.

Decrypt ciphertext from the SQL functions with the CLI

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

decrypt-batch writes plaintext as Base64, so SQL turns it back with decode for the comparison. checked is the number of rows with ciphertext, and mismatches should be 0.

Limitations

• Only UTF8 databases are supported. The extension does not install in an EUC_KR database, and non-ASCII values fail in a LATIN1 database.
• The Base64 result must fit within the PostgreSQL value size limit (1 GB), which caps plaintext at about 800 MB.
• If any row in a statement fails, the whole statement ends with an error.
• The same value encrypts to a different ciphertext each time, so you cannot search or index the ciphertext column. Keep a separate hash column if you need lookups.
• Plaintext passes through the memory of the backend process on the database server. Plaintext written directly into SQL statements may be logged depending on the server log settings.
• Changes to the key file contents or permissions apply to new sessions; changes to environment variables apply after the server restarts.

API reference

All functions return text. Naming an algorithm the default mode does not allow raises 현재 모드(korean-standard)에서 허용되지 않는 알고리즘 (algorithm not allowed in the current mode).

Test environments

Steps 1 to 7, the SQL function example, the bulk processing example (a 2,345-row round trip including NULL, empty strings, line breaks, and 1 KB Korean values), and rejection of calls from users without a grant after the revoke were confirmed (2026-09-17).