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

51–60 of 81 posts

Re: Postgres sequences can skip 32 unexpectedly

#51

Post by leftnode »

peterejhamilton wrote: post #46
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 :)

Another added benefit is that you can build a simple interface to allow end users to adjust their sequences (or our support staff in this instance).

In our system, by default, all objects start at 1000. If a new account is created, and they want to increase a sequence to some value (say they already have 5000 invoices in QuickBooks and they want to start all new invoices at 10000 so they know every invoice #IN10000 and higher was created in our system), we have a simple interface that one of our support staff can go to arbitrarily increase the next value.

Re: Postgres sequences can skip 32 unexpectedly

#52

Post by peterejhamilton »

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?

Hey, author here :)

I don't think they're always a bad idea, but when talking about external IDs many folks would agree with you, in a few ways, actually.

Relying on exposed primary IDs being ordered, and/or exposing them, is often a bit of a can of worms.

A common case is that you leak information about your company because people can see how quickly you're growing. I've seen this in a few products I use and it's always interesting when you take an action a few days apart and can see how much volume they're doing.

TIL: German Tank Problem, thanks @teddyh! I first came across it with a good story about someone buying Donuts/Coffee in a shop and using the receipt numbers to estimate yesterday's sales, can't find the link, though :(

In this instance it's not our primary ID field - those are internal, and are long and random. They holds no meaning other than being a reference, so aren't used for sorting or exposed to the customer, and even if it was, it wouldn't mean anything or confer any information.

The IDs I refer to the in the article are more like external references. References we _explicitly_ want to increment by 1 each time.

For those referencing invoices as a parallel, it's a good equivalent. I'm not familiar with the details in the comments below, but if I created two invoices and they were referenced #1 and #33, I'd be quite confused (which is a version of what our customers felt/experienced here).

IMO It's also often a good idea to use different external IDs to your internal ones in APIs too, and ideally have no meaning attached to them, either. That way users don't do things like assume "record 99" was created before "record 100", and you can also move data around and migrate things, so long as you honour those external references (i.e., you can change the type/format of your internal IDs at will).

Re: Postgres sequences can skip 32 unexpectedly

#54

Post by locao »

bluenose69 wrote: post #50
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.

About 15 years ago I was part of a team of four developing a VoIP platform. When we were about to release we realized that our first customer would be upset if they knew they were actually the first customer. So each one of us said a number and our first user had an ID similar to 1003912 :)

Re: Postgres sequences can skip 32 unexpectedly

#55

Post by worble »

jacobsenscott wrote: post #41
combatentropy wrote: post #5

Earlier quoted context omitted.

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.

If you're not doing your authorization properly, then no, basic ID obfuscation is not an extra layer of security. If anything, the fact that some people might gloss over the glaring security issues because "well its random ids so I never decided to check" is worse, an incremental id can at least be easily tested while developing to make sure, or a nice white hat hacker might notice and let you know.

Re: Postgres sequences can skip 32 unexpectedly

#56

Post by wongarsu »

peterejhamilton wrote: post #48
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 on…

Chances are that your created_at timestamps reflect transaction start, not transaction commit. That would leave you open to issues as described by GP as well

Re: Postgres sequences can skip 32 unexpectedly

#57

Post by CodesInChaos »

peterejhamilton wrote: post #48
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 on…

Timestamps have monotonicity issues as well:

1. If you create it in the application, clocks need to be synced well enough between all application servers. If you create it in the database, this shouldn't be an issue.

2. The transaction completes some time after the the timestamp was created, and that time can vary between concurrent transactions. This is a fundamental problem.

The MAX + 1 approach should guarantee strict monotonicity, but might lead to scalability issues for highly contented counters.

Re: Postgres sequences can skip 32 unexpectedly

#58

Post by garblegarble »

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…

>We have an event in our ORM to automatically generate the next sequence value as part of the transaction so if the transaction is rolled back, the next sequence value is as well.

I assume that means you also have to only allow one TX to be in-flight at a time adding a new record whose ID is generated from a given sequence?

Re: Postgres sequences can skip 32 unexpectedly

#59

Post by eyelidlessness »

bluenose69 wrote: post #50
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.

This is a technique used in other ways too. For instance, when opening a new checking account, it’s common to start with an arbitrary number for the first check. And often new businesses will start with an arbitrary invoice number.

Re: Postgres sequences can skip 32 unexpectedly

#60

Post by CapriciousCptl »

Good writeup! It's an interesting gotcha because Postgres and SQLite docs expressly disclaim that their sequences/AUTOINCREMENT are gapless but experienced and talented programmers still use them as such. Is the type of thing that doesn't bite you until production.

Postgres docs-- https://www.postgresql.org/docs/13/sql-createsequence.html > Because nextval and setval calls are never rolled back, sequence objects cannot be used if “gapless” assignment of sequence numbers is needed. It is possible to build gapless assignment by using exclusive locking of a table containing a counter; but this solution is much more expensive than sequence objects, especially if many transactions need sequence numbers concurrently.

Post reply on HN