PostgreSQL and UUID as Primary Key

Re:PostgreSQL and UUID as Primary Key by OskarS on Saturday July 06, 2024 @02:56AM (#40888623)

Can I ask (as a humble application developer, not a backend/database person), if the two requirements are:

1. The UUIDs should be ordered internally, for B-tree performance

2. The UUIDs should not be ordered externally, for security reasons

Why not use encryption? The unencrypted ID is a sequential id, but as soon as it leaves the database, it's always encrypted. Like, when getting it out:

    SELECT encrypt(id) FROM table WHERE something = whatever;

and when putting stuff in:

    UPDATE table SET something = whatever WHERE id = decrypt(<encrypted-key>)

Seems like the best of both worlds, and you don't need to store separate things.

[ Parent | on HN ]

Re:PostgreSQL and UUID as Primary Key by spencerap on Saturday July 06, 2024 @03:36AM (#40888795)

If the key and encryption mechanism are ever leaked, those opaque external IDs can be converted easily back to sequence numbers, and vice versa, which might pose a risk for you or your users. You won't be able to rotate the encryption key without breaking anything external that tracks those encrypted IDs... third party services, SEO, user bookmarks, etc.

[ Parent | on HN ]

Re:PostgreSQL and UUID as Primary Key by OskarS on Saturday July 06, 2024 @03:45AM (#40888834)

You store the key in the database, right? Like, if the database leaks, it doesn’t matter if your ids are sequeneced or unsequenced, because all data has leaked anyway. The key leaking doesn’t seem like a realistic security issue.

[ Parent | on HN ]

Re:PostgreSQL and UUID as Primary Key by zxexz on Saturday July 06, 2024 @04:37AM (#40889034)

Ideally if you do this, you store the key in a separate schema with proper roles so that you can call encrypt() with the database role, which can't select the key. Even then, the decrypted metadata should not be particularly sensitive - and should immutably reference a point in time so you can validate against some known key revocation retroactively.

My take is it's rarely necessary to have a token, that you give to an external entity, that has any embedded metadata all - 99.9% of apps aren't operating at a scale where even a million-key hashmap sitting in ram and syncing changes to disk on update would cause any performance difference.

[ Parent | on HN ]

Re:PostgreSQL and UUID as Primary Key by OskarS on Saturday July 06, 2024 @07:09AM (#40889669)

This is a very weird thread: half the people are arguing that having these timestamps is not a realistic security problem at all, and the other half is arguing that any fix to it has to have Fort Knox level security policies.

It seems to me: the actual value of knowing these ids/timestamps to a hacker is tiny, but it's not nothing (German tank problem and all that). Like, if a hacker was able to decode the timestamps, it's not ideal, but it's not like a catastrophe either (especially given that half the people in this thread thinks it has no security value at all). Given that threat model, a simple scheme like I suggested seems fine to me.

[ Parent | on HN ]

Re:PostgreSQL and UUID as Primary Key by stoperaticless on Saturday July 06, 2024 @05:34AM (#40889268)

> The key leaking doesn’t seem like a realistic security issue.

But it is.

If you have a password in a system, you want to rotate it regularly or at least have that ability (for example, when angry colleague leaves).

[ Parent | on HN ]

Re:PostgreSQL and UUID as Primary Key by fauigerzigerk on Saturday July 06, 2024 @03:46AM (#40888836)

>Why not use encryption?

Because then you have a key management problem, which adds complexity.

[ Parent | on HN ]