add pgsodium extension

[sebastianwebber]

this PR adds the pgsodium extension.

Issue https://github.com/timescale/forge-issues/issues/1152

[sebastianwebber]

it looks like that is working!

Starting the container on 1 terminal:

docker run --rm -e POSTGRES_PASSWORD=fooo -v ./data:/home/postgres/pgdata docker.io/timescale/timescaledb-ha:cicd-3805b0b-amd64

Running in another terminal

add the server script file:

export PGDATA='data/data/'
cat <<EOF > data/data/server_key
#!/bin/bash
KEY_FILE=\$PGDATA/pgsodium_root.key

if [ ! -f "\$KEY_FILE" ]; then
    head -c 32 /dev/urandom | od -A n -t x1 | tr -d ' \n' > \$KEY_FILE
fi
cat \$KEY_FILE
EOF
chmod +x data/data/server_key

Creating the config file in data/data/postgresql.auto.conf:

cat <<EOF > data/data/postgresql.auto.conf
shared_preload_libraries = 'timescaledb,pgaudit,pgsodium'
search_path = '"\$user", public, pgsodium, pgsodium_tasks'
pgsodium.getkey_script = '/home/postgres/pgdata/data/server_key'
EOF

finally, create the extension:

❯ docker exec -it  $(docker ps -q ) psql
psql (16.2 (Ubuntu 16.2-1.pgdg22.04+1))
Type "help" for help.

postgres=#
postgres=# \dx
                                                    List of installed extensions
        Name         | Version |   Schema   |                                      Description
---------------------+---------+------------+---------------------------------------------------------------------------------------
 plpgsql             | 1.0     | pg_catalog | PL/pgSQL procedural language
 timescaledb         | 2.14.2  | public     | Enables scalable inserts and complex queries for time-series data (Community Edition)
 timescaledb_toolkit | 1.18.0  | public     | Library of analytical hyperfunctions, time-series pipelining, and other SQL utilities
(3 rows)

postgres=# create extension pgsodium;
CREATE EXTENSION
postgres=# create extension pgaudit;
CREATE EXTENSION
postgres=# \dx
                                                    List of installed extensions
        Name         | Version |   Schema   |                                      Description
---------------------+---------+------------+---------------------------------------------------------------------------------------
 pgaudit             | 16.0    | public     | provides auditing functionality
 pgsodium            | 3.1.9   | pgsodium   | Pgsodium is a modern cryptography library for Postgres.
 plpgsql             | 1.0     | pg_catalog | PL/pgSQL procedural language
 timescaledb         | 2.14.2  | public     | Enables scalable inserts and complex queries for time-series data (Community Edition)
 timescaledb_toolkit | 1.18.0  | public     | Library of analytical hyperfunctions, time-series pipelining, and other SQL utilities
(5 rows)

Tests:

postgres=# \x
Expanded display is on.
postgres=# select * from crypto_box_seed_new_keypair(derive_key(1));
-[ RECORD 1 ]--------------------------------------------------------------
public | \x50285c31697d055f88686c46508d7f04c6204037de3b8990c14b66f9e94f542c
secret | \xbc97423416bf9379a962d3ff03b56f4f9cab3dfbf798092d58a2cb60a104ef1a

postgres=# select * from pgsodium.create_key();
-[ RECORD 1 ]---+-------------------------------------
id              | 2699be0b-f5da-4bda-891e-044e8d37e685
name            |
status          | valid
key_type        | aead-det
key_id          | 2
key_context     | \x7067736f6469756d
created         | 2024-04-03 03:17:11.171914+00
expires         |
associated_data |