[Ideas] data encrypt or privacy infomation protection

[yz271544]

Description

The following project describes a data encryption/decryption algorithm previously implemented in the Teradata data warehouse, which has also been used on other platforms.

The current Cloudberry could consider adding the issuance of decryption JWT tokens to the console, for example, by adding an administrator-set key to the claim.

Then, the JWT token could be configured at the JDBC or command-line connection session level to support encryption and decryption in various scenarios.

It's also worth considering scenarios where data is encrypted during loading and decrypted during export. Alternatively, it could support extended interfaces like Teradata's fastload and fastexp, providing inmod and outmod functions for both data loading and export.

data encrypt and decrypt demo

Use case/motivation

The goal is to enable native support for privacy data protection scenarios in next-generation data warehouses.

Related issues

No response

Are you willing to submit a PR?

• Yes I am willing to submit a PR!

[jianlirong]

Thanks for opening this, @yz271544 — this is a topic I think a lot of users
running Cloudberry in regulated environments (finance, healthcare, gov, etc.) would
care about.

A few questions to help turn this into something actionable:

1. Cloudberry already has two encryption layers: TDE (storage-engine-level
   encryption at rest) and pgcrypto (column-level encrypt/decrypt via SQL
   functions). How does the JWT-based approach relate to these — is it meant
   to sit above pgcrypto (managing/rotating the keys those functions use),
   or is it a separate, session-scoped mechanism?

2. On the JDBC/CLI session-level piece — could you sketch a concrete flow?
   E.g. client presents a JWT at connect time → coordinator validates it →
   derives a decryption key from a claim → result sets are transparently
   decrypted before being returned? That would clarify whether this is
   closer to a connection-level policy (like RLS) or in-transit re-encryption.

3. The inmod/outmod-style hooks for load/unload are interesting — the
   gpfdist/gpload path could plausibly support pluggable transform hooks.
   Does the teradata-cpt demo have a reference implementation for this part,
   or is it algorithm-only right now?

4. Would this be additive (new extension/contrib module) or does it need
   changes to core (parser/executor) to plumb JWT context through to the
   storage/output paths? That distinction matters a lot for scoping the work.

If there's interest, it might be worth turning this into a short design doc.

Lirong

[pavetheroad41]

Thanks for putting this together - data protection is a real gap for a lot of enterprise Cloudberry/Greenplum deployments, so it's good to see this raised.

A few thoughts and questions on the proposal:

Scope and threat model
It would help to pin down what threat model this is targeting - encryption at rest (protecting data files/backups if storage is compromised), encryption in transit (already largely covered by TLS on JDBC/libpq), or column-level/application-layer encryption (protecting specific sensitive fields even from DB admins). The JWT + claims approach you're describing sounds closest to the third case, which has very different design constraints than TDE-style at-rest encryption.

Prior art to consider

pgcrypto already gives Postgres-family databases column-level encrypt/decrypt functions (pgp_sym_encrypt, etc.). Worth clarifying how this proposal differs - is the goal mainly to move key/claim management into a JWT-based session context rather than passing keys explicitly in SQL?
Greenplum/Cloudberry's MPP architecture means encryption/decryption work needs to be pushed down to segments efficiently, not just the coordinator, this could get expensive at scale if not carefully implemented (e.g., per-row UDF calls).

JWT-based key delivery
Using a JWT claim to carry a decryption key (or a wrapped key) at session level is an interesting idea, but it raises questions: How would key rotation work? What's the failure mode if a token expires mid-session or mid-COPY? Would the key material touch server logs or EXPLAIN output anywhere?

Load/export integration
The fastload/fastexp-style inmod/outmod hooks are a reasonable model - Cloudberry's gpfdist/external table framework already has extensibility points that might map naturally onto this (custom protocol handlers or format functions) rather than requiring new machinery.

Happy to help think through a design doc if there's interest in moving this forward, particularly around how this would interact with the segment/coordinator split and existing external table protocols.

[yz271544]

Thanks for the feedback. I want to clarify the proposal a bit further.

This is not intended to be another encryption-at-rest mechanism like TDE, and it is also different from storing encrypted column values with pgcrypto.

The underlying table data would remain unchanged. Privacy transformation would only happen when sensitive data is returned to a client. An authorized session could bypass the transformation and receive the original value.

So this is probably closer to a Dynamic Privacy Protection / Dynamic Data Masking framework, with CPT being only one possible transformation algorithm.

1. Privacy policy

An administrator could define policies based on:

