GitHub user yz271544 added a comment to the discussion: [Ideas] data encrypt or
privacy infomation protection
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:
```sql
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:
```text
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:
```text
authoritative policy catalog
|
v
policy generation/version
|
+----------------------+
| |
v v
coordinator cache segment-local cache
```
Each database instance could maintain:
```text
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:
```text
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:
```sql
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:
```text
database_oid
user_oid
expires_at
privacy_bypass
```
Then the per-query decision becomes inexpensive:
```text
valid authorized session
-> original value
otherwise
-> apply privacy policy
```
One issue that probably needs special handling is credential leakage.
A statement such as:
```sql
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:
```text
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:
```sql
SELECT mobile FROM customer;
```
would require protection.
However:
```sql
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:
```text
INSERT ... SELECT
UPDATE ... FROM
CREATE TABLE AS SELECT
SELECT INTO
```
On the other hand:
```sql
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:
```text
INSERT ... RETURNING
DELETE ... RETURNING
COPY ... TO STDOUT
cursor FETCH
```
Conceptually:
```text
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:
```text
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:
```text
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:
```sql
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:
```text
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.
GitHub link:
https://github.com/apache/cloudberry/discussions/1943#discussioncomment-18269739
----
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]
