Skip to content

Commit

Permalink
[WIP] Support external HMAC keys by ID and View specification with ta…
Browse files Browse the repository at this point in the history 
…ble SECURITY LABEL (#30)

* tables can be labeled with alternate views, bytea column support.

* HMAC can use encrypted external keys.

* cleanup valid_key view

* key tests.

* remove comment.

* cleanup leftover extschema

* support all the symmetric aead, auth, secret and hash method for uuids.

* multi-column associated data

* some more key tests.

* oops bad test.

* user data jsonb to associated data text, more docs, cleanups.

* readme.

* new doc framework.

* Tweak from github dev

* reindent all C code.

* rendered markdown files for cross checking, notebook and indent scripts.
  • Loading branch information
@michelp
michelp authored Aug 31, 2022
1 parent 401217e commit f2b4ed2
Show file tree
Hide file tree
Showing 78 changed files with 4,885 additions and 2,424 deletions.
4 changes: 2 additions & 2 deletions META.json
View file
Original file line number Diff line number Diff line change
Expand Up @@ -2,7 +2,7 @@
"name": "pgsodium",
"abstract": "Postgres extension for libsodium functions",
"description": "pgsodium is a PostgreSQL extension that exposes modern libsodium based cryptographic functions to SQL.",
"version": "3.0.4",
"version": "3.0.5",
"maintainer": [
"Michel Pelletier <pelletier.michel@gmail.com>"
],
Expand All @@ -13,7 +13,7 @@
"abstract": "Postgres extension for libsodium functions",
"file": "src/pgsodium.h",
"docfile": "README.md",
"version": "3.0.4"
"version": "3.0.5"
}
},
"prereqs": {
Expand Down
162 changes: 117 additions & 45 deletions README.md
View file
Original file line number Diff line number Diff line change
Expand Up @@ -45,7 +45,7 @@ encrypt and decrypt one or more columns of data in a table.
* [Key Derivation](#key-derivation)
* [Key Exchange](#key-exchange)
* [HMAC512](#hmac512)
* [Advanced Stream API](#stream)
* [Advanced Stream API](#advanced-stream-api)
* [XChaCha20-SIV](#xchacha20-siv)
* [Signcryption](#signcryption)

Expand All @@ -56,17 +56,35 @@ library and it's development headers, you may also need the PostgreSQL
header files typically in the '-dev' packages to build the extension.

After installing the dependencies, clone the repo and run `sudo make
install`.
install`. You can also install pgsodium through the pgxn extension
network with `pgxn install pgsodium`.

pgTAP tests can be run with `sudo -u postgres pg_prove test.sql` or
they can be run in a self-contained Docker image. Run `./test.sh` if
you have docker installed to run all tests. Note that this will run
the tests against and download docker images for five different major
versions of PostgreSQL (10, 11, 12, 13, 14), so it takes a while and
requires a lot of network bandwidth the first time you run it.
you have docker installed to run all tests.

As of version 3.0.0 pgsodium requires PostgreSQL 14+. Use pgsodium
2.0.* for earlier versions of Postgres. Once you have the extension
correctly compiled you can install it into your database using the
SQL:

```
CREATE EXTENSION pgsodium;
```

Note that pgsodium is very careful about the risk of `search_path`
hacking and must go into a database schema named `pgsodium`. The
above command will automatically create that schema. You are
encouraged to always reference pgsodium functions by their fully
qualified names, or by making sure that the `pgsodium` schema is first
in your `search_path`.

# Usage

Without using the optional [Server Managed
Keys](#server-key-management) feature pgsodium is a simple and
straightforward interface to the libsodium API.

pgsodium arguments and return values for content and keys are of type
`bytea`. If you wish to use `text` or `varchar` values for general
content, you must make sure they are encoded correctly. The
Expand Down Expand Up @@ -137,11 +155,11 @@ containers, you can append this after the run:

When the server starts, it will load the secret key into memory, but
this key is *never* accessible to SQL. It's possible that a
sufficiently clever malicious superuser can access the key by
invoking external programs, causing core dumps, looking in swap space,
or other attack paths beyond the scope of pgsodium. Databases that
work with encryption and keys should be extra cautious and use as many
protection mitigations as possible.
sufficiently clever malicious superuser can access the key by invoking
external programs, causing core dumps, looking in swap space, or other
attack paths beyond the scope of pgsodium. Databases that work with
encryption and keys should be extra cautious and use as many process
hardening mitigations as possible.

It is up to you to edit the get key script to get or generate the key
however you want. pgsodium can be used to generate a new random key
Expand All @@ -150,6 +168,12 @@ patterns including prompting for the key on boot, fetching it from an
ssh server or managed cloud secret system, or using a command line
tool to get it from a hardware security module.

You can specify the location of the get key script with a database
configuration variable in either `postgresql.conf` or using `ALTER
SYSTEM`:

pgsodium.getkey_script = 'path_to_script'

# Server Key Derivation

New keys are derived from the primary server secret key by id and an
Expand Down Expand Up @@ -237,7 +261,7 @@ Transparent Column Encryption require it.
To create a new key, call the `pgsodium.create_key()` function:

```
# select * from pgsodium.create_key('This is an optional comment');
# select * from pgsodium.create_key();
-[ RECORD 1 ]-------------------------------------
id | 74d97ba2-f9e3-4a64-a032-8427cd6bd686
status | valid
Expand All @@ -251,34 +275,57 @@ user_data |
```

This key can now be used for [Transparent Column
`pgsodium.create_key()` takes the following arguments, all of them are
optional:

- `key_type pgsodium.key_type = 'aead-det'`: The type of key to
create.If you do not specify a `raw_key` argument, a new derived
key_id of the correct type will be automatically generated in
`key_context` argument context. Possible values are:
- `aead-det`
- `aead-ietf`
- `hmacsha512`
- `hmacsha256`
- `auth`
- `shorthash`
- `generichash`
- `kdf`
- `generichash`
- `kdf`
- `secretbox`
- `secretstream`
- `name text = null`: The optional unique name of the key.
- `raw_key bytea = null`: A raw key to store encrypted, if not
specified, the raw key is derived from `key_id` and `key_context`.
- `raw_key_nonce bytea = null`: The nonce used to encrypt the raw
key with, if not specified a new random nonce will be generated.
- `key_context bytea = 'pgsodium'`: The libsodium context to use
for derivation if `key_id` is not null.
- `parent_key uuid = null`: The parent key use to encrypt the raw
key. If not specified, a new unnamed key is created.
- `expires timestamptz = null`: The expiration time checked by the
`pgsodium.valid_key` view.
- `associated_data text = ''`: Extra user data you can associate
with the encrypted raw key. This data is appended to the key
UUID, and mixed into the encryption signature and can be
authenticated with it.

Keys of the type `aead-det` can be used for [Transparent Column
Encryption](#transparent-column-encryption). The view
`pgsodium.valid_keys` filters the key table for only keys that are
valid and not expired.

# Security Roles

The pgsodium API has three nested layers of security roles:

- `pgsodium_keyiduser` Is the least privileged role, it cannot
create or use raw `bytea` keys, it can only create
`crypto_secretkey` nonces and access the `crypto_secretkey`,
`crypto_auth` and `crypto_aead` API functions that accept key ids
only. This role can also access the `randombytes` API. This is
the role you would typically give to a user facing application.
The pgsodium API has two nested layers of security roles:

- `pgsodium_keyholder` Is the next more privileged layer, it can do
everything `pgsodium_keyiduser` can do, but it can also use, but
not create, raw `bytea` encryption keys. This role can use public
key APIs like `crypto_box` and `crypto_sign`, but it cannot create
keypairs. This role is useful for when keys come from external
sources and must be passed as `bytea` to API functions.
- `pgsodium_keyiduser` Is the less privileged role that can only
access keys by their UUID. This is the role you would typically
give to a user facing application.

- `pgsodium_keymaker` is the most privileged role, it can do
everything the previous roles can do, but it can also create keys,
keypairs and key seeds and derive keys from key ids. Be very
careful how you grant access to this role, as it can create valid
secret keys derived from the root key.
- `pgsodium_keymaker` is the more privileged role and can work with
raw `bytea` And managed server keys. You would not typically give
this role to a user facing application.

Note that public key apis like `crypto_box` and `crypto_sign` do not
have "key id" variants, because they work with a combination of four
Expand Down Expand Up @@ -309,17 +356,17 @@ UUIDs for use with the internal encryption functions used by the TCE
functionality. Creating a key to use is the first step:

```
# select * from pgsodium.create_key('Optional Comment');
# select * from pgsodium.create_key();
-[ RECORD 1 ]-------------------------------------
id | dfc44293-fa78-4a1a-9ef9-7e600e63e101
status | valid
created | 2022-08-03 18:50:53.355099
expires |
key_type | aead-det
key_id | 5
key_context | \x7067736f6469756d
comment | Optional Comment
user_data |
id | dfc44293-fa78-4a1a-9ef9-7e600e63e101
status | valid
created | 2022-08-03 18:50:53.355099
expires |
key_type | aead-det
key_id | 5
key_context | \x7067736f6469756d
comment |
associated_data |
```

This key is now stored in the `pgsodium.key` table, and can be
Expand Down Expand Up @@ -371,7 +418,7 @@ CREATE TABLE private.users (

SECURITY LABEL FOR pgsodium
ON COLUMN private.users.secret
IS 'ENCRYPT WITH KEY COLUMN key_id NONCE COLUMN nonce';
IS 'ENCRYPT WITH KEY COLUMN key_id;
```
This approach ensures that “cracking” the key for one row does not
Expand All @@ -398,14 +445,39 @@ CREATE TABLE private.users (
id bigserial primary key,
secret text,
key_id uuid not null,
nonce bytea
nonce bytea
);
SECURITY LABEL FOR pgsodium
ON COLUMN private.users.secret
IS 'ENCRYPT WITH KEY COLUMN key_id NONCE nonce';
```
## One Key ID per Row with Nonce Support and Associated Data
The `aead-det` algorithm can mix user provided data into the
authentication signature for the encrypted secret. This
"authenticates" the plaintext and ensures that it has not been altered
(or the decryption will fail). This is useful for associated useful
metadata with your secrets:
```sql
CREATE TABLE private.users (
id bigserial primary key,
secret text,
key_id uuid not null,
nonce bytea,
associated_data text
);
SECURITY LABEL FOR pgsodium
ON COLUMN private.users.secret
IS 'ENCRYPT WITH KEY COLUMN key_id NONCE COLUMN nonce';
IS 'ENCRYPT WITH KEY COLUMN key_id NONCE nonce ASSOCIATED (id, associated_data)';
```
You can specify multiple columns as shown above with both the id and
associated data column. Columns used for associated data must be
*deterministicly* castable to `text`.
# Simple public key encryption with `crypto_box()`
Here's an example usage from the test.sql that uses command-line
Expand Down
25 changes: 25 additions & 0 deletions doc-notebook.sh
View file
Original file line number Diff line number Diff line change
@@ -0,0 +1,25 @@
version=$1
shift

DB_HOST="pgsodium-doc-db-$version"
DB_NAME="postgres"
SU="postgres"
EXEC="docker exec $DB_HOST"
TAG="pgsodium/test-$version"

echo building test image $DB_HOST
docker build -f docs/Dockerfile . -t $TAG --build-arg "version=$version"

echo running test container
docker run --rm -d -p 8888:8888 --net=host -v `pwd`:/pgsodium -e POSTGRES_HOST_AUTH_METHOD=trust --name "$DB_HOST" $TAG -c 'shared_preload_libraries=pgsodium'

echo waiting for database to accept connections
until
$EXEC \
psql -o /dev/null -t -q -U "$SU" \
-c 'select pg_sleep(1)' \
2>/dev/null;
do sleep 1;
done

docker exec -u postgres -e NB_UID=$(id -u) -e NB_GID=$(id -g) -it $DB_HOST jupyter-lab
33 changes: 33 additions & 0 deletions docs/Authenticated_Encryption_With_Additional_Data.ipynb
View file
Original file line number Diff line number Diff line change
@@ -0,0 +1,33 @@
{
"cells": [
{
"cell_type": "code",
"execution_count": null,
"id": "bronze-design",
"metadata": {},
"outputs": [],
"source": []
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 3",
"language": "python",
"name": "python3"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 3
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython3",
"version": "3.9.2"
}
},
"nbformat": 4,
"nbformat_minor": 5
}
33 changes: 33 additions & 0 deletions docs/Configuration.ipynb
View file
Original file line number Diff line number Diff line change
@@ -0,0 +1,33 @@
{
"cells": [
{
"cell_type": "code",
"execution_count": null,
"id": "revolutionary-delicious",
"metadata": {},
"outputs": [],
"source": []
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 3",
"language": "python",
"name": "python3"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 3
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython3",
"version": "3.9.2"
}
},
"nbformat": 4,
"nbformat_minor": 5
}
19 changes: 19 additions & 0 deletions docs/Dockerfile
View file
Original file line number Diff line number Diff line change
@@ -0,0 +1,19 @@
ARG version
FROM postgres:${version}
ARG version

RUN apt-get update && apt-get install -y make git postgresql-server-dev-${version} curl build-essential libreadline-dev pgxnclient python3-pip
RUN curl -s -L https://download.libsodium.org/libsodium/releases/libsodium-1.0.18.tar.gz | tar zxvf - && cd libsodium-1.0.18 && ./configure && make check && make install

RUN pip3 install ipython-sql sqlalchemy psycopg2 pgspecial ipykernel jupyterlab

RUN mkdir "/pgsodium"
WORKDIR "/pgsodium"
COPY . .
RUN make && make install
RUN ldconfig
RUN cd `pg_config --sharedir`/extension/
RUN cp getkey_scripts/pgsodium_getkey_urandom.sh `pg_config --sharedir`/extension/pgsodium_getkey
RUN sed -i 's/exit//g' `pg_config --sharedir`/extension/pgsodium_getkey
RUN chmod +x `pg_config --sharedir`/extension/pgsodium_getkey
RUN chown -R postgres:postgres /pgsodium
Loading

0 comments on commit f2b4ed2

Please sign in to comment.