No description
Find a file
Cédric Villemain 7eb36393c6 minor wording
2021-04-08 14:46:49 +02:00
expected Fake (NOT) LIKE operators 2021-04-08 12:27:05 +02:00
sql Fake (NOT) LIKE operators 2021-04-08 12:27:05 +02:00
.gitignore update test / ... 2021-04-02 09:41:19 +00:00
COPYRIGHT some more edits 2021-04-01 12:03:28 +02:00
Makefile Fake (NOT) LIKE operators 2021-04-08 12:27:05 +02:00
pg_encrypted_types--0.1.sql Fake (NOT) LIKE operators 2021-04-08 12:27:05 +02:00
pg_encrypted_types.c Change btree cmp func name 2021-04-07 11:06:33 +02:00
pg_encrypted_types.control premiers pas en C 2021-04-01 14:42:02 +00:00
README.pg_encrypted_types.md minor wording 2021-04-08 14:46:49 +02:00

PostgreSQL Transparent Encrypted Data Types

This extension provides encrypted text data type to use in place of text data type when encryption is required and to be done on the server side as much transparently as possible.

The idea is that every time the data has to be written to disk on data related files (HEAP, TOAST, INDEX, WAL), it's encrypted. However logfiles are not managed and PostgreSQL must be configured to prevent it from leaking values.

At this stage, the extension is still in development and the encryption function does not use a key yet. The purpose is to evaluate how transparent the behavior is.

How it works

The extension provides new data types that are encrypted bytea for the storage, and unencrypted text (text only at the moment) when the data is requested.

This is done by defining the new type and doing the encryption/decryption in the INPUT/OUTPUT functions of the data type, for both TEXT and BINARY protocol of PostgreSQL so that it works for common drivers and also for drivers based on the BINARY protocol, like pgJDBC.

Installation and Testing

To build it, just do this:

make
make installcheck
make install

If you encounter an error such as:

make: pg_config: Command not found

Be sure that you have pg_config installed and in your path. If you used a package management system such as RPM to install PostgreSQL, be sure that the -devel package is also installed. If necessary tell the build process where to find it:

env PG_CONFIG=/path/to/pg_config make && make installcheck && make install

If you encounter an error such as:

ERROR:  must be owner of database regression

You need to run the test suite using a super user, such as the default "postgres" super user:

make installcheck PGUSER=postgres

Once the extension is installed, you can add it to a database. It's a simple as connecting to a database as a super user and running, here in a dedicated schema:

CREATE SCHEMA tde_types;
CREATE EXTENSION pg_encrypted_types WITH SCHEMA tde_types;

Configure PostgreSQL

It's recommended to configure PostgreSQL not to writte bind parameters and values from queries in error in the log files.

This can be achieved by setting:

SET log_parameter_max_length = 0;
SET log_parameter_max_length_on_error = 0;

Note however that the behavior from PostgreSQL must be cerfuly tested.

Usage

Creating a new table with tde_text

CREATE TABLE my_table (
   decrypted text
 , encrypted tde_text
);

Adding a new column with tde_text

CREATE TABLE my_table (
   decrypted text
);

ALTER TABLE my_table ADD COLUMN encrypted SET DATA TYPE tde_text;

Converting an existing column to/from tde_text

CREATE TABLE my_table (
   decrypted text
 , encrypted tde_text
);

-- From tde_text to text
ALTER TABLE my_table ALTER COLUMN encrypted SET DATA TYPE text;

-- From text to tde_text
ALTER TABLE my_table ALTER COLUMN encrypted SET DATA TYPE tde_text;

Indexing the encrypted data (tde_text)

-- Complete form
CREATE INDEX ON my_table USING BTREE( encrypted btree_tde_text_ops );

-- Shorten form
CREATE INDEX ON my_table USING BTREE( encrypted );

Limitations

  • Hardcoded encryption/decryption functions, will be removed in favor of pg_crypto functions or a generic callback API.

  • Support only equality and negator, no "order by" operators support.

  • This extension does not prevent the server from writing decrypted data in the log files, see "Configure PostgreSQL"

  • Other extensions might expose the unencrypted data, be careful of the PostgreSQL setup. For instance it's discouraged to use "auto-explain" extension.

  • Does not support pattern searching despite LIKE operator is available: it's only for equality operation.