digoal
2022-12-15
PostgreSQL , PolarDB , extension , pgcrypto , 加解密 , bytea , 文件 , 大对象
1、如何将文件导入为bytea
三种方法, 根据版本选择, 新版本直接使用read函数
-- 1
create or replace function bytea_import(p_path text, p_result out bytea)
language plpgsql as $$
declare
l_oid oid;
begin
select lo_import(p_path) into l_oid;
select lo_get(l_oid) INTO p_result;
perform lo_unlink(l_oid);
end;
$$;
-- 2
pg_read_binary_file('/path/to/file')
-- 3
pg_read_file('location_of file')::bytea
2、如何加密bytea内容
pgp_sym_encrypt_bytea(data bytea, psw text [, options text ]) returns bytea
postgres=# show bytea_output ;
bytea_output
--------------
hex
(1 row)
3、如何解密bytea内容
pgp_sym_decrypt_bytea(msg bytea, psw text [, options text ]) returns bytea
4、如何将bytea导出为文件
借助大对象接口
DO language plpgsql $$
DECLARE
l_lob_id OID;
r record;
BEGIN
for r in
select data, filename from bytea_table
LOOP
l_lob_id:=lo_from_bytea(0,r.data);
PERFORM lo_export(l_lob_id, '/home/...'||r.filename);
PERFORM lo_unlink(l_lob_id);
END LOOP;
END;
$$;
1、写一个测试文件
echo "PolarDB X and PostgreSQL 数据库其乐无穷" > ~/1.txt
2、创建加密插件
create extension pgcrypto;
3、创建测试表, 存储文件导入并加密后的bytea
create table test (id int, file_encrypt bytea);
insert into test values (1, pgp_sym_encrypt_bytea(pg_read_binary_file('/Users/digoal/1.txt'), 'z...j'));
postgres=# \x
Expanded display is on.
postgres=# select * from test;
-[ RECORD 1 ]+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
id | 1
file_encrypt | \xc30d0407030210f2661fffc6862b78d260019b8df8eb439cf28ea4325af03f0a9607e86e1db073eec17036f9a3aeb34f920c9199479c33cfda90f20717ed658d73455e4ac9d4001d918781ad8550c2126a55fe564b9248fd0be41b16f1a6006e2177b26ed5ac30ff360ac05614cb43b8ff
4、将bytea解密并导出到文件
DO language plpgsql $$
DECLARE
l_lob_id OID;
BEGIN
SELECT lo_from_bytea(0, pgp_sym_decrypt_bytea(file_encrypt, 'z...j')) into l_lob_id FROM test where id=1;
PERFORM lo_export(l_lob_id, '/Users/digoal/1.txt.from.pg');
PERFORM lo_unlink(l_lob_id);
END;
$$;
5、你也可以把加密后的bytea存储到文件中, 可以从这个文件恢复原始内容
copy (select file_encrypt from test) to '/Users/digoal/1.csv'; -- 加密后的bytea导出到csv文件
create table test_import (file_encrypt bytea); -- 导入加密后的bytea
copy test_import from '/Users/digoal/1.csv';
DO language plpgsql $$ -- 解密并导出为原始文件
DECLARE
l_lob_id OID;
BEGIN
SELECT lo_from_bytea(0, pgp_sym_decrypt_bytea(file_encrypt, 'z...j')) into l_lob_id FROM test_import;
PERFORM lo_export(l_lob_id, '/Users/digoal/1.txt.from.pg');
PERFORM lo_unlink(l_lob_id);
END;
$$;
6、比对文件内容和md5, 完全一致
IT-C02YW2EFLVDL:~ digoal$ cat 1.txt.from.pg
PolarDB X and PostgreSQL 数据库其乐无穷
IT-C02YW2EFLVDL:~ digoal$ cat 1.txt
PolarDB X and PostgreSQL 数据库其乐无穷
IT-C02YW2EFLVDL:~ digoal$ md5 1.txt
MD5 (1.txt) = cab5daf2095974f3aaf63c139e186848
IT-C02YW2EFLVDL:~ digoal$ md5 1.txt.from.pg
MD5 (1.txt.from.pg) = cab5daf2095974f3aaf63c139e186848
《大对象 - Use PostgreSQL server program import binary data into database bytea type》
https://www.postgresql.org/docs/current/lo-funcs.html
https://www.postgresql.org/docs/current/functions-admin.html
https://www.postgresql.org/docs/current/pgcrypto.html
https://dba.stackexchange.com/questions/1742/how-to-insert-file-data-into-a-postgresql-bytea-column
