PostgreSQL pgcrypto无法加密数据问题求助

Hey there! Let's dig into why your data isn't getting encrypted even though you've set up pgcrypto and added @ColumnTransformer to your entity. I've worked through similar issues before, so let's break this down step by step:

1. Verify Your Database Column Type #

First off, pgp_sym_encrypt() returns a bytea type (binary data), not plain text. If your first_name column is set to varchar or another text type, Hibernate might skip the encryption logic entirely to avoid type mismatches.

Check your table structure and update it if needed:

ALTER TABLE your_table_name ALTER COLUMN first_name TYPE bytea;

(Replace your_table_name with your actual table name)

2. Ensure Hibernate Recognizes the PostgreSQL Dialect #

@ColumnTransformer relies on Hibernate generating the correct SQL for your database. Make sure you've set the proper PostgreSQL dialect in your application.yml:

spring:
  jpa:
    properties:
      hibernate:
        dialect: org.hibernate.dialect.PostgreSQL96Dialect

Since you're using PostgreSQL 9.6, this dialect will ensure Hibernate handles PostgreSQL-specific functions like pgp_sym_encrypt correctly.

3. Check if @ColumnTransformer is Applied Correctly #

Double-check your entity field annotations. The @ColumnTransformer should be paired with a @Column annotation, and explicitly defining the column type can help avoid mismatches:

@Column(name = "first_name", columnDefinition = "bytea")
@ColumnTransformer(
    read = "pgp_sym_decrypt(first_name, 'mySecretKey')",
    write = "pgp_sym_encrypt(?, 'mySecretKey')"
)
private String firstName;

4. Inspect the Generated SQL #

Enable Hibernate's SQL logging to see if the encryption function is actually being included in the INSERT statement. Add these lines to your application.yml:

spring:
  jpa:
    show-sql: true
    properties:
      hibernate:
        format_sql: true

Run your app again and look at the logs. If you don't see pgp_sym_encrypt in the INSERT clause, that means Hibernate isn't picking up your @ColumnTransformer—this could be due to outdated Hibernate versions (try upgrading if you're on an older Spring Boot release) or incorrect annotation placement.

5. Rule Out Native SQL Usage #

If you're using JdbcTemplate or EntityManager.createNativeQuery() to insert data, @ColumnTransformer won't work. These methods bypass Hibernate's entity mapping logic, so you'll need to manually include the pgp_sym_encrypt function in your native SQL:

INSERT INTO your_table_name (first_name) VALUES (pgp_sym_encrypt(?, 'mySecretKey'));

6. Test Encryption Directly in PostgreSQL #

To confirm pgcrypto is working correctly, run these commands directly in PgAdmin4:

-- Insert encrypted data
INSERT INTO your_table_name (first_name) VALUES (pgp_sym_encrypt('Test Name', 'mySecretKey'));

-- Decrypt and verify
SELECT pgp_sym_decrypt(first_name, 'mySecretKey') FROM your_table_name;

If this works, the issue is definitely in your Spring Boot configuration, not the database setup.

Bonus: Move Your Key to Configuration (Best Practice) #

Once you get encryption working, don't hardcode your key in the annotation! Use Spring's property injection to pull it from application.yml:

1. Add the key to application.yml:

   encryption:
     key: mySecretKey
   

2. Update your entity using SpEL:

   @ColumnTransformer(
       read = "pgp_sym_decrypt(first_name, '${encryption.key}')",
       write = "pgp_sym_encrypt(?, '${encryption.key}')"
   )
   

内容的提问来源于stack exchange，提问作者user143019