written by Granthana Biswas
Table of Contents
Cluster encryption can be used if the DBA cannot or does not rely on the file system in terms of confidentiality.
If cluster encryption is enabled, PostgreSQL encrypts data (both relations and write-ahead log) when writing to disk, and decrypts it when reading. The encryption is transparent, so the applications see no difference between the encrypted and unencrypted clusters.
In this blog, we go through the basic steps used to install PostgreSQL 9.6 with Transparent Data Encryption (TDE) on Ubuntu. TDE is a part of PGEE.
|
1 2 |
sudo mkdir -p /usr/local/pgsql/data sudo chown postgres:postgres /usr/local/pgsql/data |
sudo apt-get install libreadline8 libreadline-dev zlibc zlib1g-dev bison flex libssl-dev openssl
You can choose where all files will be installed by passing --prefix. Default is /usr/local/pgsql which I am using here for example. Make sure you enable openssl by passing --with-openssl:
|
1 2 |
sudo ./configure --prefix=/usr/local/pgsql --with-openssl sudo make |
|
1 |
sudo make install |
|
1 |
sudo su - postgres |
|
1 |
export PATH=$PATH:/usr/local/pgsql/bin |
To create an encrypted cluster, use the -K option to pass the initdb utility. For example:
|
1 |
initdb -D /usr/local/pgsql/data -K/usr/local/pgsql/keypass |
Here /usr/local/pgsql/keypass is an executable file that returns either encryption key or encryption password with the appropriate prefix. In this case, we are passing the encryption_password in 8-16 characters in a simple executable file which outputs:
|
1 2 3 4 5 6 7 |
encryption_password=<8-16_passphrase> $ chmod 755 /usr/local/pgsql/keypass $ cat /usr/local/pgsql/keypass echo encryption_password=UrOs2k11CHiGo |
Internally, PostgreSQL always uses the encryption key. If the encryption key command returns a password then a key will be generated from the password using a built-in key derivation function. Optionally, you can pass encryption_key as a hex encoded 256 bit key from any key store.
|
1 2 3 |
$cat /usr/local/pgsql/keypass echo encryption_key=<`sh /location/return-key.sh`> |
On completion, initdb stores the encryption key command to postgresql.conf. Thus, the user can control the cluster using pg_ctl without passing the encryption command again and again.
If encryption is enabled, full_page_writes must not be turned off, otherwise the server refuses to start. This is because the encryption introduces differences between data within a page, and therefore a server crash during a disk write can result in more serious damage of the page than it would do without encryption. The whole page needs to be retrieved from WAL in such a case to ensure reliable recovery.
|
1 2 3 |
vi postgresql.conf full_page_writes = on |
Once the PostgreSQL server is running, client applications should recognize no difference from an unencrypted cluster, except that data_encryption configuration variable is set.
Unlike pg_ctl, some of the server applications (for example pg_waldump) do not need the -K because they are not able to process the postgresql.conf file.
Since WAL is encrypted, any replication solution based on log shipping assumes that all standby servers are encrypted using the same key as their standby server. On the other hand, logica -replication allows replication between encrypted and unencrypted clusters, or between clusters encrypted with different keys.
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
This line is not executing...
initdb -D /usr/local/pgsql/data -K/usr/local/pgsql/keypass
Can you provide any manual of that TDE tool?
can you kindly provide the error message? "not executing" is not enough.
I was able to build and install postgres. But when i created a table and inserted some rows, i was still able to do a select and see all the plain data as a user of postgres db. I thought i should see encrypted data meaning jumbled data. When i do show encryption in psql it says on. But no encrytion. What is going on? Any idea?
I am getting this error when i tried..
-sh-4.2$ initdb -D /data/postgre/db2 -K/data/postgre/key/keypass
initdb: invalid option -- 'K'
Try "initdb --help" for more information.
-sh-4.2$
can you suggest how you incrypted the postgres at instance level.
Thanks in advance.
i assume you are using "normal" PostgreSQL and not our TDE version
yes, not TDE version, how to get it? and can't we do instance encryption for normal postgres?
Hi Team,
1) Is there a way to do the same with PostgreSQL 10 or 11
2) How can I verify that the data is well encrypted?
Thanks in advance
Hi, when i run
initdb -D /usr/local/pgsql/data -K/usr/local/pgsql/keypass
I get the error:
running bootstrap script ... sh: 1: /usr/local/pgsql/keypass: not found
keypass doesn't exist
I've compiled postgresql-9.6.12-tde.tar.gz
make and make install were executed correctly but keypass not was created
Thanks in advance
hello. you have to write the script to provide the key. the binary is not there by default.
Thanks for this nice post.
Can you share any experiences running "transparent data encryption-psql" as a docker container?
By the way: On postgres 12.1 the part with the keypass file needs to be like this:
export ENCRYPTION_PASSWORD="echo {your-32-char-key}"
echo $ENCRYPTION_PASSWORD > /usr/local/pgsql/keypass
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data -K/usr/local/pgsql/keypass
Please tell me if I'm wrong, but encryption_password=key didn't work. Moreover if the key is not exactly 32 characters long, the error is either "key too short" or "key too long".
Kind regads.
Tobias
Its a greate initiative.
BTW can i integrate it with third party tools such as Repmgr, pgbackrest , Barman?
Hi Cybertec Team,
Great patch!! I was wondering if there is any example solution where key rotation with the postgresTDE has been implemented?
Best regards,
Asad
Hi Team,
After successfully installation now I am not able to create extensions "uuid-ossp,postgis,pg_repack" in the DB can you help me out.
Hi Team,
I have setup Pgsql 12 TDE successfully and showing expected output by this command
"pg_controldata /some_where/ | grep -i encryp"
and successful with giving output when using command
test=# SHOW data_encryption;
doing so, my database should be encrypted.
i took a pg_dump and imported to the another posgresql12 (without TDE). and same database was also working fine there too.
if this can work on another database then what is the purpose of TDE?
or i am missing something in configuration?
i completely followed this link :
https://www.cybertec-postgresql.com/transparent-data-encryption-installation-guide/
when i am running this command:- /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data -K /usr/local/pgsql/keypass
i am getting this error:- sh: 1: /usr/local/pgsql/keypass: Permission denied
initdb: fatal: could not read encryption key from command "/usr/local/pgsql/keypass": Success
initdb: removing contents of data directory "/usr/local/pgsql/data"
Please let me with this error.
If the permissions on
/usr/local/keypass(and all the directories above it) allowpostgresto execute the file, it could be a SE-Linux problem.hello Team,
I have implemented all the steps successfully and with the command its showing encryption: on but how I should demonstrate to my seniors that TDE is implemented because the data is stored plan