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](https://github.com/HexaCluster/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: ```bash # 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](https://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](https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_DDL.html) 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](https://www.postgresql.org/docs/current/infoschema-routines.html), 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](https://www.postgresql.org/docs/current/sql-createlanguage.html) 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. ```sql 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](https://github.com/HexaCluster/plpgsql_wrap), 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](https://www.postgresql.org/docs/current/logical-replication.html), [streaming replication](https://www.postgresql.org/docs/current/runtime-config-replication.html), 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](https://datatracker.ietf.org/doc/html/rfc5288). You can see the property directly. Re-create the same function twice and capture both blobs: ```sql 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](https://www.openssl.org/docs/man3.0/man3/EVP_EncryptInit.html) 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](https://www.postgresql.org/docs/current/app-pgdump.html) emits the encrypted blob verbatim: ```sql -- 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: ```sql 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: ```sql 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](https://www.hexacluster.ai) 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: ```sql 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`: ```text === 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: ```sql 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: ```text === 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](https://www.hexarocket.com) 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](https://www.hexacluster.ai) 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](https://www.hexacluster.ai). And if you just want to read the code, the [plpgsql_wrap repository on GitHub](https://github.com/HexaCluster/plpgsql_wrap) 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](https://hexacluster.ai/application-migration-and-modernization), [database migration](https://hexacluster.ai/database-migration-to-postgresql), and PostgreSQL consulting such as [performance tuning](https://hexacluster.ai/performance-tuning), [health audits](https://hexacluster.ai/architectural-health-audit), [managed DBA services](https://hexacluster.ai/managed-dba-services), and [24/7/365 support](https://hexacluster.ai/support-plans). To start a conversation or explore how we can support your migration journey, please contact us at connect@hexacluster.ai
[Subscribe to our Newsletters](https://hexacluster.ai/#:~:text=Subscribe%20to%20Newsletters) and Stay tuned for more interesting topics.

⚡ Automated Schema Conversion

🔄 Real-Time Data Movement

🚀 End-to-End Modernization