Hacker News Reader

Comment by coldtea | Hacker News Reader

>As far as I know, no database has ever had any of the problems he mentions with IDs not round-tripping properly during backup/restore. (Please correct me if I'm wrong! But why do I think this is true? Because any database that doesn't roundtrip primary keys would break all foreign keys on backup/restore. Databases with broken backup/restore tend to either fix it real fast, or go away.)

You might not use foreign keys, but still have autoincremented primary key.

Database ids on the URL are also a security concern.

View on HN

> Database ids on the URL are also a security concern.

They shouldnt be. Users should only be allowed access to their own records, even if they know the id's of records belonging to other users.

For one, they expose the number of users and other internal details. Even that is a security concern, access or not.

Not if you don't start at zero, and no one knows where you started. Heck, add a multiplier for use in URL's even. It's not hard to come up with reversible obfuscation functions that would hide your number of users for 99.99% of cases. If you have a competitor that set on knowing your count of whatever objects then maybe you have a case. But let's say this is number of posts, clearly viewable on the website anyways. Who cares? If that sort of info is exploitable, you have to have much bigger security holes present.

At the point where you're doing all of that obfuscation, wouldn't it be easier and safer to use something like a uuid?

So you rather your DB performance tank because of unsortable and unclusterable (sp?) PKs than expose a tiny bit information that shouldn't even be that important? You can use a UUID as a URI link, but you shouldn't use it as a PK on most databases. That means you would have to add another column for the link ID (which I think is a relatively good and clean solution)

Postgres doesn't have issues with UUIDs as primary keys; they don't need to be sorted (hash indexes were dicey before Postgres 10 but are just fine today) and Postgres doesn't row-cluster by default.

What performance cost there is (and in my experience it is not much) is because a UUID is twice as large as a bigint. And there are situations where that matters. But not that many of them.

Hash indexes can't be used for primary keys:

   postgres[7878][1]=# SELECT amname, pg_indexam_has_property(oid, 'can_unique') FROM pg_am WHERE amtype = 'i';
   ┌────────┬─────────────────────────┐
   │ amname │ pg_indexam_has_property │
   ├────────┼─────────────────────────┤
   │ btree  │ t                       │
   │ hash   │ f                       │
   │ gist   │ f                       │
   │ gin    │ f                       │
   │ spgist │ f                       │
   │ brin   │ f                       │
   └────────┴─────────────────────────┘
   (6 rows)

And postgres' btree indexes definitely are affected by the randomness in many UUID generation schemes.

Huh, so it is. And looking at our schema, it looks like the UUID/hash tables we've got don't use pkeys at all.

Looks like I have a conversation to have. Thanks. :)

Why shouldn’t you use a uuid as a primary key on most databases?

Use an encoded time prefix and a random part to create your UUID. I've been using such UUIDs as primary keys in Postgresql for years and I can't tell the difference in performance with say 64-bit numbers. If you don't need millions of records, even a 64-bit number with timestamp and random part will do – like "Twitter snowflake" ids.

> Not if you don't start at zero, and no one knows where you started.

Yes, in fact - even then. There are fascinating historical stories about lay-people who scoffed and then failed to account for the dark art of statistics. https://www.statisticshowto.datasciencecentral.com/german-ta...

I once scraped a representative slice of Slashdot comments a while back and noticed some odd changes in their UIDs. They were originally sequential and then they switched to even numbers for a while, then odd numbers. I don't know if they were doing it to juice the UID stats to look like more people were joining the platform.

> They were originally sequential and then they switched to even numbers for a while, then odd numbers.

That sounds like they wanted to migrate to another cluster, and wanted to have both clusters running at the same time (for testing) without risking an ID overlap, so they turned the least significant bit of the ID into a cluster identifier. The older cluster was 0 (even numbers), the new cluster was 1 (odd numbers).

> Database ids on the URL are also a security concern.

More so than in mark up? If so why?

Should surrogate keys never be exposed to users in any form? Then what's the alternative? Nonces for everything, all the time?

> More so than in mark up? If so why?

Easy to enumerate, if that's a concern.

In the case of URLs, you can have /questions/:id/:slug, and then load the question by :id, but if :slug doesn't match the slug you've got in the database, then return 404.

Sure, but that means that you have a unique key combination of id + slug for all intents and purposes. The argument for having the ID is that the slug can change and you can redirect. If you don't redirect, that argument falls flat, and if you redirect you're easier to enumerate.

Isn't most of the security concern fixed by using guids instead of incrementing ints?

Yes, but please don't use guids in urls.

why?

Seems like your options are:

1. Mutable data (looks nice but can change)

2. Incrementing data (looks nice but can be guessed and measured)

3. Random looking data (immutable and unguessable but ugly)

Guids aren't the only option for 3 and some of the others may not look as bad, but it still seems the safest option. Any other compelling reasons to not use guids?

There's no reason not to use guids in urls.

Other than them being obnoxiously long, that is.

Reply on news.ycombinator.com