Procedures

% sudo usermod -aG wheel enterprisedb

% python

Python 3.9.18 (main, Aug 23 2024, 00:00:00)
[GCC 11.4.1 20231218 (Red Hat 11.4.1-3)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> quit()

% sudo dnf install pip

Updating Subscription Management repositories.
Last metadata expiration check: 0:04:54 ago on Mon 09 Sep 2024 02:59:02 PM EDT.
Dependencies resolved.
=================================================================================================
 Package                 Architecture  Version         Repository                           Size
=================================================================================================
Installing:
 python3-pip             noarch        21.2.3-8.el9    rhel-9-for-x86_64-appstream-rpms     2.0 M

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

Total download size: 2.0 M
Installed size: 8.7 M
Is this ok [y/N]: y
Downloading Packages:
python3-pip-21.2.3-8.el9.noarch.rpm                                    1.8 MB/s | 2.0 MB     00:01
--------------------------------------------------------------------------------------------------
Total                                                                  1.8 MB/s | 2.0 MB     00:01
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                                                                                                                                                                         1/1
  Installing       : python3-pip-21.2.3-8.el9.noarch                                                                                                                                                                                         1/1
  Running scriptlet: python3-pip-21.2.3-8.el9.noarch                                                                                                                                                                                         1/1
  Verifying        : python3-pip-21.2.3-8.el9.noarch                                                                                                                                                                                         1/1
Installed products updated.

Installed:
  python3-pip-21.2.3-8.el9.noarch

Complete!

% /usr/bin/python3.9 -m pip install --upgrade pip

Defaulting to user installation because normal site-packages is not writeable
Requirement already satisfied: pip in /usr/lib/python3.9/site-packages (21.2.3)
Collecting pip
  Downloading pip-24.2-py3-none-any.whl (1.8 MB)
.
.
.
Successfully installed pip-24.2

% sudo pip3 install --upgrade setuptools

Requirement already satisfied: setuptools in /usr/lib/python3.9/site-packages (53.0.0)
Collecting setuptools
  Downloading setuptools-74.1.2-py3-none-any.whl (1.3 MB)
     |████████████████████████████████| 1.3 MB 710 kB/s
Installing collected packages: setuptools
Successfully installed setuptools-74.1.2

% sudo pip install tox

Defaulting to user installation because normal site-packages is not writeable
Collecting tox
  Downloading tox-4.18.1-py3-none-any.whl.metadata (5.0 kB)
Collecting cachetools>=5.5 (from tox)
  Downloading cachetools-5.5.0-py3-none-any.whl.metadata (5.3 kB)
.
.
.
Successfully installed cachetools-5.5.0 chardet-5.2.0 colorama-0.4.6 distlib-0.3.8 filelock-3.16.0 packaging-24.1 platformdirs-4.3.2 pluggy-1.5.0 pyproject-api-1.7.1 tomli-2.0.1 tox-4.18.1 virtualenv-20.26.4

% sudo dnf install -y git

% sudo pip install cryptography

Collecting cryptography
  Downloading cryptography-43.0.1-cp39-abi3-manylinux_2_28_x86_64.whl (4.0 MB)
.
.
.
Successfully installed cffi-1.17.1 cryptography-43.0.1 pycparser-2.22

% cd ~/pykmip
% ./bin/run_tests.sh

pep8: install_deps> python -I -m pip install -r /home/xxxxx/pykmip/requirements.txt -r /home/xxxxx/pykmip/test-requirements.txt
.
.
.
writing output... [100%] installation .. server
generating indices... genindex py-modindex done
writing additional pages... search done
dumping search index in English (code: en)... done
dumping object inventory... done
build succeeded.

The HTML pages are in ../.tox/docs/tmp/html.
  pep8: OK (37.05=setup[27.65]+cmd[9.41] seconds)
  py38: SKIP (0.24 seconds)
  py310: SKIP (0.01 seconds)
  bandit: OK (19.03=setup[13.74]+cmd[5.30] seconds)
  docs: OK (14.05=setup[11.38]+cmd[2.67] seconds)
  congratulations :) (70.52 seconds)

$ ssh enterprisedb@<postgres-server-ip-address>

