pgsodium: Encryption Features

pgsodium is a PostgreSQL extension which provides SQL access to libsodium's high-level cryptographic algorithms.

libsodium is a modern, easy-to-use software library for encryption, decryption, signatures, password hashing, and more. It is a portable, cross-compilable, installable, and packageable fork of the NaCl library, with a compatible but extended API to improve usability even further.

The design choices emphasize security and ease of use. But despite the emphasis on high security, primitives are faster across-the-board than most implementations.

pgsodium exposes the following libsodium APIs to SQL:

• Generating Random Data
• Secret key cryptography
  • Authenticated encryption
  • Authentication
• Public key cryptography
  • Authenticated encryption
  • Public key signatures
  • Sealed boxes
• Hashing
• Password hashing
• Key Derivation
• Key Exchange
• HMAC512
• Advanced Stream API
• XChaCha20-SIV
• Signcryption

It also enables some Postgres specific features including:

• Server Key Management

• Transparent Column Encryption

Note that column encryption should only be used in highly sensitive scenarios as it has a meaningful impact on statement performance and flexibility.

Specifically:

• Encryption and decryption both take time. Inserting and selecting encrypted data takes more time than a "plain" column of data.

• Encrypted columns should never be indexed. This is because the index will store the encrypted value of a column, which would not be useful.

• Encrypted columns can be queried in a WHERE clause, but this can also have some negative performance consequences, since the value must be decrypted in order to matched to any WHERE qualifiers.

• While you can encrypt multiple columns in the same table, each column must go through a full encryption cycle, so two columns will take twice the time as one etc.

In general, it is a bad idea to over-use column encryption for mundane data or data that you need to search against such as names, user or account types, addresses, country codes, etc. Column encryption is intended to be used for very sensitive data that would cause serious issues if it were to leak, such as API keys, payment keys, highly sensitive personal information, etc.

Note that Supabase projects are already encrypted at rest by default.

Get the Root Encryption Key for your Supabase Project#

Encryption requires keys. Keeping the keys in the same database as the encrypted data would be unsafe. For more information about managing the pgsodium root encryption key on your Supabase project see encryption key location. This key is required to decrypt values stored in Supabase Vault and data encrypted with Transparent Column Encryption.

Enable the extension#

Resources#

• Supabase Vault
• Read more about Supabase Vault in the blog post
• Supabase Vault on GitHub

Resources#

• Official pgsodium documentation