如何使用Hibernate @ColumnTransformer在Postgres中加密列 [英] How to encrypt a column in Postgres using Hibernate @ColumnTransformer

问题描述

我正在尝试对prostrgres DB中的列进行加密.列名是类型为"bytea"的"test".

I am trying to encrypt a column in my prostrgres DB. The column name is "test" of type "bytea".

我的实体代码在下面，

@ColumnTransformer(
          forColumn="test", 
          read="pgp_sym_encrypt(test::bytea, 'mySecretKey')", 
          write="pgp_sym_decrypt(?, 'mySecretKey')")
private String test;

当我尝试检索实体时，我正在获取如下所示的加密数据.如何以编程方式获取解密后的值?但是我得到了实际值，如果我执行一个postgres select查询.

When I tried to retrieve the entity, I am getting the encrypted data like below. How do I get the decrypted value programmatically? But I get the actual value If i execute a postgres select query.

  "test": "\\xc30d04070302474627ea0994ea657bd24401aaa5543862d57524a407e5dbe2ee0f6f0f33ea4f4474f5bc801dca5d32956d41a975505b12ac000f124177bdc2f4507cbfd724d716aaa513ba46f004dfefd3b2b32eb6"

1. 当我尝试保留实体时，出现以下错误.

错误:列"test"的类型为bytea，但表达式的类型为字符变化

ERROR: column "test" is of type bytea but expression is of type character varying

推荐答案

您需要使用 pgp_sym_encrypt 进行写入，并使用 pgp_sym_decrypt 进行读取.你做了相反的事情.

You need to use pgp_sym_encrypt for write and pgp_sym_decrypt for read. You did the opposite.

@ColumnTransformer(
    read =  "pgp_sym_decrypt(" +
            "    test, " +
            "    current_setting('encrypt.key')" +
            ")",
    write = "pgp_sym_encrypt( " +
            "    ?, " +
            "    current_setting('encrypt.key')" +
            ") "
)
@Column(columnDefinition = "bytea")
private String test;

因为在映射中对加密密钥进行硬编码听起来不是一个好主意，所以我们将使用PostgreSQL支持用户定义的设置.

Because hard-coding the encryption key in the mapping does not sound like a very good idea, we will use the PostgreSQL support for user-defined settings instead.

因此， encrypt.key 存储在 postgresql.conf 配置文件中:

encrypt.key = 'Wow! So much security.'

The example is on GitHub and works like a charm.

这篇关于如何使用Hibernate @ColumnTransformer在Postgres中加密列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！