使用libsodium的PostgreSQL现代密码学。.zip

[](https://github.com/michelp/pgsodium/actions/workflows/test.yml) # pgsodium pgsodium is an encryption library extension for [PostgreSQL](https://www.postgresql.org/) using the [libsodium](https://download.libsodium.org/doc/) library for high level cryptographic algorithms. pgsodium can be used a straight interface to libsodium, but it can also use a powerful feature called [Server Key Management](#server-key-management) where pgsodium loads an external secret key into memory that is never accessible to SQL. This inaccessible root key can then be used to derive sub-keys and keypairs *by key id*. This id (type `bigint`) can then be stored *instead of the derived key*. Another advanced feature of pgsodium is [Transparent Column Encryption](#transparent-column-encryption) which can automatically encrypt and decrypt one or more columns of data in a table. # Table of Contents * [pgsodium](#pgsodium) * [Installation](#installation) * [Usage](#usage) * [Server Key Management](#server-key-management) * [Server Key Derivation](#server-key-derivation) * [Key Management API](#key-management-table) * [Security Roles](#security-roles) * [Transparent Column Encryption](#transparent-column-encryption) * [Simple public key encryption with crypto_box()](#simple-public-key-encryption-with-crypto_box) * [Avoid secret logging](#avoid-secret-logging) * [API Reference](#api-reference) * [Generating Random Data](#generating-random-data) * [Secret key cryptography](#secret-key-cryptography) * [Authenticated encryption](#authenticated-encryption) * [Authentication](#authentication) * [Public key cryptography](#public-key-cryptography) * [Authenticated encryption](#authenticated-encryption-1) * [Public key signatures](#public-key-signatures) * [Sealed boxes](#sealed-boxes) * [Hashing](#hashing) * [Password hashing](#password-hashing) * [Key Derivation](#key-derivation) * [Key Exchange](#key-exchange) * [HMAC512](#hmac512) * [Advanced Stream API](#advanced-stream-api) * [XChaCha20-SIV](#xchacha20-siv) * [Signcryption](#signcryption) ## Installation pgsodium requires libsodium >= 1.0.18. In addition to the libsodium 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`. 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. 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 [`encode() and decode()` and `convert_to()/convert_from()`](https://www.postgresql.org/docs/current/functions-binarystring.html) binary string functions can convert from `text` to `bytea`. Simple ascii `text` strings without escape or unicode characters will be cast by the database implicitly, and this is how it is done in the tests to save time, but you should really be explicitly converting your `text` content if you wish to use pgsodium without conversion errors. Most of the libsodium API is available as SQL functions. Keys that are generated in pairs are returned as a record type, for example: ``` postgres=# SELECT * FROM crypto_box_new_keypair(); public | secret --------------------------------------------------------------------+-------------------------------------------------------------------- \xa55f5d40b814ae4a5c7e170cd6dc0493305e3872290741d3be24a1b2f508ab31 | \x4a0d2036e4829b2da172fea575a568a74a9740e86a7fc4195fe34c6dcac99976 (1 row) ``` pgsodium is careful to use memory cleanup callbacks to zero out all allocated memory used when freed. In general it is a bad idea to store secrets in the database itself, although this can be done carefully it has a higher risk. To help with this problem, pgsodium has an optional Server Key Management function that can load a hidden server key at boot that other keys are *derived* from. # Server Key Management If you add pgsodium to your [`shared_preload_libraries`](https://www.postgresql.org/docs/current/runtime-config-client.html#RUNTIME-CONFIG-CLIENT-PRELOAD) configuration and place a special script in your postgres shared extension directory, the server can preload a libsodium key on server start. **This root secret key cannot be accessed from SQL**. The only way to use the server secret key is to derive other keys from it using `derive_key()` or use the key_id variants of the API that take key ids and contexts instead of raw `bytea` keys. Server managed keys are completely optional, pgsodium can still be used without putting it in `shared_preload_libraries`, but you will need to provide your own key management. Skip ahead to the API usage section if you choose not to use server managed keys. See the file [`getkey_scripts/pgsodium_getkey_urandom.sh`](getkey_scripts/pgsodium_getkey_urandom.sh) for an example script that returns a libsodium key using the linux `/dev/urandom` CSPRNG. pgsodium also comes with example scripts for: - [Amazon Web Service's Key Management Service](getkey_scripts/pgsodium_getkey_aws.sh). - [Google Cloud's Cloud Key Management](getkey_scripts/pgsodium_getkey_gcp.sh). - [Doppler SecretOps Platform](getkey_scripts/pgsodium_getkey_doppler.sh). - [Zymbit Zymkey 4i Hardware Security Module](getkey_scripts/pgsodium_getkey_zmk.sh). Next place `pgsodium` in your `shared_preload_libraries`. For docker containers, you can append this after the run: docker run -d ... -c 'shared_preload_libraries=pgsodium' 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 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 with `select encode(randombytes_buf(32), 'hex')`. Other common 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