• column name or pattern
• data type
• scope: database / schema / table / column
• transformation algorithm
• algorithm-specific parameters

For CPT, possible parameters could include:

• mode
• key / key ID
• start position
• transformation length
• a special value meaning "from start position to the end"

For example:

CREATE PRIVACY POLICY phone_policy
SCOPE DATABASE current_database()
MATCH (
    COLUMN_NAME ~ '(mobile|phone|tel)',
    DATA_TYPE IN ('text', 'varchar')
)
USING CPT (
    MODE = 'DIGIT',
    KEY_ID = 'phone-key-v3',
    START = 4,
    LENGTH = -1
);

Policy precedence could be:

column > table > schema > database

2. Global metadata and cache

The policy should be logically cluster-wide, but I do not think all coordinator and segment processes need to literally share one memory region.

A possible design is:

authoritative policy catalog
        |
        v
policy generation/version
        |
        +----------------------+
        |                      |
        v                      v
coordinator cache        segment-local cache

Each database instance could keep:

L1: backend-local cache
L2: instance shared-memory cache
L3: persistent catalog

When a policy changes, its generation/version changes and stale caches are invalidated or rebuilt.

For CPT specifically, key-derived substitution mappings could also be precompiled and cached in shared memory rather than rebuilt for every row.

3. Session-level JWT authorization

For CLI/JDBC/ODBC scenarios, I would like authorization to be session-scoped, for example:

SET privacy.token = 'eyJ...';

The JWT would not contain the CPT key. It would only authorize the session to bypass privacy transformation.

Claims could bind the token to:

• database
• database user
• privilege such as privacy:bypass
• validity period

After validation, the backend could keep a small session-local authorization context.

Then runtime behavior becomes:

authorized session
    -> return original value

normal session
    -> apply privacy policy

One concern is token leakage through SQL logs, pg_stat_activity, audit logs, or client history, so the token value should be treated as sensitive configuration.

4. Apply protection only at the output boundary

Initially I thought this could simply be based on whether the statement is a SELECT.

But I think the better rule is:

Only protect sensitive data when it crosses the database-to-client boundary.

For example:

SELECT mobile FROM customer;

should apply privacy transformation.

But:

INSERT INTO backup_customer(mobile)
SELECT mobile FROM customer;

should not, because this is internal data movement and the original value should be stored.

The same applies to UPDATE ... FROM, CTAS and SELECT INTO.

However:

UPDATE customer
SET ...
RETURNING mobile;

should still protect mobile, because the value is returned to the client.

Likewise for:

INSERT ... RETURNING
DELETE ... RETURNING
COPY ... TO STDOUT
cursor FETCH

So conceptually:

Scan / Filter / Join / Aggregate / Sort / Motion
                 |
                 | plaintext
                 v
        Final client-visible output
                 |
                 v
          Privacy Policy
          /            \
 authorized          normal
     |                  |
 plaintext          CPT / MASK

This keeps indexes, joins, grouping, ordering and normal internal query semantics unchanged.

5. MPP execution

Since Cloudberry is MPP, applying all transformations only on the coordinator could become a bottleneck for large result sets.

Ideally the final privacy projection should be pushed to segments where possible:

Segment 1 -> privacy projection --\
Segment 2 -> privacy projection ----> Coordinator -> Client
Segment 3 -> privacy projection --/

while relational processing still happens on plaintext values before that final projection.

6. Expression bypass

Protecting only direct column references is not enough.

For example:

SELECT mobile || '' FROM customer;
SELECT substring(mobile, 1, 11) FROM customer;
SELECT json_build_object('mobile', mobile) FROM customer;

must not bypass the policy.

Eventually this probably requires some form of sensitive-data lineage / taint propagation.

For an initial implementation, a conservative approach could be:

• direct sensitive column output -> apply configured policy
• expression derived from sensitive column -> deny or apply a safe fallback
• safe aggregate such as COUNT(*) -> allow
• authorized session -> bypass

7. Implementation questions

I would especially appreciate feedback on these points:

1. Would an authoritative catalog plus per-instance shared-memory caches be a reasonable architecture for policy metadata in Cloudberry?

2. Would a custom session GUC such as:

SET privacy.token = '...';

be a reasonable integration point for session authorization?

3. Would it make more sense to implement the privacy transformation close to the final output / executor boundary, rather than modifying both the PostgreSQL planner and GPORCA?

My current preference is to keep the transformation as late as possible, so that only data actually leaving the database is affected.

[yz271544]

A few implementation details behind the proposal above, mainly to explain how I am currently thinking about policy management, session authorization, and query execution.

1. Policy representation

One possible model is to define privacy policies using:

• column name or column-name pattern
• data type
• scope: database / schema / table / column
• transformation algorithm
• algorithm-specific parameters

For CPT, the parameters may include:

• mode
• key or key identifier
• start position
• transformation length
• a special value meaning "from the start position to the end of the value"

For example:

CREATE PRIVACY POLICY phone_policy
SCOPE DATABASE current_database()
MATCH (
    COLUMN_NAME ~ '(mobile|phone|tel)',
    DATA_TYPE IN ('text', 'varchar')
)
USING CPT (
    MODE = 'DIGIT',
    KEY_ID = 'phone-key-v3',
    START = 4,
    LENGTH = -1
);

I am also considering hierarchical policy precedence such as:

column > table > schema > database

This would allow a database-wide default policy while still supporting more specific overrides.

2. Policy metadata and caching

The policy metadata needs to be logically consistent across the Cloudberry cluster, but I do not think this necessarily implies a single shared-memory region across the coordinator and all segments.

A possible model is:

authoritative policy catalog
        |
        v
policy generation/version
        |
        +----------------------+
        |                      |
        v                      v
coordinator cache        segment-local cache

Each database instance could maintain:

L1: backend-local cache
L2: instance shared-memory cache
L3: persistent catalog

When a policy is created, altered, or dropped, the policy generation could change and stale caches could be invalidated or rebuilt.

For algorithms such as CPT, this may also be useful for precomputed state.

For example, key-derived substitution mappings could be compiled once:

policy create / update
        |
        v
compile CPT parameters
        |
        v
build substitution mappings
        |
        v
store compiled CPT context in local shared memory

The executor would then only need to look up the compiled context and apply the transformation.

This avoids rebuilding CPT mappings for every row.

3. Session authorization

For CLI/JDBC/ODBC usage, I would like the authorization mechanism to remain session-scoped and easy to consume.

For example:

SET privacy.token = 'eyJ...';

The JWT itself would not carry the CPT key.

It would only authorize the current session to bypass privacy transformation.

Possible claims may bind the token to:

• database
• database user
• a privilege such as privacy:bypass
• validity period

After successful validation, the backend could keep only a small session-local authorization context, for example:

database_oid
user_oid
expires_at
privacy_bypass

Then the per-query decision becomes inexpensive:

valid authorized session
    -> original value

otherwise
    -> apply privacy policy

One issue that probably needs special handling is credential leakage.

A statement such as:

SET privacy.token = '...';

may otherwise appear in SQL logs, pg_stat_activity, audit logs, JDBC traces, or client history.

So if a GUC-based interface is used, I think privacy.token should be treated as sensitive data and hidden or redacted wherever practical.

4. Execution semantics

One point I changed my mind about is where the transformation should be triggered.

Initially I was thinking in terms of:

SELECT -> transform
INSERT / UPDATE / DELETE -> do not transform

But this appears too coarse.

I think a better semantic rule is:

Apply privacy transformation only when a sensitive value is exposed through a client-visible output boundary.

For example:

SELECT mobile FROM customer;

would require protection.

However:

INSERT INTO backup_customer(mobile)
SELECT mobile FROM customer;

would not, because the selected value is being used internally and should remain unchanged.

Likewise, transformations should probably not affect:

INSERT ... SELECT
UPDATE ... FROM
CREATE TABLE AS SELECT
SELECT INTO

On the other hand:

UPDATE customer
SET ...
RETURNING mobile;

does expose a value to the client, so the RETURNING expression should still be protected.

The same reasoning applies to:

INSERT ... RETURNING
DELETE ... RETURNING
COPY ... TO STDOUT
cursor FETCH

Conceptually:

Scan
Filter
Join
Aggregate
Sort
Motion
        |
        | original values
        v
client-visible projection
        |
        v
privacy transformation
        |
        +-- authorized session -> original value
        |
        +-- normal session     -> CPT / MASK / ...

The reason I prefer this model is that the privacy transformation would not change the semantics of filtering, joining, sorting, grouping, statistics, or other relational operations.

5. MPP execution

There is also an MPP-specific consideration.

If the coordinator performs all transformations after receiving the final result set, it may become a bottleneck for large result sets.

For example:

Segments
    |
    | large plaintext result
    v
Coordinator
    |
    v
privacy transformation
    |
    v
Client

Ideally, where the execution plan allows it, the final privacy projection could be executed on the segments:

