Types of Encryption in PostgreSQL

PostgreSQL has different options for encryptions at different levels that fulfil your security requirements encompassing different scenarios and insecure networks.

1. Encrypting Columns in PostgreSQL (Encryption For Specific Columns)
The pgcrypto module provides cryptographic functions for PostgreSQL.
You can apply encryption on a specific column. e.g,;

-- first you need to add extension
-- example DDL
CREATE TABLE test_encrypt(
  value TEXT
INSERT INTO test_encrypt VALUES ('yourvalue');
-- encrypt value
WITH encrypted_data AS (
  SELECT crypt('PasswordToEncrypt0',gen_salt('md5')) as hashed_value
UPDATE test_encrypt SET value = (SELECT hashed_value FROM encrypted_data);
Validate password:
SELECT (value = crypt('PasswordToEncrypt0', value)) AS match FROM test_encrypt;
(1 row)

ForThe /contrib function library pgcrypto allows certain fields to be stored encrypted. When using this encryption client has to use decryption key to decrypt the data on the server and then client can read it.
Cons: Though the dcrypted data and the decryption key is available on the server for a very short duration while the data is decrypted and server-client communication happens but still this is a drawback as data can be intercepted in this duration by any DB administrator or whosoever has full access to the Database.
2. Data Partition Encryption
File system mount can be encrypted in Linux systems using a "loopback device". The entirer file system partition can be encrypted. On FreeBSD, this facility is called GEOM Based Disk Encryption.
Cons: This prevents the data from being read but does not prevent the data when the file system is mounted because when it is mounted, the OS provides an unencrypted view of the data.

3. Password Encryption
The db table stores the password as md5 hash and does not store the original password anywhere on the server, it only shows the string of characters which is visible to the customer as a string.
Cons: The drawback of this md5 encryption is that it translates to fix string which can be decoded easily because the translation is easily available on internet.
To enable this first you need to declare auth-method as md5 in pg_hba.conf as below:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   sameuser        all                                     md5
--You can use the option Encrypted as below to create a use with md5:

4. Encrypting Passwords Across A Network
In MD5 encryption the password is double-encrypted before sending the password to the database server, the first encryption is based on the user name and the second encryption is based the salt key sent by the server.
SSL Host Authentication
By exchanging keys and certificates between client and server we can configure SSL so that no other machine can pretend like a server in the middle of communication to intercept data. This takes both the server and client machines to be configured accordingly. This provides stronger verification of identity than the mere use of passwords.

5. Encrypting Data Across A Network
By exchanging keys and certificates between client and server we can configure SSL so that no other machine can pretend like a server in the middle of communication to intercept data. Following are the steps to configure SSL in PostgreSQL:
a. Buy the certificates from a trusted service provider and copy your signed certificate and private key to any locations on the database server:

[~]# cp server.{key,crt} /var/opt/eraazi/pgsql10/lib/pgsql/data/.
[~]# chown postgres:postgres /var/opt/eraazi/pgsql10/lib/pgsql/data/server.{key,crt}
[~]# chmod 0400 /var/opt/eraazi/pgsql10/lib/pgsql/data/server.key

b. Edit the postgresql.conf file and add the following option:


Also add and edit the values of below parameters in the postgresql.conf file as per the locations of the files you designated in the first step:
ssl_ca_file, ssl_cert_fil, ssl_key_file

c. Edit the pg_hba.conf file. This file is a permissions file for restricting access to the database. Look for a line similar to the following:

host      mydb        mydbuser       yourhostIP/24       md5

In the above entry change the host option to hostssl as below:

hostssl    mydb      mydbuser       yourhostIP/24       md5

This changes the incoming communication protocol to use SSL and refuse any unencrypted PostgreSQL connections.

d. Restart the pgsql10-postgresql service so the changes take effect:

[~]# service pgsql10-postgresql restart

To find the steps to configure ssl in detail please go the link how to configure SSL for PostgreSQL database on CentOS 7/ RHEL 7

6. Client-Side Encryption
Client side encryption can be used to protect data so that the server never has decrypted data which can save the data from the DB administrators as well. In this case the application encrypts the data on client side and then sends data to the server and data is decrypted by application on the client side before use.
Different programming languages have their own method of encryption available viz php has PHP Encryption library


Who is online?

There are currently 0 users online.