Encrypted Functions and Stored Procedures

Updated on 2024-08-20 GMT+08:00

View PDF

In the current version, only encrypted functions and stored procedures in SQL or PL/pgSQL are supported. Because users are unaware of the creation and execution of functions or stored procedures in an encrypted stored procedure, the syntax has no difference from that of non-encrypted functions and stored procedures.

For details about the syntax of functions and stored procedures, see "User-defined Functions" and "Stored Procedures" in Developer Guide.

The gs_encrypted_proc system catalog is added to the function or stored procedure for encrypted equality query to store the returned original data type.

For details about the fields in the system catalog, see "System Catalogs and System Views > System Catalogs > GS_ENCRYPTED_PROC" in Developer Guide.

Creating and Executing a Function or Stored Procedure that Involves Encrypted Columns

Create a key. For details, see Using gsql to Operate an Encrypted Database.

Create an encrypted table.

      
         gaussdb=# CREATE TABLE creditcard_info (
   id_number int,
   name  text,
   credit_card varchar(19) encrypted with (column_encryption_key = cek1, encryption_type = DETERMINISTIC)
 ) with (orientation=row);
CREATE TABLE

Insert data.

      
         gaussdb=# insert into creditcard_info values(1, 'Avi', '1234567890123456');
INSERT 0 1
gaussdb=# insert into creditcard_info values(2, 'Eli', '2345678901234567');
INSERT 0 1

Create a function supporting encrypted equality query.

      
         gaussdb=# CREATE FUNCTION f_encrypt_in_sql(val1 text, val2 varchar(19)) RETURNS text AS 'SELECT name from creditcard_info where name=$1 or credit_card=$2 LIMIT 1' LANGUAGE SQL;
CREATE FUNCTION
gaussdb=# CREATE FUNCTION f_encrypt_in_plpgsql (val1 text, val2 varchar(19), OUT c text) AS $$
 BEGIN
 SELECT into c name from creditcard_info where name=$1 or credit_card =$2 LIMIT 1;
 END; $$
 LANGUAGE plpgsql;
CREATE FUNCTION

Execute the function.

      
         gaussdb=# SELECT f_encrypt_in_sql('Avi','1234567890123456');
 f_encrypt_in_sql
------------------
 Avi
(1 row)

gaussdb=# SELECT f_encrypt_in_plpgsql('Avi', val2=>'1234567890123456');
 f_encrypt_in_plpgsql
----------------------
 Avi
(1 row)

NOTE:

Because the query, that is, the dynamic query statement executed in a function or stored procedure, is compiled during execution, the table name and column name in the function or stored procedure must be known in the creation phase. The input parameter cannot be used as a table name or column name, or any connection mode.
In a function or stored procedure that executes dynamic clauses, data values to be encrypted cannot be contained in the clauses.
Among the RETURNS, IN, and OUT parameters, encrypted and non-encrypted parameters cannot be used together. Although the parameter types are all original, the actual types are different.
In advanced package APIs, for example, dbe_output.print_line(), decryption is not performed on the APIs whose output is printed on the server. This is because when the encrypted data type is forcibly converted into the plaintext original data type, the default value of the data type is printed.
In the current version, LANGUAGE of functions and stored procedures can only be SQL or PL/pgSQL, and does not support other procedural languages such as C and Java.
Other functions or stored procedures for querying encrypted columns cannot be executed in a function or stored procedure.
In the current version, default values cannot be assigned to variables in DEFAULT or DECLARE statements, and return values in DECLARE statements cannot be decrypted. You can use input parameters and output parameters instead when executing functions.
gs_dump cannot be used to back up functions involving encrypted columns.
Keys cannot be created in functions or stored procedures.
In this version, encrypted functions and stored procedures do not support triggers.
Encrypted equality query functions and stored procedures do not support the escape of the PL/pgSQL syntax. The CREATE FUNCTION AS 'Syntax body' syntax whose syntax body is marked with single quotation marks ('') can be replaced with the CREATE FUNCTION AS $$Syntax body$$ syntax.
The definitions of encrypted columns cannot be modified in an encrypted equality query function or stored procedure, including creating an encrypted table and adding an encrypted column. Because the function is executed on the server, the client cannot determine whether to refresh the cache. The columns can be encrypted only after the client is disconnected or the cache of the encrypted columns on the client is refreshed.
Encrypted functions and stored procedures do not support compilation check. When creating an encrypted function, do not set behavior_compat_options to 'allow_procedure_compile_check'.
Functions and stored procedures cannot be created using encrypted data types (byteawithoutorderwithequalcol, byteawithoutordercol, _byteawithoutorderwithequalcol or _byteawithoutordercol).
If an encrypted function returns a value of an encrypted type, the result cannot be an uncertain row type, for example, RETURN [SETOF] RECORD. You can replace it with a definite row type, for example, RETURN TABLE(columnname typename[, ...]).
When an encrypted function is created, the OID of the encrypted column corresponding to a parameter is added to the gs_encrypted_proc system catalog. Therefore, if a table with the same name is deleted and created again, the encrypted function may become invalid and you need to create the encrypted function again.