pgcryptokey

Name	Last modified	Size

Parent Directory		-
pgcryptokey-0.74.tar.gz	2019-02-21 12:03	6.9K
pgcryptokey-0.75.tar.gz	2019-02-22 17:22	6.9K
pgcryptokey-0.80.tar.gz	2019-08-29 16:00	7.2K
pgcryptokey-0.81.tar.gz	2019-08-29 21:20	7.4K
pgcryptokey-0.82.tar.gz	2019-08-29 22:13	7.5K
pgcryptokey-0.83.tar.gz	2019-08-31 14:12	7.6K
pgcryptokey-0.84.tar.gz	2020-03-20 11:39	7.6K
pgcryptokey-0.85.tar.gz	2020-03-20 12:05	7.6K

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.