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

Postgres sequences can skip 32 unexpectedly

incident.io

Post reply on HN
Print view View original

41–50 of 81 posts

Re: Postgres sequences can skip 32 unexpectedly

#41

Post by jacobsenscott »

combatentropy wrote: post #5
bsder wrote: post #4

Isn't creating a sequence a bad idea in general, anyway? Aren't there a zillion ways to compromise things if you know that some field is a sequence?

Do you mean enumeration, whereby an attacker starts at some ID and tries several in sequence? It has never been a problem for me and my applications. Just because you know a record exists, doesn't mean you can see it. For example, if you are authorized to view https://www.example.com/records/100 , and you decide to try https://www.example.com/records/101 , then the code will check to see if you're authorized to see r…

I've certainly seen this be a problem - even if you and every future programmer who touches your code gets the authorization check right every time over the years the app is live, and over the hundreds or thousands of endpoints it exposes, plenty of apps don't get it right every time.

Using random id's is a nice and low effort additional layer of security there.

Re: Postgres sequences can skip 32 unexpectedly

#42

Post by advisedwang »

agent327 wrote: post #26

This is why you should never expose your database IDs to the customer. They just complain about it, and it invites them wanting to assign meaning and have control over the values.

You would run into this problem even if you aren't using the database number as your primary key. We don't want to force the user to generate the incident handle (slowing down incident creation) so a sequence of number is useful.

Generating the sequence in application logic can make it challenging to guarantee we don't duplicate numbers if two requests come in at once (you don't want locks/global synchronous state in the application if you can avoid it), so having the database generate them is a good fit.

Re: Postgres sequences can skip 32 unexpectedly

#43

Post by peterejhamilton »

agent327 wrote: post #26

This is why you should never expose your database IDs to the customer. They just complain about it, and it invites them wanting to assign meaning and have control over the values.

Hey, author here :)

Normally I'd agree with you, but in this case we're explicitly dealing with external IDs that we want to be incrementing.

Our internal IDs and API IDs actually follow a totally different approach, and hold no meaning other than as a reference :)

Re: Postgres sequences can skip 32 unexpectedly

#44

Post by peterejhamilton »

josep-panadero wrote: post #14

> Sequences felt like a good use case for this when we started, [...] We’ve since moved to a different approach which enforces the behaviour we want more explicitly when creating incidents, and we learned something along the way. The entire article shows that the author has a very good grasp as much of the technical side of development as of the business side. And the conclusion makes a lot of sense. Sequences are a…

Author here - thanks for the kind words. You're right, this was a case of pragmatic decisions revisited at a later date, something I'm a big fan of (although it would have been nice to not fall foul of the issue at all!)

We were very open, and our customers in this case were really understanding - just one of the reasons we love working with them!

Re: Postgres sequences can skip 32 unexpectedly

#45

Post by rcthompson »

adeel_siddiqui wrote: post #39

Nice write-up. Something does not add up here though. Primary writes 32 ahead to the WAL when fetched the first time, then keeps a counter (log_cnt) which it decreases each time nextval is called. So, when sequence was initialized, nextval is 1 and WAL has 32. The replica sees 32 as fetched offset. How does incident sequence switch from 7 to 39? Shouldn't it be 33 when the replica was made the primary? Same for incid…

Maybe it "tops up" the logged values whenever the database is idle?

Re: Postgres sequences can skip 32 unexpectedly

#46

Post by peterejhamilton »

leftnode wrote: post #33

We have a similar system (multi-tenant database) where each tenant (account) has objects that have unique identifiers for that specific account (customers, locations, jobs, invoices, etc). Customer #C1010 may have 2 locations #L1899 and #L8443 and many invoices #IN1940 and #IN2399 for example. When we first built the system, I considered using native Postgres sequences to track these, but decided against them because…

Nice - we're using a very similar approach now (procedure that runs just before creation) which I think will last us a good while. Glad to know it's worked out well for you :)

Re: Postgres sequences can skip 32 unexpectedly

#47

Post by dragontamer »

agent327 wrote: post #26

This is why you should never expose your database IDs to the customer. They just complain about it, and it invites them wanting to assign meaning and have control over the values.

Customer IDs should have at least one or two checksum digits to help spotcheck for data entry errors anyway.

Re: Postgres sequences can skip 32 unexpectedly

#48

Post by peterejhamilton »

CodesInChaos wrote: post #35

> we don’t just want a monotonically increasing sequence Be careful about treating sequences as monotonic. For transactions in progress at the same time, the order of sequence values might not be consistent with the order of transaction commits and the logical order of serializable transactions. One example where that could cause problems is if you filter a change stream using the last seen id. For such an approach a…

Super interesting, and that makes a lot of sense!

Luckily our _internal_ IDs don't rely on sequences at all, and for ordering I'd always use a field specifically for that purpose like `created_at` timestamps etc (vs inferring ordering from IDs). Best if IDs just remain references!

This could have been another interesting bug though, in a way glad we hit this one instead and that it's now fixed so we don't hit this one in future!

Re: Postgres sequences can skip 32 unexpectedly

#49

Post by peterejhamilton »

devit wrote: post #15

The initial design was quite flawed, in addition to not using sequences they should not use one DB object per organization, but rather a single object with an "organization" field.

Hey - author here! I'm not totally sure I follow on this one. Happy to chat more if there's any context missing from the article that you'd find interesting :)

Re: Postgres sequences can skip 32 unexpectedly

#50

Post by bluenose69 »

agent327 wrote: post #26

This is why you should never expose your database IDs to the customer. They just complain about it, and it invites them wanting to assign meaning and have control over the values.

Years ago, I bought some software. Its serial number had quite a few digits. I found out later that I had been the first customer. I guess the idea was that customers might be more confident to jump in the water, if they saw others there already.
Post reply on HN