Where exactly do we place this postgresql.conf configuration file in spring boot application?

user_14047348 0
Viewed 937

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?

spring-boot encryption postgresql orm hibernate
2 Answers

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.

user_3303150

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)
user_30081390
Related