Closed rtyler closed 5 years ago
UPDATE events SET correlator = encode(digest(concat(correlator, type), 'sha256'), 'hex') WHERE correlator ~ '(\w+)-(\w+)-(\w+)-(\w+)-(\w+)';
:rofl:
This converts to the same SHA256 provided by the server-side implementation in #19
uplink_development=# SELECT * FROM events ORDER BY id DESC LIMIT 10;
id | type | payload | createdAt | updatedAt | correlator
----+--------------+-----------------------------------------------------------+----------------------------+----------------------------+------------------------------------------------------------------
38 | stapler | {"timestamp":"Wed Oct 24 14:08:09 PDT 2018","hi":"there"} | 2018-10-24 21:08:09.129+00 | 2018-10-24 21:08:09.129+00 | c49de0b576b689ac6a5d7233ba59d8736065ddca0a55c130624fdb1c2541d2c0
37 | jest-example | {"generatedAt":1540415288607} | 2018-10-24 21:08:08.629+00 | 2018-10-24 21:08:08.629+00 | 0xdeadbeef
36 | stapler | {"timestamp":"Wed Oct 24 14:07:44 PDT 2018","hi":"there"} | 2018-10-24 21:07:44.315+00 | 2018-10-24 21:07:44.315+00 | c49de0b576b689ac6a5d7233ba59d8736065ddca0a55c130624fdb1c2541d2c0
Row 36 was transformed by the server. Row 38 was transformed by the PostgreSQL query above :+1:
This has been applied in production, and we're now on more privacy-conscious correlator IDs :smile:
:laughing: you can do this entirely in PostgreSQL after loading the
pgcrypto
extension withCREATE EXTENSION pgcrypto