Error with pgp_sym_encrypt_bytea - function pgp_sym_encrypt_bytea(bytea, integer) does not exist

59 views Asked by At

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)
 )
0

There are 0 answers