plpgsql_wrap: Oracle WRAP for PostgreSQL PL/pgSQL Encryption

If your team is migrating workloads from Oracle to PostgreSQL, one of the first questions a security or compliance reviewer asks is: "What replaces Oracle WRAP?" In Oracle, WRAP using DBMS_DDL.WRAP, lets you ship PACKAGE and PROCEDURE source as opaque ciphertext, so that competitors, contractors, or even your own DBAs can't read the business logic embedded in a database. Until now, PostgreSQL had no equivalent. PL/pgSQL source has always been visible in pg_proc.prosrc to anyone with the right SELECT privilege. This is the gap that plpgsql_wrap fills: a PostgreSQL extension that brings true PL/pgSQL encryption to the database, transparently to your application code. To support this feature, HexaCluster has just released an extension called plpgsql_wrap. With plpgsql_wrap, stored procedure source is AES-256-GCM encrypted at CREATE time, stored as ciphertext inside pg_proc, and stays encrypted through pg_dump, pg_restore, logical replication, and pg_upgrade.

---

---

Let us see a simple quick setup and move on to why it matters the most.

Quick start

If you want to try it on a sandbox PostgreSQL 12+ instance:

# 1. Build with a random key (or supply your own 64 hex chars)
export WRAP_KEY_HEX=$(openssl rand -hex 32)
echo $WRAP_KEY_HEX           # back this up
git clone https://github.com/HexaCluster/plpgsql_wrap
cd plpgsql_wrap
make WRAP_KEY_HEX=$WRAP_KEY_HEX
sudo make install

# 2. In each database that needs it
psql -d yourdb -c "CREATE EXTENSION plpgsql_wrap;"

# 3. Replace LANGUAGE plpgsql with LANGUAGE plpgsql_wrap on
#    any function whose source you want to protect.

The source is on GitHub at github.com/HexaCluster/plpgsql_wrap, released under the PostgreSQL License.

---

Why source-code confidentiality matters in PostgreSQL

Most stored-procedure code is operational glue: it may be a bonus formula, a fraud-detection threshold, or a pricing-tier table. Individually unremarkable; collectively, the kind of thing legal departments call "trade secrets" and auditors call "scope items." On Oracle, DBMS_DDL.WRAP has been the standard answer for two decades. Teams running on Oracle build Oracle WRAP into their deployment pipelines and forget about it.

When that team migrates their oracle databases to PostgreSQL, the conversation happends in a different way. The PostgreSQL's pg_proc catalog stores procedure bodies in plain text format. The \sf procedure_name, the pg_get_functiondef(), information_schema.routines, and a casual pg_dump all expose the same source code. This creates a backtrack for Organisations who wrapped their business logic decades ago and PostgreSQL exposes it.

So now, the plpgsql_wrap closes that gap without changing what developers have wrapped, and without bolting on an external key-management dependency at runtime.

Let us see how we can wrap procedure body in PostgreSQL using plpgsql_wrap.

---

How procedure body is wrapped inside PostgreSQL with plpgsql_wrap.

plpgsql_wrap is just an extension to procedural language that delivers PL/pgSQL encryption at DDL creation time. You write functions exactly the way you write PL/pgSQL (same BEGIN/END, same RAISE, same RETURN), but you declare them with LANGUAGE plpgsql_wrap instead of LANGUAGE plpgsql. At CREATE FUNCTION time the extension's validator hook syntax-checks the body, encrypts it with AES-256-GCM, and writes the ciphertext directly into pg_proc.prosrc. Anyone who later runs SELECT prosrc FROM pg_proc WHERE proname = 'calculate_bonus' sees an opaque blob; the original source never persists in the catalog tables.

The following Create Function example will deliver the above explanation as is.

CREATE OR REPLACE FUNCTION hr.calculate_bonus(p_emp_id int, p_year int)
RETURNS numeric
LANGUAGE plpgsql_wrap
AS $$
DECLARE
    v_salary numeric;
    v_factor numeric := 0.15;
