Security has always been a great concern of Enterprises. Especially, if you have crucial information stored in the database, you would always prefer to have high security around it. Over the years, technologies have evolved and provided better solutions around it.
If you have very sensitive information, people try to keep this information encrypted so, that in case, somebody gets access of the system, then they cannot view this information, if they are not authorized.
Moving Oracle to the Cloud?
Learn 5 key decisions to make to ensure success. Get the Whitepaper.
For managing sensitive information, Enterprises use multiple methods:
- Encrypting specific information.
If you are PPAS users, you would like to use DBMS_CRYPTO package which provides a way of encrypting sensitive information in databases.
For more information, please refer following link:
For PostgreSQL, users can use pgcrypto module.
- Transparent Data Encryption (TDE) is another method employed by both Microsoft and Oracle to encrypt database files. TDE offers encryption at file level. This method solves the problem of protecting data at rest i.e. encrypting databases both on the hard drive and consequently on backup media. Enterprises typically employ TDE to solve compliance issues such as PCI DSS.
Postgres Plus, currently doesn’t have inbuilt TDE, however, if Enterprises looking for encryption at the database file level, they can use one of the following methods for protecting data at rest:
- Full Disk Encryption:
Full disk or partition encryption is one of the best ways of protecting your data. This method not only protects each file, however, also protects the temporary storage that may contain parts of these files. Full disk encryption protects all of your files and then you do not have to worry about selecting what you want to protect and possibly missing a file.
RHEL (Red Hat) supports Linux Unified Key Setup-on-disk-format (or LUKS). LUKS bulk encrypts Hard Drive partition.
For more information on LUKS, please refer following link:
- File system-level encryption:
File system-level encryption often called file/directory encryption. In this method individual files or directories are encrypted by the file system itself.
There is stackable cryptographic file system encryption available which user can utilize in their environment.
File system level Encryption gives following advantages:
- Flexible file-based key management, so that each file can be and usually is encrypted with a separate encryption key.
- Individual management of encrypted files e.g. Incremental backups of the individual changed files even in encrypted form, rather than backup of the entire encrypted volume.
- Access control can be enforced through the use of public-key cryptography, and the fact that cryptographic keys are only held in memory while the file that is decrypted by them is held open.
Stackable cryptographic file system encryption can be use for Postgres for Transparent Data Encryption.
In this blog, I will discuss using mount ecrpytfs as it requires less overhead in setup (LUKS requires a new disk to be configured and formatted before storing data on it. “mount ecrpytfs” works with existing directories and data).
If Enterprises want to give the control to DBAs for TDE, they can use/define few sudo rules for DBAs to execute commands for encryption.
Following is a method, which they can use:
- Ask system admin to create sudo rules to allow DBA to execute encryption for data directory for Postgres Plus. One common way to do this is using the “mount ecryptfs” command in Linux operating systems.
- If user needs to encrypt the /ppas94/data directory, they can use following command:
sudo mount -t ecryptfs /ppas94/data /ppas94/data
More information can be found in the documentation from RHEL:
User can also specify encryption key type (passphrase, openssl), cipher (aes, des3_ede…) key byte size, and other options with above commands.
Example is given below:
# mount -t ecryptfs /home /home -o ecryptfs_unlink_sigs \
ecryptfs_key_bytes=16 ecryptfs_cipher=aes ecryptfs_sig=c7fed37c0a341e19
<strong>
Centos 7 and RHEL 7, by default doesn’t come with ecrpytfs therefore, users can also use encfs command.
For more information on encfs, please refer following link:
https://wiki.archlinux.org/index.php/EncFS
Following are the steps to use encfs to encrypt the data directory.
- Create a data directory using the following command, as enterprisedb user.
mkdir /var/lib/ppas/9.4/encrypted_data
chmod 700 /var/lib/ppas/9.4/encrypted_data
- Use following encfs command to encrypt the data directory.
encfs /var/lib/ppas-9.4/encrypted_data/ /var/lib/ppas-9.4/data
Snapshot of above command is given below:
encfs /var/lib/ppas/9.4/encrypted_data /var/lib/ppas/9.4/data
The directory "/var/lib/ppas/9.4/data" does not exist. Should it be created? (y,n) y</pre>
Creating new encrypted volume.
Please choose from one of the following options:
enter "x" for expert configuration mode,
enter "p" for pre-configured paranoia mode,
anything else, or an empty line will select standard mode.
?>
Standard configuration selected.
Configuration finished. The filesystem to be created has
the following properties:
Filesystem cipher: "ssl/aes", version 3:0:2
Filename encoding: "nameio/block", version 4:0:2
Key Size: 192 bits
Block Size: 1024 bytes
Each file contains 8 byte header with unique IV data.
Filenames encoded using IV chaining mode.
File holes passed through to ciphertext.
Now you will need to enter a password for your filesystem.
You will need to remember this password, as there is absolutely
no recovery mechanism. However, the password can be changed
later using encfsctl.
New Encfs Password:
Verify Encfs Password:
- After encrypting, data directory, users also need to modify the postgresql-<version> service script to include proper command in it for password. For that either, they can use sshpass or they can write their own program which can pass the password for mounting directory.
As you can see, achieving Transparent Data Encryption Postgres is very easy.