Segment 1 -> privacy projection --\
Segment 2 -> privacy projection ----> Coordinator -> Client
Segment 3 -> privacy projection --/

while filters, joins, grouping, sorting, and other internal operations still use the original values.

This is another reason why having compiled policy state available in segment-local shared memory may be useful.

6. Derived expressions

A direct-column-only implementation would be easy to bypass.

For example:

SELECT mobile || '' FROM customer;
SELECT substring(mobile, 1, 11) FROM customer;
SELECT json_build_object('mobile', mobile) FROM customer;

If mobile is protected, these derived expressions should not automatically become unprotected.

Longer term, this may require some form of sensitive-data lineage or taint propagation.

For an initial implementation, a conservative rule may be sufficient:

direct sensitive column
    -> configured transformation

expression derived from sensitive column
    -> deny or apply a safe fallback

safe aggregate such as COUNT(*)
    -> allow

authorized session
    -> bypass

This could keep the first implementation relatively small while avoiding obvious policy bypasses.

7. Open implementation questions

The areas where I would especially appreciate implementation guidance are:

1. Whether Cloudberry already has a suitable catalog invalidation or generation mechanism that could be reused for privacy-policy cache invalidation across coordinator and segments.

2. Whether a custom session GUC is an appropriate place for a short-lived authorization token, or whether there is a better session-authentication hook available.

3. Where the cleanest client-output boundary exists in Cloudberry execution, especially considering both PostgreSQL Planner and GPORCA paths.

4. Whether a final privacy projection can be pushed to segments without affecting normal optimizer semantics.

5. How much of this could initially be implemented as an extension, and which parts would realistically require Cloudberry core changes.

My current preference is to keep policy transformation as late in execution as possible and to avoid changing internal relational semantics.

[pavetheroad41]

Thanks for the detailed writeup, reframing this as dynamic data masking (transform-at-egress) rather than TDE/pgcrypto clarifies the scope a lot, and these are the right questions to be asking at this stage.

On your open implementation questions:

1. Catalog invalidation for policy metadata

Cloudberry/Postgres already has most of the plumbing for this: DDL on the coordinator is dispatched to segments via the existing QD→QE dispatch path, and syscache/relcache invalidation runs on SharedInvalidationMessage (sinvaladt.c). A new pg_privacy_policy catalog could piggyback on that mechanism, new invalidation message type, bump a generation counter, backends lazily refresh their L1 cache. That's close to what you sketched, so it likely doesn't need new infrastructure.

2. Session GUC vs. auth hook

A custom GUC (privacy.token) is workable, but I'd validate it closer to connection time (e.g. ClientAuthentication_hook) rather than allowing arbitrary mid-session SET, and explicitly strip it from pg_stat_activity.query and audit-log text, not just SHOW ALL. Since the JWT is a bearer credential, leakage risk is about replay within its validity window; short expiry + audience binding (as you proposed) is the right mitigation, but the redaction needs to be more aggressive than a GUC flag provides.

3. Client-output boundary - Planner vs. GPORCA

Postgres already gives you most of this distinction: regardless of which planner produced the plan, execution ends by pushing tuples through a DestReceiver. Plain SELECT uses DestRemote; CTAS/SELECT INTO use DestIntoRel - already a different receiver. That's nearly the exact dichotomy you want, and it's planner-agnostic, so you'd hook at the receiver level rather than in ORCA or the PG planner.

Two gaps to flag: COPY ... TO STDOUT doesn't go through DestReceiver, it's a separate path in copy.c and needs its own hook. RETURNING and cursor FETCH do use the normal tuple destination, so those should fall out for free.

4. Pushing the projection to segments

Feasible if it's inserted as a post-optimization wrapper - just below the final Gather Motion, after planning completes - rather than something the optimizer costs, so it doesn't perturb join/agg/sort decisions. Worth noting as precedent: RLS + GPORCA has had rough edges historically (ORCA falls back to the PG planner in some RLS cases) - a useful cautionary example for how an ORCA-blind feature like this could get stuck.

5. Extension vs. core

Given the COPY gap and the need for cross-node cache invalidation, I don't think this stays cleanly in extension territory. A reasonable path: prototype as an extension covering plain SELECT/RETURNING only (validates policy DDL + caching + JWT auth end-to-end), then upstream once proven, since COPY interception and ORCA-safe plan injection will need to land in core regardless.

Given the scope, I'd second Lirong's suggestion of a short design doc - the catalog schema, cache invalidation protocol, and DestReceiver hook points would be the key pieces to nail down before writing code.