pgcryptokey - cryptographic key management extension

OVERVIEW
--------
pgcryptokey allows the creation, selection, rotation, and
deletion of cryptographic data keys.  Each cryptographic data
key is encrypted/decrypted with (i.e., wrapped inside) an access
password. Accessing a cryptographic data key requires the proper access
password, as illustrated below:

		+------------------------+
		|                        |
		|    access password     |
		|                        |
		|  +------------------+  |
		|  |encrypted_data_key|  |
		|  +------------------+  |
		|                        |
		+------------------------+

There are two ways to set the access password:

* It can be set at boot time, so all sessions can access any cryptographic
data keys that require the boot-supplied access password

* It can be set by clients, so security can be controlled at the session
level

Data encryption/decryption keys are stored in the table pgcryptokey and
unlocked via access passwords.  This table is automatically created by
the extension:

	CREATE TABLE pgcryptokey (
	        key_id SERIAL PRIMARY KEY,
	        name TEXT DEFAULT 'main',
	        encrypted_data_key BYTEA NOT NULL,
	        created TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL,
	        superseded_by INTEGER
	);

INSTALLATION
------------
To use pgcryptokey, you must install the extension with "CASCADE" to also
install the pgcrypto extension, e.g.:

	CREATE EXTENSION pgcryptokey CASCADE;

ACCESS PASSWORD
---------------
The server variable pgcryptokey.access_password is used by all pgcryptokey
functions as the access password.  This variable can be set at database
server start or by SQL clients.  Only one mode can be used while the
server is running.

Boot
~~~~
To set the access password at server start, set the postgresql.conf
variable 'shared_preload_libraries' to 'pgcryptokey_acpass', copy
the shell script SHAREDIR/extension/pgcryptokey_acpass.sample to
PGDATA/pgcryptokey_acpass, set its execution permission, and restart
the database server.  When pgcryptokey.access_password is set at server
start, the value is read-only.  All users can view a boot-time-set
pgcryptokey.access_password value, though they need access to the
pgcryptokey table to make use of it.

In boot mode, the executable gets a string by prompting the terminal,
but this can be modified to use a key management server, cryptographic
hardware, or ssh to access another computer.  It is insecure to store
the access password in the executable.  When prompting the terminal or
using ssh, the typed password is SHA-256-hashed before storing it in the
pgcryptokey.access_password server variable.

Client
~~~~~~
To set the access password from the client, call this function:

	get_shared_key() RETURNS TEXT

to get a client/server shared secret.  (For security reasons, this is
only supported for SSL and Unix-domain socket connections.)  Then,
encrypt your access password with the shared key with:

	set_session_access_password(encrypted_password TEXT) RETURNS BOOLEAN

'encrypted_password' must be hex-encoded.  Here is an example of its
usage:

	SELECT get_shared_key()
	\gset
	\set enc_access_password `echo 'my secret' | tr -d '\n' | openssl dgst -sha256 -binary | gpg2 --symmetric --batch --cipher-algo AES128 --passphrase :'get_shared_key' | xxd -plain | tr -d '\n'`
	SELECT set_session_access_password(:'enc_access_password');

See sql/pgcrypto.sql for more complete examples.

CHANGING THE ACCESS PASSWORD
----------------------------
To change the access password, use these functions:

	change_key_access_password(name TEXT, new_encrypted_password TEXT) RETURNS BOOLEAN
	change_key_access_password(key_id INTEGER, new_encrypted_password TEXT) RETURNS BOOLEAN

These functions will use the session access password as the existing key
password.  You will need to supply the new access passwords using the
shared key as outlined above.

KEY CREATION
------------
To create a cryptographic key, call the function:

	create_cryptokey(name TEXT, byte_len INTEGER) RETURNS INTEGER

The length of the cryptographic data key is specified in bytes, e.g.,
16 bytes is 128 bits.

KEY ACCESS
----------
To set the default cryptographic data key for future operations, use:

	set_cryptokey(name TEXT) RETURNS BOOLEAN
	set_cryptokey(key_id INTEGER) RETURNS BOOLEAN

This sets server variables 'pgcryptokey.name', 'pgcryptokey.key_id', and
'pgcryptokey.key'.

As with all functions below, specifying the name only affects
active/non-superseded keys.  To affect superseded keys, specify the key_id.

These functions return the cryptographic data key directly as TEXT:

	get_cryptokey(name TEXT) RETURNS TEXT
	get_cryptokey(key_id INTEGER) RETURNS TEXT

KEY ROTATION
------------
pgcryptokey allows for cryptographic data key rotation using these
functions:

	supersede_cryptokey(name TEXT, byte_len INTEGER) RETURNS INTEGER
	supersede_cryptokey(key_id INTEGER, byte_len INTEGER) RETURNS INTEGER

The old and new keys will use the same access password;  this can be
changed after key rotation using change_cryptokey_password().

REENCRYPTION
------------
These functions convert data values from one cryptographic data key
to another:

	reencrypt_data(data BYTEA, old_key_id INTEGER, new_key_id INTEGER) RETURNS BYTEA
	reencrypt_data_bytea(data BYTEA, old_key_id INTEGER, new_key_id INTEGER) RETURNS BYTEA

KEY DESTRUCTION
---------------
These functions remove cryptographic keys:

	drop_cryptokey(name TEXT) RETURNS BOOLEAN
	drop_cryptokey(key_id INTEGER) RETURNS BOOLEAN

EXAMPLE
-------
Here is an example of the use of this extension:

	SELECT create_cryptokey('test', 16);

	CREATE TEMPORARY TABLE cryptokey_sample (data BYTEA);

	INSERT INTO cryptokey_sample VALUES (pgp_sym_encrypt('my data',
	                                     get_cryptokey('test')));
	
	-- use a server variable for the data key
	SELECT set_cryptokey('test');

	SELECT pgp_sym_decrypt(data, current_setting('pgcryptokey.key'))
	FROM cryptokey_sample;
	
	SELECT drop_cryptokey('test');

See sql/pgcrypto.sql for more examples.

FUTURE CONSIDERATIONS
---------------------
When the default access password is set at boot time, all users can
view it, though they need access to the pgcryptokey table to make use of
it. Using C variables and C functions would allow function permissions
to control such access.
