Is there a way to convert a UUID to a UUID5 in an existing postgres data set?
I have a master/slave setup with pglogical replicating specific tables & columns. I need to give access to this replicated data to a third party, but I want to anonymise it as much as possible.
Every row in a table uses a UUID (generated in code) as primary key, but in order to ensure anonymity I need to ensure that the primary keys of the master & replicated tables are one-way transformed to make back-tracing harder. Some of the replicated tables refer to each other as well, meaning I can't just exclude those columns - values need to match between tables. Hopefully this makes sense so far...
Is there a way of using uuid-ossp and uuid_generate_v5() to essentially use the existing UUIDs as the namespace for a v5 UUID, returning those in the view to the third party (or even to use to transform the specific columns during replication)?
I'm a little stumped. If I can get this sorted entirely in postgres I won't have to muck about with additional tools to transform the data before allowing the third party access, I can just give them real-time data instead of batched. But if I need to involve some additional code then I guess that's not the end of the world...
Thanks in advance!
Simply provide read-only views to a third-party (in a separate schema) and limit data access to them to those views (or schema).
A view can then be defined with a query such as:
SELECT uuid_generate_v5(t.uuid, 'custom-third-party'), t.a, t.b, ...
FROM ...
This way, you can also provide different UUID's to different third-parties as the original UUID's should be unique across data entries.
Rewriting data strategies are not going to scale and would just add overhead where it is not needed.