Re: Possible solution for masking chosen columns when using pg_dump
От | Julien Rouhaud |
---|---|
Тема | Re: Possible solution for masking chosen columns when using pg_dump |
Дата | |
Msg-id | 20221003154453.sfhl54gb3ccxpscp@jrouhaud обсуждение исходный текст |
Ответ на | Possible solution for masking chosen columns when using pg_dump (Олег Целебровский <oleg_tselebrovskiy@mail.ru>) |
Ответы |
Re: Possible solution for masking chosen columns when using pg_dump
|
Список | pgsql-hackers |
Hi, On Mon, Oct 03, 2022 at 06:30:17PM +0300, Олег Целебровский wrote: > > Hello, here's my take on masking data when using pg_dump > > The main idea is using PostgreSQL functions to replace data during a SELECT. > When table data is dumped SELECT a,b,c,d ... from ... query is generated, the columns that are marked for masking are replacedwith result of functions on those columns > Example: columns name, count are to be masked, so the query will look as such: SELECT id, mask_text(name), mask_int(count),date from ... > > So about the interface: I added 2 more command-line options: > > --mask-columns, which specifies what columns from what tables will be masked > usage example: > --mask-columns "t1.name, t2.description" - both columns will be masked with the same corresponding function > or --mask-columns name - ALL columns with name "name" from all dumped tables will be masked with correspodingfunction > > --mask-function, which specifies what functions will mask data > usage example: > --mask-function mask_int - corresponding columns will be masked with function named "mask_int" from defaultschema (public) > or --mask-function my_schema.mask_varchar - same as above but with specified schema where the function is stored > or --mask-function somedir/filename - the function is "defined" here - more on the structure below FTR I wrote an extension POC [1] last weekend that does that but on the backend side. The main advantage is that it's working with any existing versions of pg_dump (or any client relying on COPY or even plain interactive SQL statements), and that the DBA can force a dedicated role to only get a masked dump, even if they forgot to ask for it. I only had a quick look at your patch but it seems that you left some todo in russian, which isn't helpful at least to me. [1] https://github.com/rjuju/pg_anonymize
В списке pgsql-hackers по дате отправления: