In the last post we did the setup of Fujitsu Enterprise Postgres so we’re now ready to look at how TDE is implemented in this distribution of PostgreSQL. The unit of encryption in this version of PostgreSQL is a tablespace but before we can encrypt anything we need to create a master encryption key.

The location where the keystore gets created is specified by the “keystore_location” parameter, a parameter not known in community PostgreSQL:

postgres@rhel9-latest:/home/postgres/ [fe18] mkdir /u02/pgdata/keystore
postgres@rhel9-latest:/home/postgres/ [fe18] psql -c "alter system set keystore_location = '/u02/pgdata/keystore'"
ALTER SYSTEM
postgres@rhel9-latest:/home/postgres/ [fe18] sudo systemctl restart fe-postgres-1.service
postgres@rhel9-latest:/home/postgres/ [fe18] psql -c "show keystore_location"
  keystore_location   
----------------------
 /u02/pgdata/keystore
(1 row)

Once that is ready we need to create the master encryption key using a function called “pgx_set_master_key”:

postgres@rhel9-latest:/home/postgres/ [fe18] psql
psql (18.0)
Type "help" for help.

postgres=# \df pgx_set_master_key
                                List of functions
   Schema   |        Name        | Result data type | Argument data types | Type 
------------+--------------------+------------------+---------------------+------
 pg_catalog | pgx_set_master_key | void             | text                | func
(1 row)

postgres=# SELECT pgx_set_master_key('secret');
ERROR:  passphrase is too short or too long
DETAIL:  The length of the passphrase must be between 8 and 200 bytes.
postgres=# SELECT pgx_set_master_key('secret123');
 pgx_set_master_key 
--------------------
 
(1 row)

This created a new keystore file under the location we’ve specified above which is of course not human readable:

postgres=# \! ls -la /u02/pgdata/keystore
total 4
drwxr-xr-x. 2 postgres postgres  25 Jan 28 10:43 .
drwxr-xr-x. 4 postgres postgres  34 Jan 28 10:39 ..
-rw-------. 1 postgres postgres 928 Jan 28 10:43 keystore.ks
postgres=# \! strings /u02/pgdata/keystore/keystore.ks
KSTR
@NA\
Io      BS
a!]I
>yu;
2r<:4
G)n%j
6wE"
@{OT
ym&M]
@1l'z
}5>,

The advantage of implementing TDE on the tablespace level and not the whole instance is, that we can still restart the instance without specifying the master encryption key:

postgres@rhel9-latest:/home/postgres/ [fe18] sudo systemctl restart fe-postgres-1.service
postgres@rhel9-latest:/home/postgres/ [fe18] psql
psql (18.0)
Type "help" for help.

postgres=$

Before we can encrypt anything we need to open the keystore using another function called “pgx_open_keystore”:

postgres=# \df pgx_open_keystore
                               List of functions
   Schema   |       Name        | Result data type | Argument data types | Type 
------------+-------------------+------------------+---------------------+------
 pg_catalog | pgx_open_keystore | void             | text                | func
(1 row)

postgres=# select pgx_open_keystore('secret123');
 pgx_open_keystore 
-------------------
 
(1 row)

Once the keystore is open we can create an encrypted tablespace and put some data inside:

postgres=# \! mkdir /var/tmp/tbsencr
postgres=# create tablespace tbsencr location '/var/tmp/tbsencr' with (tablespace_encryption_algorithm = 'AES256' );
CREATE TABLESPACE
postgres=# \dbs+
                                                        List of tablespaces
    Name    |  Owner   |     Location     | Access privileges |                 Options                  |   Size    | Description 
------------+----------+------------------+-------------------+------------------------------------------+-----------+-------------
 pg_default | postgres |                  |                   |                                          | 23 MB     | 
 pg_global  | postgres |                  |                   |                                          | 790 kB    | 
 tbsencr    | postgres | /var/tmp/tbsencr |                   | {tablespace_encryption_algorithm=AES256} | 928 bytes | 
(3 rows)
postgres=# create table t1 ( a int, b text ) tablespace tbsencr;
CREATE TABLE
postgres=# insert into t1 select i, i::text from generate_series(1,100) i;
INSERT 0 100

Trying to read from that table without opening the keystore will of course fail:

postgres@rhel9-latest:/home/postgres/ [fe18] sudo systemctl restart fe-postgres-1.service
postgres@rhel9-latest:/home/postgres/ [fe18] psql -c "select * from t1"
ERROR:  could not encrypt or decrypt data because the keystore is not open
HINT:  Open the existing keystore, or set the master encryption key to create and open a new keystore

There are not only pgx_* functions, there are also pgx_* catalog views which give you more information about specific topics not available in community PostgreSQL, e.g. for listing the tablespaces and their encryption scheme:

postgres=# select spcname, spcencalgo from pg_tablespace ts, pgx_tablespaces tsx where ts.oid = tsx.spctablespace;
  spcname   | spcencalgo 
------------+------------
 pg_default | none
 pg_global  | none
 tbsencr    | AES256
(3 rows)

Like in Oracle with the wallet there is the option to auto open the keystore:

postgres@rhel9-latest:/home/postgres/ [fe18] pgx_keystore --enable-auto-open /u02/pgdata/keystore/keystore.ks 
Enter passphrase: 
auto-open of the keystore has been enabled

The whole management of the key store and also the auto open options are explained well in the official documentation.

Btw: If you dump the data with pg_dump while the keystore is open this will result in an unencrypted dump:

postgres@rhel9-latest:/home/postgres/ [fe18] pg_dump | grep -A 3 COPY
COPY public.t (a) FROM stdin;
1
\.

--
COPY public.t1 (a, b) FROM stdin;
1       1
2       2
3       3

There is the pgx_dmpall command which can be used to backup the whole instance, but this is out of scope for this post.

Post Views: 7
Thumbnail [60x60]
by
Daniel Westermann