Using

Suggest edits

After you have configured all of the Thales Certificates, as stated in the Configuring section, you will be able to use them in conjunction with your EDB Postgres distribution.

Note

It is important to note that this doc is intended for versions 15.2 and above of EDB Postgres Advanced Server and versions 15.2 and above of EDB Postgres Extended Server as these versions support Transparent Data Encryption (TDE).

To implement Thales CipherTrust Manger with your EDB Postgres distribution you must ensure that you have the following downloaded to your system:

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

All of the .pem files that you created in the Configuring section, key.pem, cert.pem and ca.pem, need to be copied to the system where your EDB Postgres distribution is installed. For our 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 as stated in the Configuring section.

To install the edb-tde-kmip-client on your system assume root user and issue the install command for edb-tde-kmip-client. For our example we installed it on a RHEL8 Server so it would be dnf install edb-tde-kmip-client.

You should receive some output 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 pykmip.conf File

On your system where you have your EDB Postgres distribution, navigate to the directory where you have 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
Username
Password
Keyfile
Certfile
Ca_certs

For example:

edb@debian:~$ cat /tmp/pykmip.conf  
[client]
host=172.22.20.194
port=5696
username=testuser123     
password=Adminedb@123
keyfile=/tmp/key.pem
certfile=/tmp/cert.pem
ca_certs=/tmp/ca.pem

Note

For more information on the pykmip.conf file and the contents of it you can visit the pykmip documentation.

Create a Key on Thales CipherTrust Manager

There are two ways you can create a key with Thales CipherTrust Manager. You can create one locally with python3 or you an use the Thales CipherTrust Manager UI.

Use one of the two methods listed below to proceed.

Create a Key Locally with python3 on Thales CipherTrust Manager

On your system with your EDB Postgres distribution, login as the superuser of the database to create the key on Thales CipherTrust Manager.
Type 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()

Navigate back to Thales CipherTrust Manager, and select Keys on the navigation bar.
Check that your key, in this case edbtestkey, has been created.

Create a Key on Thales CipherTrust Manager UI

You may also create Keys on the Thales CipherTrust Manager UI to use in your database WRAP and UNWRAP commands for encryption.

Login to Thales CipherTrust Manager.
On the main page, select Keys from the left bar.
Select Add Key to create a new key.
Give the key an identifiable name, and select the Key Properties and Key Usage boxes per your requirements.
Select Add Key.
After you select Add Key, you will be taken to that key's page with specific information.
The specific key ID that is needed for your PGDATAKEYWRAPCMD and PGDATAKEYUNWRAPCMD commands is the ID which is shown at the top of your key information page.

Verify Encryption and Decryption

To ensure that your key that you created will be able to encrypt and decrypt data, run the following two commands as the superuser on your system where you have 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=thales

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 Thales

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

printf secret | python3 /tmp/edb_tde_kmip_client.py encrypt --out-file=test.bin --pykmip-config-file=/tmp/pykmip.conf  --key-uid='72cb431904fe4dd0a77207b03bff7755be6265cd7f6f463bb0e59023ec5456f4' --variant=thales
 
python3 /tmp/edb_tde_kmip_client.py decrypt --in-file=test.bin --pykmip-config-file=/tmp/pykmip.conf --key-uid='72cb431904fe4dd0a77207b03bff7755be6265cd7f6f463bb0e59023ec5456f4' --variant=thales 
secret

Perform initdb for the Database

After you have completed the above steps you will be able to export the PGDATAKEYWRAPCMD and PGDATAKEYUNWRAPCMD to wrap and unwrap your encryption key and initialize your database.

Login to your EDB Postgres distribution system as the database superuser. For our example: enterprisedb user, sudo su - enterprisedb.
Navigate to the /bin directory where your executables live. In our example it is /usr/lib/edb-as/15/bin.
Type: 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=thales’
Type: 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=thales’

For our example:

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=72cb431904fe4dd0a77207b03bff7755be6265cd7f6f463bb0e59023ec5456f4 --out-file=%p --variant=thales'

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=72cb431904fe4dd0a77207b03bff7755be6265cd7f6f463bb0e59023ec5456f4 --in-file=%p --variant=thales'

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

    edb@debian:~$ /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 "edb".
    This user must also own the server process.

    The database cluster will be initialized with locale "en_US.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 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 ... Asia/Kolkata
    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 
    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 dd12 -l logfile start

Start your database and navigate to your /data directory to view the postgresql.conf file to ensure 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=72cb431904fe4dd0a77207b03bff7755be6265cd7f6f463bb0e59023ec5456f4 --in-file=%p --variant=thales'

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

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