Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

PostgreSQL Anonymizer

postgresql-anonymizer.readthedocs.io

Post reply on HN
Print view View original

11–20 of 54 posts

Re: PostgreSQL Anonymizer

#11

Post by phoronixrly »

I have some experience with the 'Masking Views' functionality. If you are going to rely on it and specifically in a Rails app, know that it is against conventions and thus is generally inconvenient. This may be the same with any other framework that features DB schema migrations.

More specifically the integration of this functionality at a fortunately ex-employer was purposefully kept away from the dev team (no motivation was offered, however I suspect that some sort of segmentation was sought after) and thus did not take into account that tables with PII did in fact still need their schema changed from time to time.

This lead to the anonymizer extension, together with the confidential views to only be installed on production DB instances with dev, test, and staging instances running vanilla postgres. With this, the possibility to catch DB migration issues related to the confidential views was pushed out to the release itself. This lead to numerous failed releases which involved having the ops team intervene, manually remove the views for the duration of the release, then manually re-create them.

So,

If you plan to use this extension, and specifically its views, make sure you have it set up in the exactly same way on all environments. Also make sure that its initialisation and view creation are part of your framework's DB migrations so that they are documented and easy to precisely reproduce on new environments.

Re: PostgreSQL Anonymizer

#12

Post by gkbrk »

Clickhouse has something similar called clickhouse-obfuscator [1]. It even works offline with data dumps so you can quickly prepare and send somewhat realistic example data to others.

According to its --help output, it is designed to retain the following properties of data:

- cardinalities of values (number of distinct values) for every column and for every tuple of columns;

- conditional cardinalities: number of distinct values of one column under condition on value of another column;

- probability distributions of absolute value of integers; sign of signed integers; exponent and sign for floats;

- probability distributions of length of strings;

- probability of zero values of numbers; empty strings and arrays, NULLs;

- data compression ratio when compressed with LZ77 and entropy family of codecs;

- continuity (magnitude of difference) of time values across table; continuity of floating point values.

- date component of DateTime values;

- UTF-8 validity of string values;

- string values continue to look somewhat natural

[1]: https://clickhouse.com/docs/en/operations/utilities/clickhou...

Re: PostgreSQL Anonymizer

#13

Post by bux93 »

gkbrk wrote: post #12

Clickhouse has something similar called clickhouse-obfuscator [1]. It even works offline with data dumps so you can quickly prepare and send somewhat realistic example data to others. According to its --help output, it is designed to retain the following properties of data: - cardinalities of values (number of distinct values) for every column and for every tuple of columns; - conditional cardinalities: number of dis…

The Dutch national office of statistics has tools intended to de-identify 'microdata' such that k-anonimity[1] is achieved called mu-argus[2] and tau-argus.

[1] A release of data is said to have the k-anonymity property if the information for each person contained in the release cannot be distinguished from at least k-1 individuals whose information also appear in the release. https://en.wikipedia.org/wiki/K-anonymity [2] https://research.cbs.nl/casc/mu.htm

Re: PostgreSQL Anonymizer

#14

Post by pgryko »

Are these tools able to automatically identify PII information or do you have to specify columns and data types manually? What happens if you have PII data in a string field? Do you just rely on something like spacy to identify the PII data?

Re: PostgreSQL Anonymizer

#15

Post by graindcafe »

pgryko wrote: post #14

Are these tools able to automatically identify PII information or do you have to specify columns and data types manually? What happens if you have PII data in a string field? Do you just rely on something like spacy to identify the PII data?

From the website:

> The project has a declarative approach of anonymization.

> Finally, the extension offers a panel of detection functions that will try to guess which columns need to be anonymized.

https://postgresql-anonymizer.readthedocs.io/en/stable/detec...

Re: PostgreSQL Anonymizer

#16

Post by heeton »

lovasoa wrote: post #4
sgt wrote: post #2

Just be careful that you don't anonymize your production data.

The principle of the software seems to be that the original data is never altered. It is a postgres extension that "masks" the data for certain postgres users. You can always connect as the root user and see everything when you need to.

It allows updating the original data - https://postgresql-anonymizer.readthedocs.io/en/stable/stati...

> These methods will destroy the original data. Use with care.

Re: PostgreSQL Anonymizer

#18

Post by foreigner »

TIL that PostgreSQL has SECURITY LABEL! It seems like this could be useful for storing all sorts of metadata about database objects, not just security stuff. E.g. like the COMMENT but not global. From reading the docs it looks like you need a "label provider" to get it to work though. I can only seem to find a few label providers around, does anyone know of one that isn't security/anonymization related and could be used more generically?
Post reply on HN