PostgreSQL has various levels of encryption to choose from. In this article we'll go over the basics built-in and the more advanced provided by the contrib module pgcrypto. When encrypting data, as a general rule the harder you make it to keep people out of your data, the easier it is for you to lock yourself out of your data. Not only does encryption make it difficult to read data, it also takes more resources to query and decrypt. With those rules of thumb, its important to pick your encryption strategies based on the sensitivity of your data.
There are two basic kinds of encryption, one way and two way. In one way you don't ever care about decrypting the data into readable form, but you just want to verify the user knows what the underlying secret text is. This is normally used for passwords. In two way encryption, you want the ability to encrypt data as well as allow authorized users to decrypt it into a meaningful form. Data such as credit cards and SSNs would fall in this category.
Normally when people want one way encryption and just want a basic simple level of encryption, they use the md5 function which is built into PostgreSQL by default. The md5 function is equivalent to using the PASSWORD function in MySQL. If you want anything beyond that, you'll want to install the pgcrypto contrib module.
pgcrypto comes packaged with most PostgreSQL installs including windows, and can be installed into a database by running the script in share/contrib/pgcrypto.sql of your PostgreSQL install. For PostgreSQL 8.4+, this adds 34 someodd functions to your list of options. For maintainability we like to install it in a separate schema say crypto, and add this schema to our database search path.
For one way encryption, the crypt function packaged in pgcrypto provides an added level of security above the md5 way. The reason is that with md5, you can tell who has the same password because there is no salt so all people with the same password will have the same encoded md5 string. With crypt, they will be different. To demonstrate lets create a table with two users who have happened to have chosen the same password.
CREATE TABLE testusers(username varchar(100) PRIMARY KEY, cryptpwd text, md5pwd text);
INSERT INTO testusers(username, cryptpwd, md5pwd)
VALUES ('robby', crypt('test', gen_salt('md5')), md5('test')),
('artoo', crypt('test',gen_salt('md5')), md5('test'));
SELECT username, cryptpwd, md5pwd
FROM testusers;
username | cryptpwd | md5pwd
----------+------------------------------------+---------------------------------
robby | $1$IOchfG/z$bZW1pRFA3wuvn6pAuD.Du/ | 098f6bcd4621d373cade4e832627b4f6
artoo | $1$84oZTXI/$yZ6wV5jhJo6aQYrTciMQR/ | 098f6bcd4621d373cade4e832627b4f6
Observe that both users have chosen the same password test. The md5 version is the same for both, but the crypted password is different although they are the same password. When any log in, we do this test.
-- successful login
SELECT username
FROM testusers
WHERE username = 'robby' AND cryptpwd = crypt('test', cryptpwd);
-- successful login
SELECT username
FROM testusers
WHERE username = 'artoo' AND cryptpwd = crypt('test', cryptpwd);
-- unsuccessful login
SELECT username
FROM testusers
WHERE username = 'artoo' AND cryptpwd = crypt('artoo', cryptpwd);
-- using md5
SELECT username
FROM testusers
WHERE username = 'robby' and md5pwd = md5('test');
In the crypt case we use the encrypted password to determine the unencrypted password is the same as the encrypted. Passing in the encrypted password unsaltifies things so to speak. For md5, we don't need any of that and thus its easier to crack since the same password will yield the same md5 code.
For data that you care about retrieving, you don't want to know if the two pieces of information are the same, but you don't know that information, and you want only authorized users to be able to retrieve it. Information like this would be things like credit cards, social security numbers or swiss bank account numbers etc.
One of the most useful and easy to use encryption modes provided in pgcrypto is the PGP encryption functions. For these set of exercises, we'll go thru using PGP encryption to encrypt sensitive database data and also how to decrypt it. There are 2 kinds of PGP encryption you can use.
Take the case of the database with sensitive information, if you are worried about the database falling in the wrong hands, you would probably be better to use the public/private key approach. This ensures you can encrypt the data with a public key you store in the database or even as part of a trigger or even in plain site in an application, but Users who need to be able to read this secure information would need the private key to decrypt it. So a person stealing your database even though they can see the public key, it does them no good at trying to get the information.
Before we can use PGP encryption, we need to make keys. Details below:
If you are on some Linux OS you probably have the command line tool called gpg that you can use already available. If you are on windows, you need to download them from somewhere like this page GNU Pg binaries. Way at the bottom of the page you should find gnupg-w32cli-1.4.10b.exe. Download and install that or you can simply extract the folder instead of installing and run from anywhere. By default it installs in folder C:\Program Files\GNU\GnuPG. You can copy these files anywhere. Really no need for installation.
Next we do the following more or less verbatim from the PostgreSQL pgcrypto docs. These steps will work on Linux/Unix/Mac OSX or windows
gpg --gen-key and follow the directions. Note if you don't need super security, you can just click enter to the password phrase thus one less argument you need to pass when decrypting your data. gpg --list-secret-keys This will provide you a list of keys one being the one you generated.
It will look something like: