Perform the Initial Configuration and Create an Encrypted Postgres Database

1. Log in to the Postgres Linux server as user enterprisedb.

2. Go to the /bin directory. In this case the /bin directory path is /usr/edb/as17/bin.

3. Set the key wrap and key unwrap commands.

   1. Set PGDATAKEYWRAPCMD: shell command to encrypt the data encryption key.
      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="12d8888e-a382-4762-b357-6050fff6f84e" \

      --variant=pykmip'

   2. Set PGDATAKEYUNWRAPCMD: shell command to decrypt the data encryption key when the database starts.
      export PGDATAKEYUNWRAPCMD='python3.9 /usr/edb/kmip/client/edb_tde_kmip_client.py \

      decrypt \

      --pykmip-config-file=/etc/pykmip/pykmip.conf \

      --key-uid="12d8888e-a382-4762-b357-6050fff6f84e" \

      --in-file=%p --variant=pykmip'

   3. Verify the key wrap and key unwrap variables set.
      $ env | grep PGDATAKEY

4. Perform the initial configuration of the database.
   The database configuration can be done with the following command.
   $ /usr/edb/as17/bin/initdb -D /var/lib/edb/as17/data -y
   

Linux server: initial configuration of the Postgres database

5. Start the database server.
   The database server can be started with the following command, and the output log can be passed to $HOME/log.
   $ /usr/edb/as17/bin/pg_ctl -D /var/lib/edb/as17/data -l $HOME/logfile start

6. Verify the data_encryption_key_unwrap_command in the postgresql.conf file.
   The data_encryption_key_unwrap_command is set with PGDATAUNWRAPCMD should be present in the /var/lib/edb/as17/data/postgresql.conf file. 

7. Ensure encryption is enabled.
   Execute the following command and confirm ‘Data encryption version’ and ‘Data encryption key length’ are set.
   $ /usr/edb/as17/bin/pg_controldata /var/lib/edb/as17/data

8. Create a database for the enterprisedb user to do the testing.
   The command for creating database hr as user enterprisedb is:
   $ /usr/edb/as17/bin/createdb --owner enterprisedb hr 

9. Connect to the hr database in psql.
   $ /usr/edb/as17/bin/psql hr

10. Create columns.
    The tables are created with the CREATE TABLE command.
    Here is an example for creating table ‘dept’:
    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));

11. Insert values into the table.
    here is an example for inserting values into the table ‘dept’:
    hr=# INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK');
hr=# INSERT into dept VALUES (20,'RESEARCH','DALLAS';

12. View the table data.
    The table data can be viewed by selecting the values from the table with the command below.
    hr=# SELECT * FROM dept;