Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

My notes on Gitlab's Postgres schema design (2022)

shekhargulati.com

Post reply on HN
Print view View original

141–150 of 170 posts

Re: My notes on Gitlab's Postgres schema design (2022)

#141

Post by neonsunset »

gfody wrote: post #42
zetalyrae wrote: post #28

The point about the storage size of UUID columns is unconvincing. 128 bits vs. 64 bits doesn't matter much when the table has five other columns. A much more salient concern for me is performance. UUIDv4 is widely supported but is completely random, which is not ideal for index performance. UUIDv7[0] is closer to Snowflake[1] and has some temporal locality but is less widely implemented. There's an orthogonal approac…

think of the primary keys in a database like typedef void* ie it's your fundamental pointer and the size of it will impact every aspect of performance throughout - memory/disk footprint and corresponding throughput bottlenecks, cpu time comparing keys which is what every operation reduces to in the deepest inner-most loops of joins and lookups etc. when x86-64 cpus were new the performance impact from switching to 64…

Prefer 32-bit does nothing for modern .NET targets. This is actually the first time I've heard the term being used in many years, even back in .NET Framework 4.6.x days it wasn't much of a concern - the code would be executed with 64-bit runtime as a default on appropriate hosts.

Re: My notes on Gitlab's Postgres schema design (2022)

#142

Post by yellowapple »

worksonmine wrote: post #136
yellowapple wrote: post #129

Earlier quoted context omitted.

> If you can still crawl all IDs and figure out if someone has an account that way it helps filter out what username and password combinations to try from previous leaks. Right, but like I suggested above, if you're able to get any response other than a 404 for an ID other than one you're authorized to access, then that in and of itself is a severe issue. So is being able to log in with that ID instead of an actual u…

> Right, but like I suggested above, if you're able to get any response other than a 404 for an ID other than one you're authorized to access, then that in and of itself is a severe issue. So is being able to log in with that ID instead of an actual username. You're missing the point and you're not thinking like a hacker yet. It's not about the ID itself or even private profiles, but the fact that you can build a dat…

> Do you leave your windows open when you leave from home just because the burglar can kick the front door in instead?

Yes (or at least: I ain't terribly worried if I do forget to close the windows before leaving), because the likelihood of a burglar climbing up multiple floors to target my apartment's windows is far lower than the likelihood of the burglar breaking through my front door.

But I digress...

> You're missing the point and you're not thinking like a hacker yet.

I'd say the same about you. To a hacker who's sufficiently motivated to go through all those steps you describe, a lack of publicly-known autoincremented IDs is hardly an obstacle. It might deter the average script kiddie or botnet, but only for as long as they're unwilling to rewrite their scripts to iterate over alphanumerics instead of just numerics, and they ain't the ones performing attacks like you describe in the first place.

> For example your profile here is '/user?id=yellowapple' not '/user?id=1337'.

In either case that's public info, and it's straightforward to build a list of at least some subset of Hacker News users by other means (e.g. scraping comments/posts - which, mind you, are sequential IDs AFAICT). Yes, it's slightly more difficult than looping over user IDs directly, but not by a significant enough factor to be a worthwhile security mitigation, even in depth.

Unless someone like @dang feels inclined to correct me on this, I'm reasonably sure the decision to use usernames instead of internal IDs in HN profile URLs has very little to do with this particular "mitigation" and everything to do with convenience (and I wouldn't be all that surprised if the username is the primary key in the first place). '/user?id=worksonmine' is much easier to remember than '/user?id=8675309', be it for HN or for any other social network / forum / etc.

> Sometimes an incrementing ID is perfectly fine, but it's more difficult to shard across services so I usually default to UUID anyway except when I really want an incrementing ID.

Sharding is indeed a much more valid reason to refrain from autoincrementing IDs, publicly-known or otherwise.

Re: My notes on Gitlab's Postgres schema design (2022)

#143

Post by [deleted] »

yellowapple wrote: post #142
worksonmine wrote: post #136

Earlier quoted context omitted.

> Right, but like I suggested above, if you're able to get any response other than a 404 for an ID other than one you're authorized to access, then that in and of itself is a severe issue. So is being able to log in with that ID instead of an actual username. You're missing the point and you're not thinking like a hacker yet. It's not about the ID itself or even private profiles, but the fact that you can build a dat…

> Do you leave your windows open when you leave from home just because the burglar can kick the front door in instead? Yes (or at least: I ain't terribly worried if I do forget to close the windows before leaving), because the likelihood of a burglar climbing up multiple floors to target my apartment's windows is far lower than the likelihood of the burglar breaking through my front door. But I digress... > You're mi…

[deleted]

Re: My notes on Gitlab's Postgres schema design (2022)

#144

Post by yellowapple »

coldtea wrote: post #131
yellowapple wrote: post #70

> It is generally a good practice to not expose your primary keys to the external world. This is especially important when you use sequential auto-incrementing identifiers with type integer or bigint since they are guessable. What value would there be in preventing guessing? How would that even be possible if requests have to be authenticated in the first place? I see this "best practice" advocated often, but to me i…

> I see this "best practice" advocated often, but to me it reeks of security theater. The idea of "security theater" is overplayed. Security can be (and should be) multilayered, it doesn't have to be all or nothing. So that, when they break a layer (say the authentication), they shouldn't automatically gain easy access to the others > If an attacker is able to do anything useful with a guessed ID without being authen…

> Security can be (and should be) multilayered, it doesn't have to be all or nothing.

In this case the added layer is one of wet tissue paper, at best. Defense-in-depth is only effective when the different layers are actually somewhat secure in their own right.

It's like trying to argue that running encrypted data through ROT13 is worthwhile because "well it's another layer, right?".

> you'd be thanking all available Gods that you at least made the IDs not guassable - which would also give them also access to every user account on the system.

I wouldn't be thanking any gods, because no matter what those IDs look like, the only responsible thing in such a situation is to assume that an attacker does have access to every user account on the system. Moving from sequential IDs to something "hard" like UUIDs only delays the inevitable - and the extraordinarily narrow window in which that delay is actually relevant ain't worth considering in the grand scheme of things. Moving from sequential IDs to something like usernames ain't even really an improvement at all, but more of a tradeoff; yeah, you make life slightly harder for someone trying to target all users, but you also make life much easier for someone trying to target a specific user (since now the attacker can guess the username directly - say, based on other known accounts - instead of having to iterate through opaque IDs in the hopes of exposing said username).

Re: My notes on Gitlab's Postgres schema design (2022)

#145

Post by Anon1096 »

yellowapple wrote: post #127
serial_dev wrote: post #98

Earlier quoted context omitted.

> In gitlabs case this is much less relevant (...) Why, though? GitLab is often self hosted, so being able to iterate through objects, like users, can be useful for an attacker.

In my experience self-hosted GitLabs are rarely publicly-accessible in the first place; they're usually behind some sort of VPN. As for an attacker being able to iterate through users, if that information is supposed to be private, and yet an attacker is getting anything other than a 404, then that's a problem in and of itself and my energy would be better spent fixing that.

This is again a defense in depth thing. In the age of WFH, cracking a corporate VPN is really not that difficult. If you can make an attacker's life harder for low cost you should do it just in case.

Re: My notes on Gitlab's Postgres schema design (2022)

#146

Post by strzibny »

yellowapple wrote: post #70

> It is generally a good practice to not expose your primary keys to the external world. This is especially important when you use sequential auto-incrementing identifiers with type integer or bigint since they are guessable. What value would there be in preventing guessing? How would that even be possible if requests have to be authenticated in the first place? I see this "best practice" advocated often, but to me i…

Whether you think it's a real problem or not, if you want to solve it somehow, I compiled the current options one have in Rails apps:

https://nts.strzibny.name/alternative-bigint-id-identifiers-...

Re: My notes on Gitlab's Postgres schema design (2022)

#147

Post by worksonmine »

yellowapple wrote: post #142
worksonmine wrote: post #136

Earlier quoted context omitted.

> Right, but like I suggested above, if you're able to get any response other than a 404 for an ID other than one you're authorized to access, then that in and of itself is a severe issue. So is being able to log in with that ID instead of an actual username. You're missing the point and you're not thinking like a hacker yet. It's not about the ID itself or even private profiles, but the fact that you can build a dat…

> Do you leave your windows open when you leave from home just because the burglar can kick the front door in instead? Yes (or at least: I ain't terribly worried if I do forget to close the windows before leaving), because the likelihood of a burglar climbing up multiple floors to target my apartment's windows is far lower than the likelihood of the burglar breaking through my front door. But I digress... > You're mi…

> multiple floors

But not if you lived on the ground floor I assume?

> iterate over alphanumerics instead of just numerics

Why would you suggest another sequential ID as a solution to a sequential ID? I didn't, UUIDs have decent entropy and are not viable to brute force. Don't bastardize my point just to make a response.

> I'm reasonably sure the decision to use usernames instead of internal IDs in HN profile URLs has very little to do with this particular "mitigation" and everything to do with convenience

It wasn't intended as an audit of HN, I was holding your hand when walking you through an example and chose the site we're on. I missed my mark and I don't think a third attempt will do much difference when you're trying so hard not to get it. If you someday have public data you don't want a competitor to enumerate you'll know the solution exists.

Re: My notes on Gitlab's Postgres schema design (2022)

#148

Post by traceroute66 »

alex_smart wrote: post #124
traceroute66 wrote: post #113

Earlier quoted context omitted.

> They then also show that there is in fact a significant performance difference when you need to migrate your schema to accodomate a change in length of strings being stored. Which is a pointless demonstration if you RTFM and design your schema correctly, using text, just like the manual and the wiki tells you to. > the text type with CHECK constraint allows you to evolve the schema easily compared to character vary…

And what is wrong with someone figuring out for themselves and explaining to others why some suggestion makes sense logically, rather than just quoting the manual?

> And what is wrong with someone figuring out for themselves....

Whatever floats your boat, of course.

However, the Postgres manual is amongst the best written and most comprehensive in the industry.

Therefore wasting your time proving the Postgres documentation is correct is, well, a rather interesting hobby.

And if I'm expected to read waffle on someone's blog post, I would rather they tell me something I do not know, and not something obvious that is already very clearly and comprehensively stated in a million places, whether the manual, the wiki or elsewhere.

Finally, as I said, its old, VERY old news. So its not even like the author is highlighting something brand-new in the latest release. Its been that way basically forever at this stage.

Re: My notes on Gitlab's Postgres schema design (2022)

#149

Post by coldtea »

yellowapple wrote: post #144
coldtea wrote: post #131

Earlier quoted context omitted.

> I see this "best practice" advocated often, but to me it reeks of security theater. The idea of "security theater" is overplayed. Security can be (and should be) multilayered, it doesn't have to be all or nothing. So that, when they break a layer (say the authentication), they shouldn't automatically gain easy access to the others > If an attacker is able to do anything useful with a guessed ID without being authen…

> Security can be (and should be) multilayered, it doesn't have to be all or nothing. In this case the added layer is one of wet tissue paper, at best. Defense-in-depth is only effective when the different layers are actually somewhat secure in their own right. It's like trying to argue that running encrypted data through ROT13 is worthwhile because "well it's another layer, right?". > you'd be thanking all available…

>I wouldn't be thanking any gods, because no matter what those IDs look like, the only responsible thing in such a situation is to assume that an attacker does have access to every user account on the system. Moving from sequential IDs to something "hard" like UUIDs only delays the inevitable*

Well, there's nothing "inevitable". It's a computer system, not the fullfilment of some prophecy.

You can have an attack vector giving you access to a layer, without guaranteed magic access to other layers.

But even if it "just delays the inevitable", that's a very good thing, as it can be time used to patch the issue.

Not to mention, any kind of cryptography just "delays the inevitable" too. With enough time it can be broken with brute force - might not even take millions of years, as we could get better at quantum computing in the next 50 or 100.

Re: My notes on Gitlab's Postgres schema design (2022)

#150

Post by dalore »

yellowapple wrote: post #70

> It is generally a good practice to not expose your primary keys to the external world. This is especially important when you use sequential auto-incrementing identifiers with type integer or bigint since they are guessable. What value would there be in preventing guessing? How would that even be possible if requests have to be authenticated in the first place? I see this "best practice" advocated often, but to me i…

It's also exposes your growth metrics. When using sequential id's one can tell how many users you have, how many users a month you are getting and all sorts of useful stuff that you probably don't want to expose.

It's how the British worked out how many tanks the German army had.

Post reply on HN