Feature: conditional transform

[gregwebs]

A conditional transform states a SQL condition used to decide whether or not to transform a row. In datanymizer a where clause is given as a string. This API seems to work. Below groups is a table.

  - name: groups
    query:
      transform_condition: "id NOT IN (select group_id FROM employee_groups)"

Datanymizer implemented this by adding NOT to the given query. I fixed an issue that adding NOT also needs proper NULL-checking behavior: datanymizer/datanymizer@24e2521

[wwoytenko]

I wonder, does that condition apply to the whole record or a specific transformer?

So here is the definition of the transformation now:

    - schema: "humanresources"
      name: "employee"
      transformers:
        - name: "Hash"
          params:
            column: "jobtitle"
            function: "sha1"
            max_length: 10

In my point of view, there might be a condition for the whole record and for specific transformer.

The prototype might be:

    - schema: "humanresources"
      where: "{{ gt .GetRawColumnValue("id") 100 }}"
      name: "employee"
      transformers:
        - name: "Hash"
          where: '{{ not eq .GetRawColumnValue("jobtitle") "Developer" }}'
          params:
            column: "jobtitle"
            function: "sha1"
            max_length: 10

If we are talking about tuple filtering then it is connected with other features (database scale down). As for now, it is better to use the query property until the automatic scale-downing feature is released.

[gregwebs]

This applies to the whole record.

It is very useful to also selectively not anonymize certain column values. We have been able to achieve that with an if statement in template logic. However it would be useful to have a declarative way of doing it as you show.

[wwoytenko]

Just want to inform you that this feature is going to be published right after the next major v0.2.0 release (15 May). Probably v0.2.1 or v0.2.2 I will keep you posted.

[wwoytenko]

Requires more testing – moved to Q4

[SiPaff]

I don't want to transform some user data, in particular some of our email developers, as I understand it, this functionality is not fully implemented?
I didn't find anything about exceptions in the documentation :(

transformation:
 - schema: "public"
   name: "account"
   transformers:
     - name: "RandomEmail"
       params:
         column: "email"
         engine: "hash"
         keep_original_domain: false
         local_part_template: "{{ .random_string | trunc 10 }}"

[gregwebs]

@SiPaff if you preserve the UUID during transformations then after running greenmask you could restore developer emails based on a list of (UUID, email) or possibly group/permission membership if those have not changed.

[wwoytenko]

@SiPaff Hi! You’re correct—the cond feature hasn’t been released yet, but it’s on the way. I’ll be sure to notify you once it’s complete. The workaround that metion @gregwebs might be suitable for you.

[SiPaff]

@wwoytenko This approach doesn't quite meet the requirements. We'll have to use pg_datanymizer the old-fashioned way, but we're looking forward to this opportunity in greemaskIO.

[wwoytenko]

@gregwebs @SiPaff

I made significant revisions to #34. I still need to add some tests for the transformation pipeline, and then this will be released in v0.2.1 on October 31.

Some things to discuss:

The conditions will work only with the current table scope, meaning you can't evaluate conditions based on values in another table.

For example, this does not work:

transform_condition: "id NOT IN (select group_id FROM employee_groups)"

Instead, you will be able to use:

when: "!(record.id | has([1,2,3]))"

It uses expressions similar to templates.

P.S.
If we need to control the transformation flow via SQL queries as well, I will create a new feature request.

[wwoytenko]

The feature is now available in main. The release is planned on 31.10.2024

https://docs.greenmask.io/dev/built_in_transformers/transformation_condition/

[gregwebs]

@wwoytenko awesome! Supporting arbitrary SQL or a join to another table is definitely useful. But I could probably use this as is by having a pre-query step and templating.

BTW, the docs link give me a cert error on a chromium browser.

[wwoytenko]

I hope now cert issue is fixed.

[gregwebs]

Works for me now.

[wwoytenko]

I created #227 feat request - sql cond implementation