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

121–130 of 170 posts

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

#121

Post by heyoni »

imiric wrote: post #103
heyoni wrote: post #99

Earlier quoted context omitted.

Delusional? Anecdotal maybe…I was describing my experience so thanks for elaborating. I only use it as a code repository. Was it specific services within GitHub that failed a lot?

My apologies, that came off a bit harsh. > Was it specific services within GitHub that failed a lot? Well, everything from its frontends, to the CI service, to the Git service itself. People weren't able to push, pull, deploy or review code for hours at a time. Just major disruptions all around, which happened regularly. I do think this has improved slightly over the past few months, but you were lucky if you weren't…

No worries you’re fine. I guess downtime was much more widespread than I could even imagine.

I do remember one time all of us at work joking that we should get the day off when GitHub was done. :D

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

#122

Post by sjwhevvvvvsj »

doctor_eval wrote: post #100
thom wrote: post #97

Earlier quoted context omitted.

It wasn’t always code first - you mention Hibernate but 15-20 years ago it was entirely feasible to inherit a database schema or design one up front, and then create performant metadata mappings to a usable object model. That sort of practise was tainted by the Bad Enterprise brushes of verbosity and XML in general, and so everyone moved to some flavour of active record. This allowed programmers to write less code an…

You are absolutely correct; my previous business ended up ejecting almost all database logic from Java ORMs, and moved almost everything to straight SQL manipulated by stored procedures. Doing so resulted in a significant performance and productivity increase, relative to all the layers of nonsense we used to jump through. One of the difficulties of doing this was that the tooling isn’t great. We had to write our own…

A simple function call as a wrapper for a well optimized sql query just can’t be beat for performance. I have never understood why anybody would use an ORM, it’s usually as much work to learn and maintain them as SQL.

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

#123

Post by remus »

serial_dev wrote: post #98
remus wrote: post #81

Earlier quoted context omitted.

In general it's a defense-in-depth thing. You definitely shouldn't be relying on it, but as an attacker it just makes your life a bit harder if it's not straightforward to work out object IDs. For example, imagine you're poking around a system that uses incrementing ints as public identifiers. Immediately, you can make a good guess that there's probably going to be some high privileged users with user_id=1..100 so yo…

> 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.

You're right, fair point.

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

#124

Post by alex_smart »

traceroute66 wrote: post #113
alex_smart wrote: post #105

Earlier quoted context omitted.

>The author effectively wastes many words trying to prove a non-existent performance difference and then concludes "there is not much performance difference between the two types". 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. Altering a table to a change a column from varchar(300) to varcha…

> 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?

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

#125

Post by tomnipotent »

JimBlackwood wrote: post #107
tomnipotent wrote: post #85

Earlier quoted context omitted.

> extremely useful for my employer. I've been in these shoes before, and finding this information doesn't help you as an executive or leader make any better decisions than you could have before you had the data. No important decision is going to be swayed by something like this, and any decision that is probably wasn't important. Knowing how many orders is placed isn't so useful without average order value or items p…

That’s not correct. Not every market is the same in it’s dynamics. Yes, most of the time that information was purely insightful and was simply monitored. However, at some moments it definitely drove important decisions.

Like what?

What's going to change how a team develops (physical) products? What's a merchandiser or buyer going to learn that influences how they spend millions of dollars or deal with X weeks-on-hand of existing inventory? What's an operations manager going to learn that improves their ability to warehouse and ship product? How's marketing going to change their strategy around segmentation or channel distribution? What's a CEO going to learn that changes what departments or activities they want to invest in?

At best you get a few little tidbits of data you can include in presentations or board decks, but nothing that's going to influence critical decisions on how money is getting spent to get the job done or how time is getting allocated to projects. Worst case you have a inexperienced CEO that's chasing rather than leading, and just end up copying superficial aspects of your competitors without the context or understanding of why they did what they did.

I've called up execs at competitors and had friendly chats that revealed more about their business in 30 minutes than you could possibly find out through this "method".

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

#126

Post by oohffyvfg »

mnahkies wrote: post #78
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 can be really handy for scraping/archiving websites if they're kind enough to use a guessable id

[flagged]

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

#127

Post by yellowapple »

serial_dev wrote: post #98
remus wrote: post #81

Earlier quoted context omitted.

In general it's a defense-in-depth thing. You definitely shouldn't be relying on it, but as an attacker it just makes your life a bit harder if it's not straightforward to work out object IDs. For example, imagine you're poking around a system that uses incrementing ints as public identifiers. Immediately, you can make a good guess that there's probably going to be some high privileged users with user_id=1..100 so yo…

> 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.

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

#128

Post by oohffyvfg »

bluerooibos wrote: post #31

Has anyone written about or noticed the performance differences between Gitlab and GitHub? They're both Rails-based applications but I find page load times on Gitlab in general to be horrific compared to GitHub.

> compared to GitHub.

this is like comparing chrome and other browsers, even chromium based.

chrome and github will employ all tricks in the book, even if they screw you. for example, how many hours of despair I've wasted when manually dissecting a git history on employer github by opening merge diffs, hitting ctrl F, seeing no results and moving to the next... only to find on the 100th diff that deep down the diff lost they hid the most important file because it was more convenient for them (so one team lead could hit some page load metric and get a promotion)

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

#129

Post by yellowapple »

worksonmine wrote: post #94
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…

> What value would there be in preventing guessing? It prevents enumeration, which may or may not be a problem depending on the data. If you want to build a database of user profiles it's much easier with incremental IDs than UUID. It is at least a data leak but can be a security issue. Imagine a server doing wrong password correctly returning "invalid username OR password" to prevent enumeration. If you can still cr…

> 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 username.

Hackers are indeed creative, but they ain't wizards. There are countless other things that would need to go horribly horribly wrong for an autoincrementing ID to be useful in an attack, and the lack of autoincrementing IDs doesn't really do much in practice to hinder an attacker once those things have gone horribly, horribly wrong.

I can think of maybe one exception to this, and that's with e-commerce sites providing guest users with URLs to their order/shipping information after checkout. Even this is straightforward to mitigate (e.g. by generating a random token for each order and requiring it as a URL parameter), and is entirely inapplicable to something like GitLab.

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

#130

Post by yellowapple »

dijit wrote: post #92
tetha wrote: post #79

Earlier quoted context omitted.

> I see this "best practice" advocated often, but to me it reeks of security theater. If an attacker is able to do anything useful with a guessed ID without being authenticated and authorized to do so, then something else has gone horribly, horribly, horribly wrong and that should be the focus of one's energy instead of adding needless complexity to the schema. Yes, but the ability to guess IDs can make this security…

Case in point, a recent security issue Gitlab experienced (CVE-2023-7028; arbitrary password reset by knowing one of the accounts associated mail addresses) was made worse by a feature of gitlab that few people know about; that the "userID" is associated with a meta/internal mail address. This meant that people could send password resets for any user if they knew their userID. The mail format was like user-1@no-reply…

Even without that auto-incrementing ID, there are plenty of other options for guessing valid email addresses to use with that exploit. For example, if you're able to figure out the format an organization uses for their email addresses (e.g. first.last@company.com), and you're able to figure out who works at that org (via e.g. LinkedIn), then there's a very good chance you can reset passwords for, say, the company's CTO or other likely-highly-privileged users.

That is: this kind of proves my point. Removing autoincrementing IDs from the equation is of minimal benefit when things have already gone horribly horribly wrong like this. It's a little bit more work on the attacker's part, but not by anywhere near enough for such a "mitigation" to be of much practical benefit.

Post reply on HN