By default, PostgreSQL internally encrypts sensitive data, such as roles' passwords. However, database users can also encrypt and decrypt sensitive data using the pgcrypto extension.
When creating a role with password and login options, one can see the role's details in the pg_shadow catalog relation. Note that it is not recommended to use the following format to create the password because the statement can appear in pg_stat_activity or the server logs:
CREATE ROLE <role_name> WITH LOGIN PASWWORD 'role_password';
The passwords in pg_catalog are encrypted with a slat by default, as shown in the following example. Note how passwd for the a and b roles are different even though they have the same password:
CREATE ROLE a WITH LOGIN PASWWORD 'a'; CREATE ROLE b WITH LOGIN PASWWORD 'a'; SELECT usename, passwd FROM pg_shadow WHERE usename IN ('a','b'); usename | passwd ---------+------------------------------------- b | md5187ef4436122d1cc2f40dc2b92f0eba0 a | md54124bc0a9335c27f086f24ba207a4912 (2 rows)
The role password is resent when a role is rested, as follows:
ALTER ROLE a RENAME TO x; NOTICE: MD5 password cleared because of role rename ALTER ROLE
When creating a user with a password, it is recommended to use the password psql meta command because it ensures that the password does not appear in clear text form in the psql history command, server log files, or elsewhere. To change the postgres role password, one needs to log in as postgres and then invoke this command, as follows:
$ psql -h localhost -U postgres postgres=# password Enter new password: Enter it again: postgres=#
The pgcrypto extension provides a cryptographic functionality. Data encryption and decryption consume hardware resources, so it is important to have a balance between data sensitivity and decryption complexity. There are two kinds of data encryption, as follows:
- One-way data encryption uses a function to generate a hash, which cannot be reversed. The resulted encrypted text has often a fixed length; for example, MD5 encryption generates 16 bytes of hashes. A good hash function should be quick to compute and not produce the same hash for a different input.
- Two-way data encryption allows the data to be encrypted and decrypted.
Pgcrypto comes with functions to support one-way and two-way encryption. It supports several hashing algorithms. Pgcrypto can be installed using the CREATE EXTENSION command, as follows:
CREATE EXTENSION pgcrypto;
In one-way encryption, retrieving data in a clear text form is not important. The encrypted text (digest) is used to verify that the user knows the secret text. One-way encryption is often used to store passwords. PostgreSQL supports out-of-the-box MD5 encryption; however, as MD5 can be cracked easily, one could use MD5 with salt, as seen in the preceding pg_shadow example.
The common scenario of validating a password is comparing the generated MD5 digest with the stored one, as follows:
CREATE TABLE account (id INT, password text); INSERT INTO account VALUES (1, md5('my password')); SELECT (md5('my password') = password) AS authenticated FROM account; ---output authenticated --------------- t (1 row)
Pgcrypto provides two functions to encrypt the password. These functions are crypt and gen_salt; also, the pgcrypto extension relieves the user from maintaining the salt. Crypt and gen_salt can be used almost in the same way as MD5 to store the password, as follows:
truncate account; INSERT INTO account VALUES (1, crypt ('my password', gen_salt('md5'))), (2, crypt ('my password', gen_salt('md5'))); SELECT * FROM account; id | password ----+------------------------------------ 1 | $1$XoZUm6GT$AnqHLYDuxQs8qLAQxlc6r/ 2 | $1$dZp2EIKk$v48D/SbgKhCMYABHreIKF1 (2 rows) SELECT crypt('my password', '$1$XoZUm6GT$AnqHLYDuxQs8qLAQxlc6r/') = '$1$XoZUm6GT$AnqHLYDuxQs8qLAQxlc6r/' AS authenticated FROM account WHERE id = 1 ; authenticated --------------- t (1 row)
Note how the password differs due to a different generated salt. Also, note how the salt does not need to be maintained. Also, one could have a stronger password by tuning the salt generation. For example, one could use blowfish hashing and specify the iteration count. Note that the more the iteration counts, the slower the decryption, and more time is subsequently required to break it, as shown in the example:
car_portal=# iming Timing is on. car_portal=# SELECT crypt('my password', gen_salt('bf',4)); crypt -------------------------------------------------------------- $2a$04$GuQUIr.JmnCypsu49CrZseQAsFKzmWcfmfBrfRRU2JJ.agqv1RcEy (1 row) Time: 2.060 ms car_portal=# SELECT crypt('my password', gen_salt('bf',8)); crypt -------------------------------------------------------------- $2a$08$955AL.pjCUScYupcVNIQKeOs6j.uC3v0HOE2c3aezQQhOJi8zMLRK (1 row) Time: 25.478 ms car_portal=# SELECT crypt('my password', gen_salt('bf',12)); crypt -------------------------------------------------------------- $2a$12$ejO0flBvlUv9pJ/7SNneT.DYn6W7oPSKiWUcTkRLLHzTMjjI37Lle (1 row) Time: 420.496 ms car_portal=# SELECT crypt('my password', gen_salt('bf',16)); crypt -------------------------------------------------------------- $2a$16$gWTTGf45/tqmGaEHVmZHD.RX/Vmjrm.3dA4S0Edhu6oo7Ei9gQ6r2 (1 row) Time: 6598.780 ms car_portal=# SELECT crypt('my password', '$2a$16$gWTTGf45/tqmGaEHVmZHD.RX/Vmjrm.3dA4S0Edhu6oo7Ei9gQ6r2'); crypt -------------------------------------------------------------- $2a$16$gWTTGf45/tqmGaEHVmZHD.RX/Vmjrm.3dA4S0Edhu6oo7Ei9gQ6r2 (1 row)
Two-way encryption is used to store sensitive information, such as payment information. Pgcrypto provides two functions—mainly encrypt and decrypt—as shown in the following script:
car_portal=# df encrypt List of functions Schema | Name | Result data type | Argument data types | Type --------+---------+------------------+---------------------+-------- public | encrypt | bytea | bytea, bytea, text | normal (1 row) car_portal=# df decrypt List of functions Schema | Name | Result data type | Argument data types | Type --------+---------+------------------+---------------------+-------- public | decrypt | bytea | bytea, bytea, text | normal (1 row)
The encrypt and decrypt functions require three arguments: the data to encrypt, the key, and the encryption algorithm. The following example shows how to encrypt and decrypt the string Hello world using the aes encryption algorithm:
car_portal=# SELECT encrypt ('Hello World', 'my key', 'aes'); encrypt ------------------------------------ xe3d6d1ddea318dbf88e34421fd095727 (1 row) car_portal=# SELECT decrypt ('xe3d6d1ddea318dbf88e34421fd095727', 'my key' , 'aes'); decrypt -------------------------- x48656c6c6f20576f726c64 (1 row) car_portal=# SELECT convert_from('x48656c6c6f20576f726c64', 'utf-8'); convert_from -------------- Hello World (1 row)
The preceding form of encryption has some limitations; for example, the statement can appear in pg_stat_activity or in the database server log, and thus, one could get the key.
Two-way encryption can be achieved in two ways: symmetric and asymmetric encryption. The preceding example shows how symmetric encryption works, where there is a key used to encrypt and decrypt data. Asymmetric encryption uses public and private keys; the public key is used to encrypt the data, and the private key used to decrypt it. Asymmetric encryption is more secure than symmetric encryption but harder to set up. To set up asymmetric encryption and decryption, one needs to generate a public key via the gpg tool. The first step in generating the key is to execute the following command. Note that the gpg command asks for a passphrase; in this example, the passphrase should not be provided:
gpg --gen-key
Now, to extract the public and private keys, one could execute the following commands:
# gpg --list-secret-key /root/.gnupg/secring.gpg ------------------------ sec 2048R/2C914A6D 2015-06-25 uid Salahaldin Juba <[email protected]> ssb 2048R/56C8FA64 2015-06-25 # gpg -a --export 2C914A6D > /var/lib/postgresql/9.4/main/public.key # gpg -a --export-secret-key 56C8FA64 > /var/lib/postgresql/9.4/main/secret.key# chown postgres:postgres /var/lib/postgresql/9.4/main/public.key #chown postgres:postgres /var/lib/postgresql/9.4/main/secret.key
The gpg option --list-secret-key is used to determine the key IDs, and the options --export-secret-key and --export are used to export the public and the private keys, respectively. The -a option is used to dump the keys in copy and paste formats; however, on the PostgreSQL backend, we need to run the dearmor function. Also, the keys were moved to the database cluster folder for convenience purposes to use the pg_read_file function. Finally, it is recommended to change the keys' ownership to postgres to protect the files on the hard disk.
After the generation of the keys, one could create a wrapper around pgp_pub_encrypt and pgp_pub_decrypt to hide the location of the keys, as follows:
CREATE OR REPLACE FUNCTION encrypt (text) RETURNS bytea AS $$ BEGIN RETURN pgp_pub_encrypt($1, dearmor(pg_read_file('public.key'))); END; $$ Language plpgsql; CREATE OR REPLACE FUNCTION decrypt (bytea) RETURNS text AS $$ BEGIN RETURN pgp_pub_decrypt($1, dearmor(pg_read_file('secret.key'))); END; $$ Language plpgsql; test=# SELECT decrypt(encrypt('hello')); decrypt --------- hello (1 row)