BEGIN
    SELECT salary INTO v_salary FROM hr.employees WHERE emp_id = p_emp_id;
    RETURN round(v_salary * v_factor, 2);
END;
$$;

SELECT substring(prosrc, 1, 50) || '...' AS stored
FROM   pg_proc WHERE proname = 'calculate_bonus';
--                          stored
-- -------------------------------------------------------
--  PLPGSQLWRAP:1:57524150013b778e13b52cb6acc4d810c7d60d...

The function is called the way any other PL/pgSQL function is called. Permissions, overloading, search paths, EXPLAIN, and every other catalog-level mechanism work normally. Only the source bytes are different.

---

How the PL/pgSQL encryption flow works

There are three moving pieces that together implement PL/pgSQL encryption without disturbing the rest of the catalog:

1. A compile-time key. A 256-bit AES key is baked into the plpgsql_wrap.so library at make time, either via wrap_key.h or with make WRAP_KEY_HEX=$(openssl rand -hex 32). The key never leaves the binary; there is no GUC, no on-disk key file, no environment variable. The threat model matches Oracle WRAP: protect against database-level inspection, not against an OS-level adversary who controls the binary.

2. A validator hook. When you run CREATE FUNCTION ... LANGUAGE plpgsql_wrap, the validator hook reads prosrc back from pg_proc, runs the real plpgsql validator on it so syntax errors are caught and rolled back, then AES-256-GCM encrypts it and writes a PLPGSQLWRAP:1: blob back. Plain source never persists on disk. If the validator rolls back, pg_proc never holds the row at all.

3. A call handler. At call time, the handler decrypts the blob, hands the plain source to the real PL/pgSQL engine, and re-encrypts after the call returns. The decrypted text lives only in memory, and explicit_bzero is called before the buffer is freed.

The full design, covering the validator decision tree, wire format, and key management notes, is documented in the README of the open-source repository.

---

The wire format

prosrc is plain ASCII hex so it survives every PostgreSQL plumbing path that handles text columns: pg_dump, COPY, logical replication, streaming replication, and pg_upgrade. The layout is:

"PLPGSQLWRAP:1:" + lowercase_hex(blob)

blob:
  [ "WRAP"        4 bytes ]  magic
  [ 0x01          1 byte  ]  version
  [ nonce        12 bytes ]  fresh per wrap (AES-GCM IV)
  [ auth tag     16 bytes ]  GCM authentication tag
  [ ciphertext    N bytes ]  the encrypted PL/pgSQL source

Every fresh CREATE OR REPLACE generates a new 96-bit nonce, so the ciphertext changes even when the source body is byte-identical. That is exactly what you want from a modern authenticated encryption mode. You can see the property directly. Re-create the same function twice and capture both blobs:

CREATE OR REPLACE FUNCTION hr.discount_for(p_amount numeric)
RETURNS numeric LANGUAGE plpgsql_wrap AS $$
BEGIN
    IF p_amount > 10000 THEN RETURN p_amount * 0.10; END IF;
    RETURN p_amount * 0.05;
END;
$$;
SELECT prosrc AS v1 FROM pg_proc WHERE proname='discount_for' \gset

CREATE OR REPLACE FUNCTION hr.discount_for(p_amount numeric)
RETURNS numeric LANGUAGE plpgsql_wrap AS $$
BEGIN
    IF p_amount > 10000 THEN RETURN p_amount * 0.10; END IF;
    RETURN p_amount * 0.05;
END;
$$;

SELECT prosrc <> :'v1'                AS reroll_changed,
       length(prosrc) = length(:'v1') AS same_length
FROM   pg_proc WHERE proname='discount_for';
--  reroll_changed | same_length
-- ----------------+-------------
--  t              | t

Same source, identical length on the wire, completely different ciphertext bytes. That property comes from the fresh nonce, and it's what makes the wrapped output resistant to comparison attacks across deployments.

If the blob is altered in any way, even a single hex digit flipped, the GCM authentication tag check fails on the next call, and plpgsql_wrap raises:

ERROR:  plpgsql_wrap: authentication failed -- wrong compile-time key or tampered blob

You don't get silent corruption. You get a hard, auditable failure.

---

So, that's the working logic, now what happens when we dump procedures or restore them to another database? We knew dump holds complete CREATE PROCEDURE source code, do we get the same source code as we generally see in our dumps, let's look at dump and restore part here.

Working with pg_dump and pg_restore

This is the part that most teams underestimate. With plpgsql_wrap, pg_dump emits the encrypted blob verbatim:

-- pg_dump output (excerpt)
CREATE OR REPLACE FUNCTION hr.calculate_bonus(p_emp_id integer, p_year integer)
    RETURNS numeric
    LANGUAGE plpgsql_wrap
    AS $$PLPGSQLWRAP:1:5752415001f046661259ed5afb7f0ebe7204b7e51f...$$;

There is no plaintext anywhere in the dump file. When pg_restore (or plain psql -f dump.sql) replays this against a target that has plpgsql_wrap installed with the same compile-time key, the validator recognizes the PLPGSQLWRAP:1: prefix, runs the GCM tag check, and stores the blob unchanged. There is no re-encryption, no re-validation, no plain-text round trip.

The practical consequence: your dump files are safe to hand to a third-party migration vendor, a backup retention service, or a junior engineer. They contain encrypted business logic, not source. If the dump is replayed on a server without the matching .so, the GCM tag will reject every function at its first call, and the failure is loud and immediate.

---

Until now, We obfuscated the source code, what if we need the function body/logic to alter something and recreate it with new logic, For that, plpgsql_wrap hands us a function to unwrap the body.

Unwrapping when you need plain source back

Encryption that you can't reverse on demand is a foot-gun, not a feature. plpgsql_wrap ships a superuser-only helper that converts a wrapped function back to ordinary PL/pgSQL in place:

SELECT plpgsql_wrap.unwrap_procedure(
    'ce257a60df2813f2bbf683f1ac0b17a3...',  -- the hex key
    'hr', 'calculate_bonus');

After this runs, prolang becomes plpgsql, prosrc is a plain source, and the function is indistinguishable from one that was created with LANGUAGE plpgsql in the first place. This is the path you use when:

• You're rotating to a new compile-time key (unwrap, install new .so, re-wrap)
• You need to ship plain source to a forensic auditor
• You're doing a controlled debug session on a non-production replica

A companion view, plpgsql_wrap.list_wrapped(), returns metadata for every wrapped function in the database (schema, name, return type, blob length) without ever exposing key material or plaintext, which makes it suitable for monitoring dashboards:

SELECT * FROM plpgsql_wrap.list_wrapped();
--  schema |    func_name    |          identity_args           | return_type | wrapped_chars
-- --------+-----------------+----------------------------------+-------------+---------------
--  hr     | calculate_bonus | p_emp_id integer, p_year integer | numeric     |           770
--  hr     | record_bonus    | p_emp_id integer, p_year integer | numeric     |           526
--  hr     | tier_for        | p_salary numeric                 | text        |           480

This view is safe to grant to a monitoring role. It exposes only the shape of the wrapped surface, not the contents.

---

The security model: what it protects, and what it doesn't

It's worth being precise here, because security people will ask:

Threat	Outcome
SELECT prosrc FROM pg_proc by a non-superuser	Returns ciphertext only
\sf or pg_get_functiondef()	Returns the wrapped CREATE FUNCTION text
pg_dump output reaching a third party	Contains the wrapped blob; useless without the .so
pg_restore to a server with the wrong key	Restore succeeds; first call fails with GCM auth error
Tampered blob in dump or in pg_proc	First call after tampering fails with auth error
Syntax-broken source submitted to the validator	CREATE rolls back; no pg_proc row ever exists
OS-level attacker with root on the database host	Can extract the key from the .so; out of scope

The last row is the same caveat Oracle WRAP carries: this is a database-layer protection, not a defense against someone who controls the operating system. If that's your threat model, you need disk encryption and HSM-managed keys on top of this, and HexaCluster's PostgreSQL consulting team can help you design that stack.

---

