Frequent news of data breaches affecting sensitive data of millions of customers have made the public, as well as governments and regulators, strictly legislate how organizations have the responsibility to protect their customers' data.
Damages caused by data breaches include not only reputation erosion of the breached organization, but also result in additional costs in repairing / patching up their systems and hardening their databases.
Protecting customer data
After one of the many recent data breaches, I had a chat with some friends about how to better secure the data they hold for customers to prevent it from happening to them.
We discussed what would happen if someone managed to do some sort of SQL injection attack which gave the attacker access to the production database. We then talked about PostgreSQL prepared statements and how this could help defend against SQL injection attacks, and the conversation continued.
At this point, we decided to play with the worst-case scenario, in which an attacker somehow managed to gain access to our database as a valid user, and retrieved user passwords stored in the system.
One-way encryption using pgcrypto
The solution to this problem would be to use pgcrypto, one of the standard PostgreSQL extensions. Of course, if an attacker had managed to get access to your server in the real world then there would be bigger problems that would need resolving quickly.
The pgcrypto module is a cryptographic extension that provides a number of hashing and cryptographic functions using MD5, SHA, HMAC, AES, BLOWFISH, PGP, and CRYPT, as well as DES and 3DES if your community version of PostgreSQL was compiled with OpenSSL support — as is the case with Fujitsu Enterprise Postgres.
So, back to the password question
All you need to do to store a secure password in your database is to hash it before you store it.
To hash a password you would first append a random string (also know an as salt) to that password, encrypt the whole string, and then store the result.
The salt can be stored in the database as is, without encrypting. This may seem like a weird idea, but for someone trying to decrypt your password data, it means that each and every password has to be cracked individually, and each cracking attempt has to be started from scratch. Being able to obtain the salt doesn’t help the attacker at all, be it with a single password or the whole set of passwords.
Password encryption using PostgreSQL and pgcrypto
For this example, pgcrypto helps you keep the encryption method, the salt, and the encrypted password in one string, to make it easier to work with. You would normally do most of the following steps within your application code, or you could easily do this with a PL/pgSQL or similar function if you wanted to.
The example below encrypts the password "mypass", adding a salt to the hashing, with 4 rounds of blowfish — the returned string is marked in red below. (In a production environment you would probably want 8 or more rounds of blowfish.)
CREATE EXTENSION pgcrypto;
SELECT crypt('mypass', gen_salt('bf', 4));
crypt
--------------------------------------------------------------
$2a$04$1bfMQDOR6aLyD4q3KVb8/ujG7ZAkyie4d/s3ABwuZNbzkFFgXtC76
We can now execute the statement below to store the string safely in the database:
To check if the password is correct, run the query below — specifying "mypass" returns a result row, indicating that it is correct.
user_id | encpass
--------+--------------------------------------------------------------
1 | $2a$04$1bfMQDOR6aLyD4q3KVb8/ujG7ZAkyie4d/s3ABwuZNbzkFFgXtC76
(1 row)
But specifying "hacker" does not return any rows, indicating that the value is incorrect.
user_id | encpass
--------+---------
(0 rows)
And that’s it, you can now store strongly encrypted passwords in your PostgreSQL database and later validate them.
This is a one-way process — what about data that needs to be retrieved later?
While the solution described here is great to secure passwords from data breaches, it is not feasible to store large quantities of data or data that needs to be listed. For those, transparent data encryption and data masking can be used to secure your data.
Transparent data encryption
As we discussed last week (see "Providing maximum data security with minimal impact to your business"), you can use TDE to safeguard your data using PCI DDS-compliant 256-bit encryption at all times.
For further reading, see also our blog post "Why Encrypted Tablespaces Matter".
Data masking
With Fujitsu Enterprise Postgres, you can use data masking to make sure that only appropriate users/roles can view sensitive data.
If you are interested in learning more about Transparent Data Encryption or Data Masking, and how they may improve your PostgreSQL database, please contact us directly. Fujitsu provides 24/7 PostgreSQL support and services, DBA and developer training, and our own enhanced version of PostgreSQL — Fujitsu Enterprise Postgres.
