Some days ago a customer asked me how to check if TDE works and files on disk are encrypted. So I installed a new virtual machine with Ubuntu and installed our PGEE-Demo on it. After installation, the new created cluster is unencrypted. So we can try to create a new table and insert one record on it with a short text. To make sure that the record is saved into the table file I called a checkpoint. To hexdump the table file I need to know the data directory and filepath, you can see all commands here:
Table of Contents
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
postgres@ubuntupgee:~$ psql psql (16.1 EE 1.3.1 (Ubuntu 16.1ee1.3.1-1.cybertec20.04~demo+1), server 16.1 EE 1.3.1 (Ubuntu 16.1ee1.3.1-1.cybertec20.04~demo+1)) Type 'help' for help. postgres=# show data_encryption; data_encryption ----------------- off (1 row) postgres=# create table encrypted(t text); CREATE TABLE postgres=# insert into encrypted values ('this is a test to check if the text is stored encrypted'); INSERT 0 1 postgres=# checkpoint; CHECKPOINT postgres=# SELECT pg_relation_filepath('encrypted'); pg_relation_filepath ---------------------- base/5/16388 (1 row) postgres=# show data_directory; data_directory ----------------------------- /var/lib/postgresql/16/main (1 row) postgres=# q |
So I've now exited psql and can create a hexdump of the table file.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
postgres@ubuntupgee:~$ hexdump -C /var/lib/postgresql/16/main/base/5/16388 00000000 00 00 00 00 00 4f 54 01 00 00 00 00 1c 00 b0 1f |.....OT.........| 00000010 00 20 04 20 00 00 00 00 b0 9f a0 00 00 00 00 00 |. . ............| 00000020 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................| * 00001fb0 e9 02 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................| 00001fc0 01 00 01 00 02 08 18 00 71 74 68 69 73 20 69 73 |........qthis is| 00001fd0 20 61 20 74 65 73 74 20 74 6f 20 63 68 65 63 6b | a test to check| 00001fe0 20 69 66 20 74 68 65 20 74 65 78 74 20 69 73 20 | if the text is | 00001ff0 73 74 6f 72 65 64 20 65 6e 63 72 79 70 74 65 64 |stored encrypted| 00002000 postgres@ubuntupgee:~$ |
We can see here that PostgreSQL has created an 8 Kbyte file that is initially completely filled with NULL values. At the beginning there is a small header that is of no further interest to us here.
The actual table data is written into the block from the back, and we can see our text here in plain text.
I will now delete the cluster and reinitialize it, this time encrypted. I completely follow the instructions here.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
root@ubuntupgee:/home/pgee# pg_dropcluster --stop 16 main root@ubuntupgee:/home/pgee# KEY=$(dd if=/dev/random bs=1k count=1 | md5sum - | cut -d ' ' -f 1) 1+0 records in 1+0 records out 1024 bytes (1.0 kB, 1.0 KiB) copied, 0.000808704 s, 1.3 MB/s root@ubuntupgee:/home/pgee# echo $KEY f60ecd1944958aac745a513c136cd3a9 root@ubuntupgee:/home/pgee# pg_createcluster --start 16 pgee -- -k -K 'echo $KEY' Creating new PostgreSQL cluster 16/pgee ... /usr/lib/postgresql/16/bin/initdb -D /var/lib/postgresql/16/pgee --auth-local peer --auth-host scram-sha-256 --no-instructions -k -K echo f60ecd1944958aac745a513c136cd3a9 The files belonging to this database system will be owned by user 'postgres'. 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 enabled. Data encryption is enabled. fixing permissions on existing directory /var/lib/postgresql/16/pgee ... ok creating subdirectories ... ok selecting dynamic shared memory implementation ... posix selecting default max_connections ... 100 selecting default shared_buffers ... 128MB selecting default time zone ... Etc/UTC creating configuration files ... ok running bootstrap script ... ok performing post-bootstrap initialization ... ok syncing data to disk ... ok Ver Cluster Port Status Owner Data directory Log file 16 pgee 5432 online postgres /var/lib/postgresql/16/pgee /var/log/postgresql/postgresql-16-pgee.log root@ubuntupgee:/home/pgee# |
Now the cluster is running with encryption turned on, the command for the encryption key is in postgresql.conf:
|
1 2 |
root@ubuntupgee:/home/pgee# grep encryption_key_command /etc/postgresql/16/pgee/postgresql.conf encryption_key_command = 'echo f60ecd1944958aac745a513c136cd3a9' |
I now start psql again to create a table like in the beginning, call a CHECKPOINT and get the path to the file:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
postgres=# create table encrypted(t text); CREATE TABLE postgres=# insert into encrypted values ('this is a test to check if the text is stored encrypted'); INSERT 0 1 postgres=# checkpoint; CHECKPOINT postgres=# SELECT pg_relation_filepath('encrypted'); pg_relation_filepath ---------------------- base/5/16388 (1 row) postgres=# show data_directory; data_directory ----------------------------- /var/lib/postgresql/16/pgee (1 row) postgres=# q |
Again I call hexdump on the table file:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
postgres@ubuntupgee:~$ hexdump -C /var/lib/postgresql/16/pgee/base/5/16388 00000000 00 00 00 00 f0 e1 79 01 d3 6a bc 42 a5 95 e8 8b |......y..j.B....| 00000010 1e df 30 ad 5f 61 0f 55 6d af f4 79 9b ba 77 69 |..0._a.Um..y..wi| 00000020 c6 c7 c5 61 c5 4c f4 a8 00 64 35 af 79 fa e7 fd |...a.L...d5.y...| 00000030 09 a4 07 13 2e c1 76 45 1f ed 03 42 3d d5 1f 8f |......vE...B=...| 00000040 38 3f 87 ad 7c d0 47 79 ed 05 1b 18 34 b6 68 ff |8?..|.Gy....4.h.| 00000050 f6 65 63 4d e2 04 16 bd 3b 8a df 20 22 ff f3 7f |.ecM....;.. '...| 00000060 f1 93 6f 04 f3 e1 94 43 8d 19 e4 8b e9 4e 64 3e |..o....C.....Nd>| 00000070 4c c9 94 ab 64 da d1 d7 39 66 85 36 65 d1 0d 84 |L...d...9f.6e...| 00000080 e8 fd 7e 38 52 7e e3 39 20 4d eb 56 f9 e2 20 f6 |..~8R~.9 M.V.. .| 00000090 77 1e 83 ec 66 9d 8b 95 90 4a 0c 2a 72 ad 0f 24 |w...f....J.*r..$| 000000a0 5a 2f 9f d9 16 31 f5 6a b9 8e 88 18 c3 ed 3f 28 |Z/...1.j......?(| 000000b0 97 d6 27 6c df 37 68 f5 22 3b 0f 57 9e ee 3b b5 |..'l.7h.';.W..;.| 000000c0 39 b8 7a 32 7f a0 13 3d ea 94 1d be c4 84 27 74 |9.z2...=......'t| 000000d0 c1 28 93 c5 1e ee ff 56 bb 0f 91 47 57 38 a6 fb |.(.....V...GW8..| 000000e0 60 45 a1 d0 83 8b e8 45 e7 86 71 1d 44 b6 93 d3 |`E.....E..q.D...| 000000f0 b8 af e7 a7 85 47 d9 dc bf 78 60 02 74 8c 84 2c |.....G...x`.t..,| 00000100 e8 56 86 af d9 78 2f 9d c5 ff 3b 33 30 b7 5e bc |.V...x/...;30.^.| 00000110 d8 5d 83 c6 c3 50 91 68 e8 30 c6 82 eb be 07 36 |.]...P.h.0.....6| 00000120 8d 1c 53 a0 e8 5d 58 61 0a 90 7c 38 7f de 74 d4 |..S..]Xa..|8..t.| 00000130 03 e0 f9 ec 9a 85 97 b3 47 3c fa 15 74 0c af 49 |........G<..t..I| 00000140 1c b8 71 40 7a 17 c4 eb be a7 f1 46 6b d1 1f 57 |..q@z......Fk..W| 00000150 bd 5a f0 36 ac da c9 f5 ce 48 79 3a d1 7d 58 bb |.Z.6.....Hy:.}X.| 00000160 b3 f1 29 d8 0b 13 e8 8f 04 34 71 29 3d fd 1f 90 |..)......4q)=...| 00000170 4e 6b f1 fd 3a 7f 8b d1 2e fb 04 b5 b5 a4 82 fb |Nk..:...........| 00000180 2e 92 de 9b 24 22 bc 28 0e 7f 7c 96 72 a8 8d 59 |.... |
The dump is now larger, but only because hexdump suppressed all NULL values in the unencrypted dump. So we see that the entire block is encrypted and not just the individual data record. So not only the text is encrypted, but also the entire free area of the table.
/en/manage-encryption-keys-with-postgresql-tde/
/en/postgresql-tde-performance/
You need to load content from reCAPTCHA to submit the form. Please note that doing so will share data with third-party providers.
More InformationYou are currently viewing a placeholder content from Facebook. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More InformationYou are currently viewing a placeholder content from X. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More Information
Leave a Reply