I know there has been recent discussion about implementing transparent
data encryption (TDE) in Postgres:
https://www.postgresql.org/message-id/CAD21AoAqtytk0iH6diCJW24oyJdS4roN-VhrFD53HcNP0s8pzA%40mail.gmail.com
I would like to now post a new extension I developed to handle
cryptographic key management in Postgres. It could be used with TDE,
with pgcrypto, and with an auto-encrypted data type. It is called
pgcryptokey and can be downloaded from:
https://momjian.us/download/pgcryptokey/
I am attaching its README file to this email.
The extension uses two-layer key storage, and stores the key in a
Postgres table. It allows the encryption key to be unlocked by the
client, or at boot time. (This would need to be modified to be a global
table if it was used for block-level encryption like TDE.)
I am willing to continue to develop this extension if there is interest.
Should I put it on PGXN eventually? It is something we would want in
/contrib?
--
Bruce Momjian <br...@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
pgcryptokey - cryptographic key management extension
OVERVIEW
--------
pgcryptokey allows the creation, rotation, selection, and deletion
of cryptographic data keys. Each cryptographic data key is
encrypted/decrypted with (i.e., wrapped inside) a key access password.
Accessing a cryptographic data key requires the proper key access password,
as illustrated below:
+------------------------+
| |
| key access password |
| |
| +------------------+ |
| |encrypted_data_key| |
| +------------------+ |
| |
+------------------------+
pgcryptokey operates in two security modes:
* The key access password is set by clients, so security is at the
session level
* The default key access password is set at boot time, so all sessions
can access cryptographic data keys that require that password
Cryptographic data keys are stored in the pgcryptokey table, which 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;
PASSWORDS
---------
There are three levels of passwords used by pgcryptokey:
1. A password entered by the client user, boot-time administrator, or
other method.
2. A hash of password #1 that is passed from the client to the server,
or set as a server variable at boot time. This is called the "key
access password".
3. A data encryption/decryption key stored in the table pgcryptokey and
unlocked via password #2. This is called the "cryptographic data key"
and is used as the 'password' argument to pgcrypto functions.
DEFAULT PASSWORDS
-----------------
The server variable pgcryptokey.default_password is used as the key
access password (number two above) by all pgcryptkey functions when the
password is not supplied. This variable can be set via SQL or at database
server start.
To set pgcryptokey.default_password at server start, set the
postgresql.conf variable 'shared_preload_libraries' to
'pgcryptokey_default', copy the shell script
SHAREDIR/extension/pgcryptokey_default.sample to
PGDATA/pgcryptokey_default, set its execution permission, and restart
the database server. When pgcryptokey.default_password is set at server
start, the value is read-only.
By default, the executable gets the key access password 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 key access password in the executable. All users
can view a boot-time-set pgcryptokey.default_password value, but they
need access to the pgcryptokey table to make use of it.
When using the default behavior of prompting the terminal, or using ssh,
the typed password is SHA-256-hashed before storing it in the
pgcryptokey.default_password server variable. This behavior can be
simulated at the session level using this SQL command (replace
'MyPassword' with the desired password):
SELECT set_config('pgcryptokey.default_password',
encode(digest('MyPassword', 'sha256'), 'hex'),
false)::VOID;
The SHA-256 hash can also be computed in psql:
\set hashed_password `printf '%s' 'MyPassword' | openssl dgst -sha256
-binary | xxd -p -c 999`
SELECT set_config('pgcryptokey.default_password', :'hashed_password',
false)::VOID;
Clients can use this method to hash user-supplied passwords before
passing them to SQL functions.
KEY CREATION
------------
To create a cryptographic key, call the function:
create_cryptokey(name TEXT, byte_len INTEGER [, password TEXT ])
RETURNS INTEGER
The length of the cryptographic data key is specified in bytes, e.g., 16
bytes is 128 bits. The key access password can optionally be supplied
as part of the function call. If supplied, make sure the function call
is not recorded in any way. If not supplied in the function call, the
key access password will be retrieved from a server variable
pgcryptokey.default_password.
While any password can be supplied during key creation, all future key
access requires the valid key access password; specifying an invalid
key access password generates an error.
KEY ACCESS
----------
To set the default cryptographic data key for future operations, use:
set_cryptokey(key_id INTEGER [, password TEXT ]) RETURNS VOID
set_cryptokey(name TEXT [, password TEXT ]) RETURNS VOID
This sets server variables 'pgcryptokey.key_id', 'pgcryptokey.name', and
'pgcryptokey.key'. Since pgcrypto only allows passwords to be TEXT
values, the hex format of the stored BYTEA value is used as the
cryptographic 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(key_id INTEGER [, password TEXT ]) RETURNS TEXT
get_cryptokey(name TEXT [, password TEXT ]) RETURNS TEXT
PASSWORD CHANGE
---------------
To change the key access password, use these functions:
change_cryptokey_password(key_id INTEGER, old_password TEXT,
new_password TEXT) RETURNS VOID
change_cryptokey_password(name TEXT, old_password TEXT, new_password
TEXT) RETURNS VOID
KEY ROTATION
------------
pgcryptokey allows for cryptographic data key rotation using these functions:
supersede_cryptokey(key_id INTEGER, byte_len INTEGER [, password TEXT
]) RETURNS INTEGER
supersede_cryptokey(name TEXT, byte_len INTEGER [, password TEXT ])
RETURNS INTEGER
These functions convert data values from one cryptographic data key to
another; the old and new keys must use the same key access password:
reencrypt_data(data BYTEA, old_key_id INTEGER, new_key_id INTEGER [,
password TEXT ]) RETURNS BYTEA
reencrypt_data_bytea(data BYTEA, old_key_id INTEGER, new_key_id INTEGER
[, password TEXT ]) RETURNS BYTEA
KEY DESTRUCTION
---------------
These functions remove cryptographic keys:
drop_cryptokey(key_id INTEGER [, password TEXT ]) RETURNS VOID
drop_cryptokey(name TEXT [, password TEXT ]) RETURNS VOID
EXAMPLE
-------
Here is an example of the use of this extension:
SELECT create_cryptokey('test', 16, 'abc');
CREATE TEMPORARY TABLE cryptokey_sample (data BYTEA);
INSERT INTO cryptokey_sample VALUES (pgp_sym_encrypt('my data',
get_cryptokey('test', 'abc')));
-- use a server variable for the data key
SELECT set_cryptokey('test', 'abc');
SELECT pgp_sym_decrypt(data, current_setting('pgcryptokey.key'))
FROM cryptokey_sample;
-- use a server variable for the key access password
SET pgcryptokey.default_password = 'abc';
SELECT drop_cryptokey('test');
The regression tests contain examples of key rotation and changing the
key access password.
TWO PASSWORDS
-------------
It is possible to use a boot-time-set password and a client-supplied
password as the key access password, e.g.:
SELECT create_cryptokey('test', 16, 'abc' ||
current_setting('pgcryptokey.default_password'));
When pgcryptokey.default_password is set at boot-time, it cannot be
changed, so you cannot use the default password feature in this
situation; you must specify the concatenation in each pgcryptokey
function call.
FUTURE CONSIDERATIONS
---------------------
When the default key 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.
