plpgsql_wrap

Overview

PGDG RPM and Pigsty DEB package hexacluster/plpgsql_wrap 1.0; control requires plpgsql and superuser=true; links OpenSSL.

Version

Build

You can build the RPM / DEB packages for plpgsql_wrap using pig build:

pig build pkg plpgsql_wrap         # build RPM / DEB packages

Install

You can install plpgsql_wrap directly. First, make sure the PGDG repository is added and enabled:

pig repo add pgdg -u          # Add PGDG repo and update cache

Install the extension using pig or apt/yum/dnf:

pig install plpgsql_wrap;          # Install for current active PG version

pig ext install -y plpgsql_wrap -v 18  # PG 18
pig ext install -y plpgsql_wrap -v 17  # PG 17
pig ext install -y plpgsql_wrap -v 16  # PG 16
pig ext install -y plpgsql_wrap -v 15  # PG 15
pig ext install -y plpgsql_wrap -v 14  # PG 14

dnf install -y plpgsql_wrap_18       # PG 18
dnf install -y plpgsql_wrap_17       # PG 17
dnf install -y plpgsql_wrap_16       # PG 16
dnf install -y plpgsql_wrap_15       # PG 15
dnf install -y plpgsql_wrap_14       # PG 14

apt install -y postgresql-18-plpgsql-wrap   # PG 18
apt install -y postgresql-17-plpgsql-wrap   # PG 17
apt install -y postgresql-16-plpgsql-wrap   # PG 16
apt install -y postgresql-15-plpgsql-wrap   # PG 15
apt install -y postgresql-14-plpgsql-wrap   # PG 14

Create Extension:

CREATE EXTENSION plpgsql_wrap CASCADE;  -- requires: plpgsql

Usage

Sources: README, v1.0 release, control file

plpgsql_wrap provides an Oracle WRAP-style procedural language for PostgreSQL. Functions written with LANGUAGE plpgsql_wrap are validated as PL/pgSQL and then stored encrypted in pg_proc.prosrc as PLPGSQLWRAP:1:<hex>.

Install With A Key

Build the extension with a 32-byte AES-256-GCM key:

export WRAP_KEY_HEX=$(openssl rand -hex 32)
make WRAP_KEY_HEX=$WRAP_KEY_HEX
sudo make install

Back up the key. Wrapped functions can only be unwrapped or restored safely when the correct compiled key is available.

Install the extension in each database that needs the language:

CREATE EXTENSION plpgsql_wrap; -- requires plpgsql

Create Wrapped Functions

Use normal PL/pgSQL syntax with a different language name:

CREATE OR REPLACE FUNCTION public.calculate_bonus(emp_id int, yr int)
RETURNS numeric
LANGUAGE plpgsql_wrap
AS $$
DECLARE
  v_salary numeric;
BEGIN
  SELECT salary INTO v_salary FROM employees WHERE id = emp_id;
  RETURN v_salary * 0.15;
END;
$$;

The stored body is opaque:

SELECT substring(prosrc, 1, 32) AS wrapped_code
FROM pg_proc
WHERE proname = 'calculate_bonus';

Dump, Restore, And Unwrap

pg_dump emits the encrypted PLPGSQLWRAP:1: blob. A restore on a server with the same compiled key works normally. A different key leaves the blob stored, but calls fail when the validator/authentication path cannot authenticate it.

Superusers can permanently unwrap a function when they know the key:

SELECT plpgsql_wrap.unwrap_procedure(
  'myhexkey',
  'public',
  'calculate_bonus',
  'emp_id int, yr int'
);

Caveats

• Version 1.0 supports PostgreSQL 14-18.
• The control file requires plpgsql and superuser installation.
• This protects casual source inspection and dumps, but the compiled key is a critical secret. Treat package artifacts and build logs accordingly.
• Syntax is validated before encryption, so ordinary PL/pgSQL syntax errors abort CREATE FUNCTION before encrypted storage is written.