SQL Function Reference

Every function below is registered by CREATE EXTENSION pg_vault_tde. The Privilege column reflects the actual grants in the extension’s install script, not a general assumption — it tells you exactly which role can call each function without being a superuser.

Diagnostics and Health

These are executable by any role by default — PostgreSQL’s standard rule for newly created functions applies here (no REVOKE was applied to them). If your security policy requires restricting diagnostics to specific roles, run REVOKE EXECUTE ... FROM PUBLIC yourself after installing the extension.

Function Returns Description
pg_vault_tde_health_check() table: version, enabled, kms_provider, enc_ops_available, checked_at Single-row diagnostic snapshot of the extension
pg_vault_tde_verify_integrity(rel regclass) record: total_tuples, failed_tuples GCM authentication-tag scan over every tuple in a table — a failed tuple means tampering or corruption, not a transient error
pg_vault_tde_encrypted_size(rel regclass) record: total_tuples, encryption_overhead_bytes Encryption storage overhead for a table
pg_vault_tde_hw_accel_info() record: openssl_version, configured_provider, provider_loaded, gcm_cipher, siv_cipher, aes_ni_available OpenSSL provider/cipher diagnostics — confirms which hardware acceleration path is active
pg_vault_tde_vault_status() table: configured, auth_method, reachable Vault/OpenBao connectivity diagnostics
pg_vault_tde_refresh_token() boolean Manually renew the current Vault token lease
pg_vault_tde_get_rotation_status(rel regclass) table: status, tuples_done, tuples_total, pct_complete, started_at, updated_at Online rotation progress for one relation
pg_vault_tde_rotation_status view All in-progress/completed rotations across the cluster (also directly GRANTed to pg_monitor)

Table and Index Operations

Function Returns Privilege Description
pg_vault_tde_reencrypt_table(rel regclass | text, batch_size int DEFAULT 1000) void pg_monitor + superuser Batch re-encrypt every row with the current DEK; locks the table for the duration
pg_vault_tde_rotate_online(rel regclass, batch_size int DEFAULT 1000) void Any role* Online DEK rotation, no exclusive lock; accepts encrypted_heap tables and tde_btree indexes (see Key Rotation)
pg_vault_tde_check_plaintext_index_keys() table: table_name, index_name, column_name, opclass_name, suggestion pg_monitor + superuser Lists tde_btree indexes still using a pre-v1.6 plaintext operator class, with a ready-to-run REINDEX command in suggestion

* rotate_online has no explicit REVOKE, but still requires whatever table-level privileges PostgreSQL itself demands to rewrite the target relation.

Local Wallet

Function Returns Privilege Description
pg_vault_tde_wallet_init(passphrase text) void pg_monitor + superuser Create the local wallet and generate its KEK (first-time setup)
pg_vault_tde_wallet_status() table: wallet_exists, wallet_open, kek_algorithm, last_opened, file_perms pg_monitor + superuser Wallet diagnostics
pg_vault_tde_wallet_unlock(passphrase text) void Superuser only Interactive wallet unlock — no server restart needed
pg_vault_tde_wallet_lock() void Superuser only Evict all DEKs from shared memory and mark the wallet closed
pg_vault_tde_wallet_change_passphrase(old_passphrase text, new_passphrase text) void Superuser only Re-protect the wallet under a new passphrase; also rotates the KEK — do not call rotate_kek() separately afterward
pg_vault_tde_migrate_vault_to_wallet(new_passphrase text) void Superuser only Online migration from the Vault provider to a local wallet; the wallet must already exist

PKCS#11 / HSM

Function Returns Privilege Description
pg_vault_tde_pkcs11_keygen() void Superuser only One-time AES-256 KEK provisioning on the token; refuses to overwrite an existing key

Key Rotation

Function Returns Privilege Description
pg_vault_tde_rotate_kek() void Superuser only Rotate the KEK and re-wrap all per-table DEKs; works across all three providers; no tuple data is touched

Backup Key Sealing

Function Returns Privilege Description
pg_vault_tde_seal_keys(dest_path text, seal_passphrase text, label text DEFAULT 'basebackup') void Superuser only Write an HMAC-SHA256-signed bundle of every wrapped DEK to dest_path (mode 0600); the KEK is never included
pg_vault_tde_seal_keys_bytea(seal_passphrase text, label text DEFAULT 'basebackup') bytea Superuser only Same bundle, returned as bytea for a remote client to store (used internally by pg_basebackup_tde)
pg_vault_tde_unseal_keys(src_path text, seal_passphrase text) void Superuser only Verify (HMAC) and re-import a sealed bundle; rejects a tampered file or wrong passphrase before writing anything

Access Methods

CREATE TABLE t (...) USING encrypted_heap;
CREATE INDEX ON t USING tde_btree (col);
Access Method Type Purpose
encrypted_heap TABLE AES-256-GCM encryption of all user-data columns
tde_btree INDEX AES-256-SIV deterministic (equality-only) encryption of B-Tree index keys

See Also