I'm trying to implement TDE encryption in a postgre database. The encryption key was created in the encryption_keys table which I put the structure below. I need to find a way to iterate over all tables/columns of a specific schema and encrypt them. I have already run the extension > CREATE EXTENSION IF NOT EXISTS pgcrypto; to use the functions.
I'm using the pgp_sym_encrypt_bytea function, which according to the documentation, takes two arguments: bytea and text. The error is ERROR: function pgp_sym_encrypt_bytea(bytea, integer) does not exist
It seems like It's receiving an integer at the second argument, but in the script I am passing the colm_name variable, a text.
insurance$=# DO$$
insurance$# DECLARE
insurance$# tbl_name text;
insurance$# colm_name text;
insurance$# BEGIN
insurance$# FOR tbl_name IN (SELECT table_name FROM information_schema.tables WHERE table_schema = current_schema()) LOOP
insurance$# FOR colm_name IN (SELECT column_name FROM information_schema.columns WHERE table_name = tbl_name) LOOP
insurance$# EXECUTE format('UPDATE %I SET %I = pgp_sym_encrypt_bytea((SELECT encryption_key FROM encryption_keys), %I)', tbl_name, colm_name, colm_name);
insurance$# END LOOP;
insurance$# END LOOP;
insurance$# END $$;
ERROR: function pgp_sym_encrypt_bytea(bytea, integer) does not exist
LINE 1: UPDATE encryption_keys SET key_id = pgp_sym_encrypt_bytea((S...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
QUERY: UPDATE encryption_keys SET key_id = pgp_sym_encrypt_bytea((SELECT encryption_key FROM encryption_keys), key_id)
CONTEXT: PL/pgSQL function inline_code_block line 8 at EXECUTE
This is the script:
DO$$
DECLARE
tbl_name text;
colm_name text;
BEGIN
FOR tbl_name IN (SELECT table_name FROM information_schema.tables WHERE table_schema = current_schema()) LOOP
FOR colm_name IN (SELECT column_name FROM information_schema.columns WHERE table_name = tbl_name) LOOP
EXECUTE format('UPDATE %I SET %I = pgp_sym_encrypt_bytea((SELECT encryption_key FROM encryption_keys), %I)', tbl_name, colm_name, colm_name);
END LOOP;
END LOOP;
END$$;
This is the table with the encryption key:
CREATE TABLE IF NOT EXISTS public.encryption_keys
(
key_id integer NOT NULL DEFAULT nextval('encryption_keys_key_id_seq'::regclass),
encryption_key bytea NOT NULL,
CONSTRAINT encryption_keys_pkey PRIMARY KEY (key_id)
)