Encrypt Query Language (EQL)

Encrypt Query Language (EQL) is a set of abstractions for transmitting, storing, and interacting with encrypted data and indexes in PostgreSQL.

Tip

New to EQL? EQL is the basis for searchable encryption functionality when using Protect.js and/or CipherStash Proxy.

Store encrypted data alongside your existing data:

• Encrypted data is stored using a jsonb column type
• Query encrypted data with specialized SQL functions (equality, range, full-text, etc.)
• Index encrypted columns to enable searchable encryption

Table of Contents

• Installation
  • dbdev
• Getting started
  • Enable encrypted columns
• Encrypt configuration
• CipherStash integrations using EQL
• Developing

---

Installation

The simplest way to get up and running with EQL is to execute the install SQL file directly in your PostgreSQL database.

1. Download the latest EQL install script:

   curl -sLo cipherstash-encrypt.sql https://github.com/cipherstash/encrypt-query-language/releases/latest/download/cipherstash-encrypt.sql

2. Run this command to install the custom types and functions:

   psql -f cipherstash-encrypt.sql

EQL Components

EQL installs and manages the following components

Name	Entity Type
eql_v2.*	Schema
public.eql_v2_encrypted	Type
public.eql_v2_configuration_state	Type
public.eql_v2_configuration	Table

eql_v2 Schema

The eql_v2 schema holds all of the functions, types and operators required to query and interact with encrypted data. The schema is stateless and the schema can be dropped without risk of data loss.

Updating EQL will drop and re-create the schema. Unless otherwise documented this is a safe operation that requires no data migration or changes.

Configuration Table & Type

The public.eql_v2_configuration table holds the searchable encryption configuration. The public.eql_v2_configuration_state type is used by the configuration table.

The table and associated type are created in the public schema to avoid any risk of data loss when updating or uninstalling EQL.

EQL updates will automatically migrate the configuration if the internal structure changes.

On uninstall the configuration table is renamed with a timestamp suffix The table is not automatically dropped to avoid any potential risk of data loss.

Renaming avoids potential conflicts in CI pipelines that may repeatedly install and uninstall EQL.

public.eql_v2_encrypted Type

The public.eql_v2_encrypted is the type used to define encrypted columns, and is used in customer table definitions. The type is created in the public schema to avoid any risk of data loss when updating or uninstalling EQL.

Dropping the public.eql_v2_encrypted type will remove any associated columns from the database.

Uninstalling EQL will not drop the public.eql_v2_encrypted type to avoid risk of data loss.

Database Permissions

EQL requires specific database privileges to install and operate correctly. The permissions needed depend on your deployment pattern.

Default Permissions (Recommended)

For most use cases, grant the following permissions to the database user that will install and use EQL:

-- Database-level permissions
GRANT CREATE ON DATABASE your_database TO your_eql_user;

-- Schema permissions  
GRANT USAGE ON SCHEMA public TO your_eql_user;
GRANT CREATE ON SCHEMA public TO your_eql_user;

-- Configuration table permissions
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE public.eql_v2_configuration TO your_eql_user;

-- User table permissions (for encrypted column constraints)
GRANT ALTER ON ALL TABLES IN SCHEMA public TO your_eql_user;
-- Or grant ALTER on specific tables that will have encrypted columns:
-- GRANT ALTER ON TABLE your_table TO your_eql_user;

Why these permissions are needed:

• CREATE ON DATABASE: Required to create the eql_v2 schema, types, and functions during installation
• CREATE ON SCHEMA public: Required to create types and tables in the public schema
• Configuration table access: EQL manages searchable encryption configuration in public.eql_v2_configuration
• ALTER on user tables: EQL adds check constraints to encrypted columns for data validation

Splitting Read and Write Access

A common production pattern separates setup/migration permissions from runtime permissions:

Setup/Migration User (Write Access)

Use during database migrations and EQL installation:

-- All default permissions above, plus:
GRANT CREATE ON DATABASE your_database TO your_migration_user;
GRANT CREATE ON SCHEMA public TO your_migration_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE public.eql_v2_configuration TO your_migration_user;
GRANT ALTER ON ALL TABLES IN SCHEMA public TO your_migration_user;

Runtime User (Read Access)

Use for application queries in production:

-- Configuration read access
GRANT SELECT ON TABLE public.eql_v2_configuration TO your_app_user;

-- EQL schema usage
GRANT USAGE ON SCHEMA eql_v2 TO your_app_user;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA eql_v2 TO your_app_user;

-- User table access (normal application permissions)
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE your_tables TO your_app_user;

Migration Workflow:

1. Use the migration user to install EQL and configure encrypted columns
2. Use the runtime user for normal application operations
3. Configuration changes (adding/removing encrypted columns) require the migration user

dbdev

Warning

The version released on dbdev may not be in sync with the version released on GitHub until we automate the publishing process.

You can find the EQL extension on dbdev's extension catalog with instructions on how to install it.

Getting started

Once the custom types and functions are installed in your PostgreSQL database, you can start using EQL in your queries.

Enable encrypted columns

Define encrypted columns using the eql_v2_encrypted type, which stores encrypted data as jsonb with additional constraints to ensure data integrity.

Example:

CREATE TABLE users (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    encrypted_email eql_v2_encrypted
);

Encrypt configuration

In order to enable searchable encryption, you will need to configure your CipherStash integration appropriately.

• If you are using CipherStash Proxy, see this guide.
• If you are using Protect.js, use the Protect.js schema.

CipherStash integrations using EQL

These frameworks use EQL to enable searchable encryption functionality in PostgreSQL.

Framework	Repo
Protect.js	Protect.js
Protect.php	Protect.php
CipherStash Proxy	CipherStash Proxy

Versioning

You can find the version of EQL installed in your database by running the following query:

SELECT eql_v2.version();

Upgrading

To upgrade to the latest version of EQL, you can simply run the install script again.

1. Download the latest EQL install script:

   curl -sLo cipherstash-encrypt.sql https://github.com/cipherstash/encrypt-query-language/releases/latest/download/cipherstash-encrypt.sql

2. Run this command to install the custom types and functions:

   psql -f cipherstash-encrypt.sql

Note

The install script will not remove any existing configurations, so you can safely run it multiple times.

Using dbdev?

Follow the instructions in the dbdev documentation to upgrade the extension to your desired version.

Developing

See the development guide.