Side by side: Oracle WRAP and plpgsql_wrap on the same function

The shortest way to show what plpgsql_wrap does is to build the same trivial bonus function on both sides and compare what a curious DBA can read back out of the catalog.

The function

A salary-bonus calculator with a tenure bump. Same logic on both databases:

Wrapping in Oracle version

Oracle's mechanism is the DBMS_DDL.WRAP package. You pass the PL/SQL text in, you get an obfuscated CREATE OR REPLACE back, you run that as DDL:

DECLARE
    src  DBMS_SQL.VARCHAR2A;
    wrap DBMS_SQL.VARCHAR2A;
    v_sql CLOB := '';
BEGIN
    src(1) := 'CREATE OR REPLACE FUNCTION sample_bonus_wrap(p_salary IN NUMBER, p_tenure IN NUMBER)';
    src(2) := 'RETURN NUMBER AS v_factor NUMBER := 0.15;';
    src(3) := 'BEGIN';
    src(4) := '    IF p_tenure >= 5 THEN v_factor := v_factor + 0.05; END IF;';
    src(5) := '    RETURN ROUND(p_salary * v_factor, 2);';
    src(6) := 'END;';

    wrap := DBMS_DDL.WRAP(ddl => src, lb => 1, ub => src.LAST);
    FOR i IN wrap.FIRST..wrap.LAST LOOP
        v_sql := v_sql || wrap(i) || CHR(10);
    END LOOP;
    EXECUTE IMMEDIATE v_sql;
END;
/

When querying Oracle's USER_SOURCE:

=== Wrapped function: opaque payload in USER_SOURCE ===
1:  FUNCTION sample_bonus_wrap wrapped
a000000
369
abcd
abcd
abcd
abcd
abcd
ab

The wrapped output is Oracle's encoded blob. The DBA who runs SELECT text FROM user_source gets the header line, a length, and base64-style ciphertext for the body.

Wrapping in PostgreSQL version

The PostgreSQL flow is shorter because plpgsql_wrap does the wrapping inside the validator, so the DDL itself is unchanged except for the LANGUAGE token:

CREATE OR REPLACE FUNCTION sample_bonus_wrap(p_salary numeric, p_tenure numeric)
RETURNS numeric LANGUAGE plpgsql_wrap AS $$
DECLARE
    v_factor numeric := 0.15;
BEGIN
    IF p_tenure >= 5 THEN
        v_factor := v_factor + 0.05;
    END IF;
    RETURN round(p_salary * v_factor, 2);
END;
$$;

Reading from pg_proc.prosrc for function body gives:

=== Wrapped function: opaque payload in pg_proc.prosrc ===
PLPGSQLWRAP:1:5752415001fd39d0fb4aae8db4678aac0e0989f19be421b8dc70232284d9ed320a...

The shape is different (Oracle uses an ASCII header plus base64 body; PostgreSQL uses a PLPGSQLWRAP:1: prefix plus hex-encoded AES-256-GCM ciphertext), but the contract for the DBA is the same: nothing readable comes back from the catalog.

Where the two implementations agree and where they differ

Aspect	Oracle WRAP	plpgsql_wrap on PostgreSQL
API surface	DBMS_DDL.WRAP produces a wrapped CREATE script	LANGUAGE plpgsql_wrap in normal DDL
What the catalog stores	Encoded text in USER_SOURCE / DBA_SOURCE	Encoded text in pg_proc.prosrc
Underlying algorithm	Oracle-proprietary obfuscation (documented community-reversed)	AES-256-GCM with a fresh nonce per wrap
Key	None; the obfuscation is the same on every Oracle install	256-bit key baked into the extension .so at build time
Tamper detection	None; a hand-edited blob is undefined behavior at runtime	GCM authentication tag rejects any modification
Re-wrap reproducibility	Same source produces the same wrapped output	Same source produces a different blob every time (fresh nonce)
Round-trips through dump/export	expdp/exp preserve wrapped source	pg_dump preserves the wrapped blob byte-for-byte
Recovery of plain source	DBMS_DDL.UNWRAP does not exist; recovery requires keeping the source separately	plpgsql_wrap.unwrap_procedure(key, schema, name) if you have the key

