GitHub user yz271544 added a comment to the discussion: [Ideas] data encrypt or
privacy infomation protection
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:
```sql id="5qhs11"
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:
```text id="0uuw7q"
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:
```text id="1obwwl"
authoritative policy catalog
|
v
policy generation/version
|
+----------------------+
| |
v v
coordinator cache segment-local cache
```
Each database instance could keep:
```text id="aiwu6p"
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:
```sql id="29lfk0"
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:
```text id="8gwii6"
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:
```sql id="6lmdij"
SELECT mobile FROM customer;
```
should apply privacy transformation.
But:
```sql id="4qt19t"
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:
```sql id="t8snkk"
UPDATE customer
SET ...
RETURNING mobile;
```
should still protect `mobile`, because the value is returned to the client.
Likewise for:
```text id="ipuxjh"
INSERT ... RETURNING
DELETE ... RETURNING
COPY ... TO STDOUT
cursor FETCH
```
So conceptually:
```text id="j7idke"
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:
```text id="pb2ux6"
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:
```sql id="qgwpbd"
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:
```sql id="f6q176"
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.
GitHub link:
https://github.com/apache/cloudberry/discussions/1943#discussioncomment-18269641
----
This is an automatically sent email for [email protected].
To unsubscribe, please send an email to: [email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
