Using

After you configure all of the Hashicorp Vault certificates, you can use them with your EDB Postgres distribution.

Note

This content is intended for versions 15.2 and later of EDB Postgres Advanced Server and versions 15.2 and later of EDB Postgres Extended Server. Only these versions support transparent data encryption (TDE).

To implement Hashicorp Vault secrets engine with your EDB Postgres distribution, make sure that you have the following downloaded to your system:

Python
PyKMIP
edb-tde-kmip-client downloaded from your EDB Repos access

You need to copy all of the .pem files that you created in Configuring Hashicorp Vault—key.pem, cert.pem, and ca.pem—to the system where your EDB Postgres distribution is installed. For this example, all of the .pem files and the edb_tde_kmip_client.py program are in the /tmp/ directory.

Check prerequisites and download edb-tde-kmip-client

Ensure that you have the prerequisite software (Python and Pykmip) installed on your system.

To install the edb-tde-kmip-client on your system, assume root user and issue the install command for edb-tde-kmip-client. This example installs it on a RHEL8 rerver: dnf install edb-tde-kmip-client.

Some output is returned that looks like the following:

[root@ip-172-31-7-145 ec2-user]# dnf install edb-tde-kmip-client
Updating Subscription Management repositories.
Last metadata expiration check: 0:00:59 ago on Thu 06 Jul 2023 01:30:54 PM UTC.
Dependencies resolved.
================================================================================
 Package              Arch    Version     Repository                       Size
================================================================================
Installing:
 edb-tde-kmip-client  noarch  1.0-1.el8   enterprisedb-enterprise-noarch   14 k

Transaction Summary
================================================================================
Install  1 Package

Total download size: 14 k
Installed size: 20 k
Is this ok [y/N]: y
Downloading Packages:
edb-tde-kmip-client-1.0-1.el8.noarch.rpm         23 kB/s |  14 kB     00:00    
--------------------------------------------------------------------------------
Total                                            23 kB/s |  14 kB     00:00     
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                        1/1 
  Installing       : edb-tde-kmip-client-1.0-1.el8.noarch                   1/1 
  Verifying        : edb-tde-kmip-client-1.0-1.el8.noarch                   1/1 
Installed products updated.

Installed:
  edb-tde-kmip-client-1.0-1.el8.noarch                                          

Complete!

Create the pykmip.conf file

On your system where you have your EDB Postgres distribution, navigate to the directory where you saved your .pem files and the edb_tde_kmip_client.py client.
In that directory, create a file called pykmip.conf and input the following:

Host
Port
Keyfile
Certfile
Ca_certs

For example:

ubuntu@ip-172-31-46-134:/tmp$ cat pykmip.conf 
[client]
host=127.0.0.1
port=5696
keyfile=/tmp/key.pem
certfile=/tmp/cert.pem
ca_certs=/tmp/ca.pem

Note

For more information on the pykmip.conf file and its contents, see the PyKMIP documentation.

Create a key on the Hashicorp Vault secrets engine

On your system where you have your EDB Postgres distribution, assume root user to create the key on the Hashicorp Vault secrets engine.
Enter python3, and then input the following, making adjustments per your system setup and directory paths:

>>> from kmip.pie import client
>>> from kmip import enums
>>> c = client.ProxyKmipClient(config_file='/tmp/pykmip.conf')
>>> c.open()
>>> key_id = c.create(enums.CryptographicAlgorithm.AES, 128, name='edbtestkey')
    (`edbtestkey` is the name that we chose for our TDE master key. Alter this per your naming requirements.)
>>> c.activate(key_id)
>>> key_id
>>> 'key_output_shows_here'
>>> c.close()