% sudo mkdir -p /etc/pykmip/certs
% sudo cp client1.pem /etc/pykmip/certs/client_cert.pem
% sudo cp client1.key/etc/pykmip/certs/client_private_key.pem
% sudo cp cacert.pem /etc/pykmip/certs/server_ca_cert.pem
% sudo chmod a+r /etc/pykmip/certs/*

export PYTHONPATH=$HOME/pykmip

% source ~/.bash_profile

% echo $PYTHONPATH

/var/lib/edb/pykmip

% sudo cp ~/pykmip/examples/policy.json /etc/pykmip/policy.json

% sudo cp ~/pykmip/examples/pykmip.conf /etc/pykmip/pykmip.conf

[client]
host=<KEYCONTROL-VAULT-IP-ADDRESS>
port=5696
keyfile=/etc/pykmip/certs/client_private_key.pem
certfile=/etc/pykmip/certs/client_cert.pem
cert_reqs=CERT_REQUIRED
ssl_version=PROTOCOL_SSLv23
ca_certs=/etc/pykmip/certs/server_ca_cert.pem
do_handshake_on_connect=True
suppress_ragged_eofs=True
#username=example_username
#password=example_password

% ping <keycontrol-vault-ip-address>

PING <keycontrol-vault-ip-address> (192.168.1.235) 56(84) bytes of data.
64 bytes from <keycontrol-vault-ip-address> (192.168.1.235): icmp_seq=1 ttl=64 time=0.701 ms
64 bytes from <keycontrol-vault-ip-address> (192.168.1.235): icmp_seq=2 ttl=64 time=0.437 ms
64 bytes from <keycontrol-vault-ip-address> (192.168.1.235): icmp_seq=3 ttl=64 time=0.421 ms
64 bytes from <keycontrol-vault-ip-address> (192.168.1.235): icmp_seq=4 ttl=64 time=0.405 ms
^C
--- <keycontrol-vault-ip-address> ping statistics ---
4 packets transmitted, 4 received, 0% packet loss, time 3044ms

% echo | \
  openssl s_client -servername <keycontrol-ip-address>-connect <keycontrol-ip-address>:443 2>/dev/null | \
  openssl x509 -noout -issuer -subject -dates -ext subjectAltName -fingerprint

issuer=C = US, O = HyTrust Inc., CN = HyTrust KeyControl Certificate Authority
subject=C = US, O = HyTrust Inc., CN = kcv-10p3p1-node-1.interop.local
notBefore=Jun  1 00:00:00 2011 GMT
notAfter=Dec 31 23:59:59 2049 GMT
X509v3 Subject Alternative Name:
    IP Address:<keycontrol-vault-ip-address>, DNS:kcv-10p3p1-node-1, DNS:kcv-10p3p1-node-1.interop.local
SHA1 Fingerprint=90:5C:34:AB:30:47:9F:1A:40:98:08:99:62:07:78:E7:9F:8D:C0:4B

% openssl s_client -CAfile /etc/pykmip/certs/server_ca_cert.pem -connect <keycontrol-vault-ip-address>:5696

CONNECTED(00000003)
.
.
.
--
SSL handshake has read 2800 bytes and written 433 bytes
Verification: OK
--
New, TLSv1.2, Cipher is ECDHE-RSA-AES256-GCM-SHA384
Server public key is 2048 bit
Secure Renegotiation IS supported
Compression: NONE
Expansion: NONE
No ALPN negotiated
SSL-Session:
    Protocol  : TLSv1.2
    Cipher    : ECDHE-RSA-AES256-GCM-SHA384
    Session-ID: 67C77D394B46DD966582BDF7E55B41F5B74A2DD8BE03FE3F6C76ADA8860CE414
    Session-ID-ctx:
    Master-Key: 71120791CFDDCA302C0C5A49AB999D8D5924233683A3D883BDFE177397E75444B097596BCC6D524C69338D88085119FA
    PSK identity: None
    PSK identity hint: None
    SRP username: None
    TLS session ticket lifetime hint: 300 (seconds)
    TLS session ticket:
    0000 - 70 bf ca 92 53 2d b5 29-41 c4 4c 44 5b e3 97 6a   p...S-.)A.LD[..j
    0010 - cf 7c 04 d6 9a a1 29 6f-60 70 d5 11 ab 41 7d 5b   .|....)o`p...A}[
    0020 - 35 d8 ee 96 0c 45 8a 6d-fe af ea 5b 5f 31 55 12   5....E.m...[_1U.
    0030 - 7f 90 74 5c d8 ec 17 69-42 09 38 22 20 03 39 9e   ..t\...iB.8" .9.
    0040 - e5 bd 4a 72 17 81 bd 68-66 e7 9f 6a 01 ec 75 79   ..Jr...hf..j..uy
    0050 - c5 e5 48 b3 85 60 0b 5e-ea c3 d9 c3 bd 5f c9 4f   ..H..`.^....._.O
    0060 - c9 e2 a7 ee 53 18 d2 ca-6c 13 1d 2c 8b 1c 1c dd   ....S...l..,....
    0070 - d2 6b 01 f1 56 1e 3b 6f-04 a0 e4 ba c5 03 1a 8d   .k..V.;o........
    0080 - 24 4f 1f 8f fe b3 7e cc-9f bc 59 87 7f 34 e5 a1   $O....~...Y..4..
    0090 - 49 2e 21 a9 27 3c a0 2e-03 7f 7e f7 53 cc 40 56   I.!.'<....~.S.@V

    Start Time: 1725981994
    Timeout   : 7200 (sec)
    Verify return code: 0 (ok)
    Extended master secret: no
--
closed

% cd ~/pykmip/
% python3.9 ./kmip/demos/pie/create.py -a AES -l 256

2024-09-10 15:48:00,086 - demo - INFO - Successfully created symmetric key with ID: 74cf4f21-a006-467d-a64f-fbc3fa6ab6d1

% sudo dnf install -y edb-tde-kmip-client

Updating Subscription Management repositories.
enterprisedb-enterprise                                               529  B/s | 659  B     00:01
enterprisedb-enterprise-noarch                                        727  B/s | 659  B     00:00
enterprisedb-enterprise-source                                        842  B/s | 659  B     00:00
Dependencies resolved.
=================================================================================================
 Package                 Architecture  Version         Repository                           Size
=================================================================================================
Installing:
 edb-tde-kmip-client     noarch        1.0-1.el9       enterprisedb-enterprise-noarch        14 k

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

Total download size: 14 k
Installed size: 20 k
Downloading Packages:
edb-tde-kmip-client-1.0-1.el9.noarch.rpm                                11 kB/s |  14 kB     00:01
---------------------------------------------------------------------------------------------------
Total                                                                   11 kB/s |  14 kB     00:01
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.el9.noarch                                      1/1
  Verifying        : edb-tde-kmip-client-1.0-1.el9.noarch                                      1/1
Installed products updated.

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

Complete!

% find / -name edb_tde_kmip_client.py
/usr/edb/kmip/client/edb_tde_kmip_client.py

% printf secret | python3.9 /usr/edb/kmip/client/edb_tde_kmip_client.py encrypt \
  --out-file=$HOME/test.bin \
  --pykmip-config-file=/etc/pykmip/pykmip.conf \
  --key-uid='74cf4f21-a006-467d-a64f-fbc3fa6ab6d1' \
  --variant=pykmip

% ls -al $HOME/test.bin

-rw-r--r--. 1 enterprisedb enterprisedb 32 Sep 10 12:06 test.bin

% python3.9 /usr/edb/kmip/client/edb_tde_kmip_client.py decrypt \
  --in-file=$HOME/test.bin \
  --pykmip-config-file=/etc/pykmip/pykmip.conf \
  --key-uid='74cf4f21-a006-467d-a64f-fbc3fa6ab6d1' \
  --variant=pykmip

secret

$ sudo su - enterprisedb

$ cd /usr/edb/as16/bin

% export PGDATAKEYWRAPCMD='python3.9 /usr/edb/kmip/client/edb_tde_kmip_client.py encrypt \
   --out-file=%p \
  --pykmip-config-file=/etc/pykmip/pykmip.conf \
  --key-uid="74cf4f21-a006-467d-a64f-fbc3fa6ab6d1" \
  --variant=pykmip'

% export PGDATAKEYUNWRAPCMD='python3.9 /usr/edb/kmip/client/edb_tde_kmip_client.py decrypt \
  --pykmip-config-file=/etc/pykmip/pykmip.conf \
  --key-uid="74cf4f21-a006-467d-a64f-fbc3fa6ab6d1" \
  --in-file=%p --variant=pykmip'

% env | grep PGDATAKEY

PGDATAKEYWRAPCMD=python3.9 /usr/edb/kmip/client/edb_tde_kmip_client.py encrypt --out-file=%p --pykmip-config-file=/etc/pykmip/pykmip.conf --key-uid="74cf4f21-a006-467d-a64f-fbc3fa6ab6d1" --variant=pykmip
PGDATAKEYUNWRAPCMD=python3.9 /usr/edb/kmip/client/edb_tde_kmip_client.py decrypt --pykmip-config-file=/etc/pykmip/pykmip.conf --key-uid="74cf4f21-a006-467d-a64f-fbc3fa6ab6d1" --in-file=%p --variant=pykmip

% /usr/edb/as16/bin/initdb -D /var/lib/edb/as16/data -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 "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 (128 bits).

creating directory /var/lib/edb/as16/data ... 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 ... /var/lib/edbok
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
.
.
.
utl_raw_public.sql
utl_raw.plb
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:

    /usr/edb/as16/bin/pg_ctl -D /var/lib/edb/as16/data -l logfile start

% /usr/edb/as16/bin/pg_ctl -D /var/lib/edb/as16/data -l $HOME/logfile start

waiting for server to start..... done
server started

# - Authentication -

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

# GSSAPI using Kerberos
#krb_server_keyfile = 'FILE:${sysconfdir}/krb5.keytab'
#krb_caseins_users = off
#gss_accept_delegation = 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.9 /usr/edb/kmip/client/edb_tde_kmip_client.py decrypt --pykmip-config-file=/etc/pykmip/pykmip.conf --key-uid="74cf4f21-a006-467d-a64f-fbc3fa6ab6d1" --in-file=%p --variant=pykmip'

% /usr/edb/as16/bin/pg_controldata /var/lib/edb/as16/data

pg_control version number:            1501
Catalog version number:               202307071
.
.
.
Float8 argument passing:              by value
Data page checksum version:           0
Data encryption version:              1
Data encryption key length:           128
Mock authentication nonce:            ac2f2e58c2ace59d45af6642d3515e689891d8d1388c65c9d8bc827732710e59

% /usr/edb/as16/bin/createdb --owner enterprisedb hr

% /usr/edb/as16/bin/psql hr

psql (16.4)
Type "help" for help.

hr=#

hr=# CREATE TABLE public.dept (deptno numeric(2) NOT NULL CONSTRAINT dept_pk PRIMARY KEY, dname varchar(14) CONSTRAINT dept_dname_uq UNIQUE, loc varchar(13));
CREATE TABLE

hr=# INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK');
INSERT 0 1
hr=# INSERT into dept VALUES (20,'RESEARCH','DALLAS');
INSERT 0 1

hr=# SELECT * FROM dept;

 deptno |   dname    |   loc
--------+------------+----------
     10 | ACCOUNTING | NEW YORK
     20 | RESEARCH   | DALLAS
(2 rows)

% /usr/edb/as16/bin/pg_ctl -D /var/lib/edb/as16/data -l $HOME/logfile stop

waiting for server to shut down.... done
server stopped

% /usr/edb/as16/bin/pg_ctl -D /var/lib/edb/as16/data -l $HOME/logfile start
waiting for server to start..... stopped waiting
pg_ctl: could not start server
Examine the log output.

% cat $HOME/logfile

2024-09-10 17:04:26 EDT LOG:  redirecting log output to logging collector process
2024-09-10 17:04:26 EDT HINT:  Future log output will appear in directory "log".
An error occurred while connecting to appliance <keycontrol-server-ip-address>: [Errno 101] Network is unreachable
could not open client connection: [Errno 101] Network is unreachable
Traceback (most recent call last):
  File "/usr/edb/kmip/client/edb_tde_kmip_client.py", line 113, in <module>
    main()
  File "/usr/edb/kmip/client/edb_tde_kmip_client.py", line 70, in main
    with pykmip_client:
  File "/var/lib/edb/pykmip/kmip/pie/client.py", line 1753, in __enter__
    self.open()
  File "/var/lib/edb/pykmip/kmip/pie/client.py", line 173, in open
    self.proxy.open()
  File "/var/lib/edb/pykmip/kmip/services/kmip_client.py", line 285, in open
    six.reraise(*last_error)
  File "/usr/lib/python3.9/site-packages/six.py", line 709, in reraise
    raise value
  File "/var/lib/edb/pykmip/kmip/services/kmip_client.py", line 274, in open
    self.socket.connect((self.host, self.port))
  File "/usr/lib64/python3.9/ssl.py", line 1376, in connect
    self._real_connect(addr, False)
  File "/usr/lib64/python3.9/ssl.py", line 1363, in _real_connect
    super().connect(addr)
OSError: [Errno 101] Network is unreachable
2024-09-10 17:38:10 EDT FATAL:  could not run command "python3.9 /usr/edb/kmip/client/edb_tde_kmip_client.py decrypt --pykmip-config-file=/etc/pykmip/pykmip.conf --key-uid="74cf4f21-a006-467d-a64f-fbc3fa6ab6d1" --in-file=pg_encryption/key.bin --variant=pykmip": child process exited with exit code 1
2024-09-10 17:38:10 EDT LOG:  database system is shut down

% /usr/edb/as16/bin/pg_ctl -D /var/lib/edb/as16/data -l $HOME/logfile start
waiting for server to start..... done
server started

% /usr/edb/as16/bin/psql hr

hr=# SELECT * FROM dept;

 deptno |   dname    |   loc
--------+------------+----------
     10 | ACCOUNTING | NEW YORK
     20 | RESEARCH   | DALLAS
(2 rows)

% cd ~/pykmip/
% python3.9 ./kmip/demos/pie/create.py -a AES -l 256

2024-09-11 10:05:39,278 - demo - INFO - Successfully created symmetric key with ID: 34816dab-24e5-4635-9990-00684b84a8c4

% cd $PGDATA/pg_encryption

% cp key.bin key.bin.original

python3.9 /usr/edb/kmip/client/edb_tde_kmip_client.py decrypt --pykmip-config-file=/etc/pykmip/pykmip.conf --key-uid="74cf4f21-a006-467d-a64f-fbc3fa6ab6d1" --in-file=key.bin --variant=pykmip |
python3.9 /usr/edb/kmip/client/edb_tde_kmip_client.py encrypt --out-file=key.bin --pykmip-config-file=/etc/pykmip/pykmip.conf --key-uid="34816dab-24e5-4635-9990-00684b84a8c4"  --variant=pykmip

#data_encryption_key_unwrap_command = 'python3.9 /usr/edb/kmip/client/edb_tde_kmip_client.py decrypt --pykmip-config-file=/etc/pykmip/pykmip.conf --key-uid="74cf4f21-a006-467d-a64f-fbc3fa6ab6d1" --in-file=%p --variant=pykmip'
data_encryption_key_unwrap_command = 'python3.9 /usr/edb/kmip/client/edb_tde_kmip_client.py decrypt --pykmip-config-file=/etc/pykmip/pykmip.conf --key-uid="34816dab-24e5-4635-9990-00684b84a8c4" --in-file=%p --variant=pykmip'

% cd

% /usr/edb/as16/bin/pg_ctl -D /var/lib/edb/as16/data -l $HOME/logfile stop

waiting for server to shut down.... done
server stopped

% /usr/edb/as16/bin/pg_ctl -D /var/lib/edb/as16/data -l $HOME/logfile start
waiting for server to start..... done
server started

% /usr/edb/as16/bin/psql hr

hr=# SELECT * FROM dept;

 deptno |   dname    |   loc
--------+------------+----------
     10 | ACCOUNTING | NEW YORK
     20 | RESEARCH   | DALLAS
(2 rows)