The two systems give the migration team feature parity on the catalog side. The PostgreSQL implementation is stronger on cryptographic primitives (modern AEAD with a real key, real authentication tag, fresh nonces), and Oracle is stronger on the "this Just Works on every install with no key management" axis. For an Oracle-to-PostgreSQL migration, the practical effect is that any procedure that was sensitive enough to wrap in Oracle has a direct equivalent on the PostgreSQL side.

---

Where it fits in an Oracle-to-PostgreSQL migration

For teams in the middle of an Oracle migration, plpgsql_wrap is one of the small, late-stage items that becomes a blocker if it isn't planned for. Anything that was previously protected with Oracle WRAP needs an equivalent protection on the PostgreSQL side, and PL/pgSQL encryption through this extension is the cleanest mapping. The pattern we see at HexaCluster looks like this:

1. Discovery phase. Use HexaRocket to inventory the Oracle source and flag every wrapped PACKAGE BODY or PROCEDURE. Wrapped objects are usually a small percentage of total LOC, but they're often the highest-value code in the database: pricing engines, eligibility rules, anti-fraud checks.

2. Conversion phase. Unwrap the Oracle source (the customer keeps these keys), convert to PL/pgSQL, validate behavior on a PostgreSQL target.

3. Re-wrap phase. Before promoting to production, re-create the sensitive procedures with LANGUAGE plpgsql_wrap. The change to the deployment script is a single token. Every other tool in the pipeline (Liquibase, Flyway, Sqitch, pg_dump-based blue/green cutovers) continues to work unchanged because plpgsql_wrap produces ordinary CREATE FUNCTION DDL.

4. Operations phase. Build into your pg_dump retention policy the assumption that dump files are now encrypted-by-default for the wrapped subset. Document the compile-time key in your key-management system. Make sure the .so is part of your standard PostgreSQL image so disaster-recovery rebuilds don't lose it.

This kind of staged migration is exactly the work HexaCluster's Oracle-to-PostgreSQL migration services are set up to do, and tools like plpgsql_wrap are part of why a PostgreSQL target now meets the same compliance bar that the Oracle source met.

🔹 HexaRocket Recommendation

To effectively handle these differences and ensure a seamless migration between databases, we recommend using HexaRocket to automate and manage your migration process with precision. With the help of HexaRocket and the experts at HexaCluster, we can test these edge cases early in your database migration to PostgreSQL, ensuring that your data integrity remains solid and your application behaves predictably across different database platforms.

---

Wrapping up

PostgreSQL has spent the last decade closing the feature gap with Oracle: partitioning, parallel query, JSON, logical replication, MERGE. Source-code confidentiality, the thing Oracle WRAP has provided for two decades, has been a stubborn holdout. Every audit, every compliance review, every conversation about IP protection ran into the same fact that pg_proc.prosrc was readable. plpgsql_wrap removes that excuse. PL/pgSQL encryption is now a build-time decision, not an architectural one.

For Oracle DBAs evaluating a PostgreSQL target, this means one less item on the "things we used to have that we now have to give up" list. For PostgreSQL DBAs inheriting a workload that's already been migrated, it means the answer to "how do we hide this code?" is no longer "you don't."

If you're scoping an Oracle-to-PostgreSQL migration and want to see how source protection fits into the wider plan (schema conversion, performance baselining, cutover), talk to the team at HexaCluster. And if you just want to read the code, the plpgsql_wrap repository on GitHub

If you need expert support migrating legacy or complex Oracle, SQL Server, MySQL, or MariaDB databases to PostgreSQL or distributed databases, we’re here to help.

HexaCluster provides end-to-end migration and modernization services, including application migration and modernization, database migration, and PostgreSQL consulting such as performance tuning, health audits, managed DBA services, and 24/7/365 support.

To start a conversation or explore how we can support your migration journey, please contact us at connect@hexacluster.ai

Subscribe to our Newsletters and Stay tuned for more interesting topics.