If this runs without error, then your key was successfully created. (You can't view keys that you create in Hashicorp Vault.)

Verify encryption and decryption

To ensure that the key you created can encrypt and decrypt data, run the following commands as the root user on the system with your EDB Postgres distribution:

printf secret | python3 /tmp/edb_tde_kmip_client.py encrypt --out-file=test.bin --pykmip-config-file=/tmp/pykmip.conf --key-uid='key_output_here’ --variant=pykmip

Location of the KMIP Client: /tmp/edb_tde_kmip_client.py
Output file: test.bin
Location of pykmip configuration file: /tmp/pykmip.conf
Encrypted key output: TDE key output
Variant: Allows for KMIP compatibility with HashiCorp Vault

python3 /tmp/edb_tde_kmip_client.py decrypt --in-file=test.bin --pykmip-config-file=/tmp/pykmip.conf --key-uid='key_output_here' --variant=pykmip
If this command is successful, it produces the output of secret:

root@ip-172-31-46-134:/etc/vault.d# printf secret | python3 /tmp/edb_tde_kmip_client.py encrypt --out-file=test.bin --pykmip-config-file=/tmp/pykmip.conf --key-uid='nfTCV2Cp5sffhQuRrOVfgCUyu8qh9kwd' --variant=pykmip
root@ip-172-31-46-134:/etc/vault.d# python3 /tmp/edb_tde_kmip_client.py decrypt --in-file=test.bin --pykmip-config-file=/tmp/pykmip.conf --key-uid='nfTCV2Cp5sffhQuRrOVfgCUyu8qh9kwd' --variant=pykmip
Secret
root@ip-172-31-46-134:/etc/vault.d#

Perform initdb for the database

After you complete the previous steps, you can export the PGDATAKEYWRAPCMD and PGDATAKEYUNWRAPCMD to wrap and unwrap your encryption key and initialize your database.

Log in to your EDB Postgres distribution as the superuser. For our example, use the enterprisedb user: sudo su - enterprisedb.
Navigate to the /bin directory where your executables are. In this example, it's /usr/lib/edb-as/15/bin.
Enter export PGDATAKEYWRAPCMD='python3 /tmp/edb_tde_kmip_client.py encrypt --pykmip-config-file=/tmp/pykmip.conf --key-uid=key_ouput_here --out-file=%p --variant=pykmip’
Enter export PGDATAKEYUNWRAPCMD='python3 /tmp/edb_tde_kmip_client.py decrypt --pykmip-config-file=/tmp/pykmip.conf --key-uid=key_output_here --in-file=%p --variant=pykmip’

enterprisedb@ip-172-31-46-134:/usr/lib/edb-as/15/bin$ export PGDATAKEYWRAPCMD='python3 /tmp/edb_tde_kmip_client.py encrypt --pykmip-config-file=/tmp/pykmip.conf --key-uid=nfTCV2Cp5sffhQuRrOVfgCUyu8qh9kwd --out-file=%p --variant=pykmip'

enterprisedb@ip-172-31-46-134:/usr/lib/edb-as/15/bin$ export PGDATAKEYUNWRAPCMD='python3 /tmp/edb_tde_kmip_client.py decrypt --pykmip-config-file=/tmp/pykmip.conf --key-uid=nfTCV2Cp5sffhQuRrOVfgCUyu8qh9kwd --in-file=%p --variant=pykmip'

Perform your initdb per your database requirements, for example: ./initdb -D dd12 -y.
If all is successful, your output looks like this:

    enterprisedb@ip-172-31-46-134:/usr/lib/edb-as/15/bin$ ./initdb -D /var/lib/edb-as/15/dd12 -y
The files belonging to this database system will be owned by user "enterprisedb".
This user must also own the server process.
The database cluster will be initialized with locale "C.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
Transparent data encryption is enabled.
creating directory /var/lib/edb-as/15/dd12 ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... America/New_York
creating configuration files ... ok
setting up data encryption ... ok
running bootstrap script ... usage: edb_tde_kmip_client.py [-h] [--pykmip-config-file FILENAME]
                              [--pykmip-config-block NAME]
                              [--in-file FILENAME] [--out-file FILENAME]
                              --key-uid KEY_UID --variant {pykmip,thales}
                              {decrypt,encrypt}
edb_tde_kmip_client.py: error: argument --variant: invalid choice: 'pymip' (choose from 'pykmip', 'thales')
2023-04-12 09:35:27 EDT FATAL:  unwrapped key is too small
child process exited with exit code 1
initdb: removing data directory "/var/lib/edb-as/15/dd12"
enterprisedb@ip-172-31-46-134:/usr/lib/edb-as/15/bin$ export PGDATAKEYWRAPCMD='python3 /tmp/edb_tde_kmip_client.py encrypt --pykmip-config-file=/tmp/pykmip.conf --key-uid=nfTCV2Cp5sffhQuRrOVfgCUyu8qh9kwd --out-file=%p --variant=pykmip'
enterprisedb@ip-172-31-46-134:/usr/lib/edb-as/15/bin$ export PGDATAKEYUNWRAPCMD='python3 /tmp/edb_tde_kmip_client.py decrypt --pykmip-config-file=/tmp/pykmip.conf --key-uid=nfTCV2Cp5sffhQuRrOVfgCUyu8qh9kwd --in-file=%p --variant=pykmip'
enterprisedb@ip-172-31-46-134:/usr/lib/edb-as/15/bin$ 
enterprisedb@ip-172-31-46-134:/usr/lib/edb-as/15/bin$ 
enterprisedb@ip-172-31-46-134:/usr/lib/edb-as/15/bin$ 
enterprisedb@ip-172-31-46-134:/usr/lib/edb-as/15/bin$ 
enterprisedb@ip-172-31-46-134:/usr/lib/edb-as/15/bin$ ./initdb -D /var/lib/edb-as/15/dd12 -y
The files belonging to this database system will be owned by user "enterprisedb".
This user must also own the server process.
The database cluster will be initialized with locale "C.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
Transparent data encryption is enabled.
creating directory /var/lib/edb-as/15/dd12 ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... America/New_York
creating configuration files ... ok
setting up data encryption ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
creating edb sys ... ok
loading edb contrib modules ...
edb_redwood_bytea.sql 
edb_redwood_date.sql 
dbms_alert_public.sql 
dbms_alert.plb 
dbms_job_public.sql 
dbms_job.plb 
dbms_lob_public.sql 
dbms_lob.plb 
dbms_output_public.sql 
dbms_output.plb 
dbms_pipe_public.sql 
dbms_pipe.plb 
dbms_rls_public.sql 
dbms_rls.plb 
dbms_sql_public.sql 
dbms_sql.plb 
dbms_utility_public.sql 
dbms_utility.plb 
dbms_aqadm_public.sql 
dbms_aqadm.plb 
dbms_aq_public.sql 
dbms_aq.plb 
dbms_profiler_public.sql 
dbms_profiler.plb 
dbms_random_public.sql 
dbms_random.plb 
dbms_redact_public.sql 
dbms_redact.plb 
dbms_lock_public.sql 
dbms_lock.plb 
dbms_scheduler_public.sql 
dbms_scheduler.plb 
dbms_crypto_public.sql 
dbms_crypto.plb 
dbms_mview_public.sql 
dbms_mview.plb 
dbms_session_public.sql 
dbms_session.plb 
edb_bulkload.sql 
edb_gen.sql 
edb_objects.sql 
edb_redwood_casts.sql 
edb_redwood_strings.sql 
edb_redwood_views.sql 
utl_encode_public.sql 
utl_encode.plb 
utl_http_public.sql 
utl_http.plb 
utl_file.plb 
edb_ht_public.sql 
edb_ht.plb 
utl_tcp_public.sql 
utl_tcp.plb 
utl_smtp_public.sql 
utl_smtp.plb 
utl_mail_public.sql 
utl_mail.plb 
utl_url_public.sql 
utl_url.plb 
utl_raw_public.sql 
utl_raw.plb 
commoncriteria.sql 
edb_gen_redwood.sql 
waitstates.sql 
installing extension edb_dblink_libpq ... ok
installing extension edb_dblink_oci ... ok
snap_tables.sql 
snap_functions.sql 
dblink_ora.sql 
sys_stats.sql 
ok
finalizing initial databases ... ok
syncing data to disk ... ok
initdb: warning: enabling "trust" authentication for local connections
initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
    pg_ctl -D /var/lib/edb-as/15/dd12 -l logfile start

Start your database and navigate to your /data directory to view the postgresql.conf file. Make sure that your data_encryption_key_unwrap_command, which you set with your export PGDATAUNWRAPCMD, is present under the Authentication section.

# - Authentication -

#authentication_timeout = 1min          # 1s-600s
#password_encryption = scram-sha-256    # scram-sha-256 or md5
#db_user_namespace = off

# GSSAPI using Kerberos
#krb_server_keyfile = 'FILE:${sysconfdir}/krb5.keytab'
#krb_caseins_users = off

# - SSL -

#ssl = off
#ssl_ca_file = ''
#ssl_cert_file = 'server.crt'
#ssl_crl_file = ''
#ssl_crl_dir = ''
#ssl_key_file = 'server.key'
#ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers
#ssl_prefer_server_ciphers = on
#ssl_ecdh_curve = 'prime256v1'
#ssl_min_protocol_version = 'TLSv1.2'
#ssl_max_protocol_version = ''
#ssl_dh_params_file = ''
#ssl_passphrase_command = ''
#ssl_passphrase_command_supports_reload = off

# - Data Encryption -

data_encryption_key_unwrap_command = 'python3 /tmp/edb_tde_kmip_client.py decrypt --pykmip-config-file=/tmp/pykmip.conf --key-uid=nfTCV2Cp5sffhQuRrOVfgCUyu8qh9kwd --in-file=%p --variant=pykmip'

For more information on how TDE is incorporated with EDB Postgres Advanced Server and EDB Postgres Extended Server, see the EDB Transparent Data Encryption documentation.

Could this page be better? Report a problem or suggest an addition!