I am trying to encrypt a column in my prostrgres DB. The column name is "test" of type "bytea".
My enity code is below,
@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.conf configuration file: encrypt.key = 'Wow! So much security.
Placed the postgresql.conf configuration file in src/main/resources of spring boot appln. But the encryption.key value is not being picked up. And is there a way to pass the key using application.properties?
postgresql.conf is the configuration file for the Postgres server. It's stored inside the data directory (aka "cluster") on the server.
You can't put it on the client side (where your application runs). It has no meaning there.
To change values in there, you need to edit the file (on the server) or use ALTER SYSTEM.
If you want to change a configuration setting for the current session, use SET or set_config()
The latter two are probably the ones you are looking for to initialize the custom property for your encryption/decryption functions.
The way to use encrypt.key, not only for current session, it's store it in postgresql.conf.
The correct place is at the end of this file, in the "Customized Options" section:
#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------
# Add settings for extensions here
encrypt.key=123456
Reload the configuration of the database server:
systemctl reload postgresql.service
To testing if it's working correctly. Open a pgsql session and type:
mydb=# show encrypt.key;
encrypt.key
-------------
123456
(1 row)
Example of encrypt:
mydb=# select pgp_sym_encrypt('Hola mundo',current_setting('encrypt.key'));
pgp_sym_encrypt
------------------------------------------------------------------------------------------------------------------------------------------------------------
\xc30d04070302255230e388dfe25e7dd23b01c5b8e62d148088a3417d3c27ed2cc11655d863b271672b9f076fffb82f1a7f074f2ecbe973df04642cd7a4f76ca5cff4a13b9a71e7cc6e693827
(1 row)
Example of decrypt:
mydb=# select pgp_sym_decrypt('\xc30d04070302255230e388dfe25e7dd23b01c5b8e62d148088a3417d3c27ed2cc11655d863b271672b9f076fffb82f1a7f074f2ecbe973df04642cd7a4f76ca5cff4a13b9a71e7cc6e693827',current_setting('encrypt.key'));
pgp_sym_decrypt
-----------------
Hola mundo
(1 row)