Postgres sequences can skip 32 unexpectedly
incident.io
Postgres sequences can skip 32 unexpectedly
1–10 of 81 posts
Re: Postgres sequences can skip 32 unexpectedly
#2Thank you for uncovering this edge case. To summarize, Postgres reserves a batch of 32 serial numbers from its sequence objects. Then, in the case of a crash, or the promotion of a "follower", that batch is lost.
I consider ID numbers somewhat opaque, like GUIDs but maybe not that opaque. Fretting about gaps in ID numbers can cause hair loss. This is just one of many ways gaps can happen.
It is just an artifact of "sequences", the database object in Postgres that autogenerates the "next" ID number for a column --- automatically set up if you declare a column of type "serial", but that's all a serial column is. Serial just means: make the column of type integer, and create a sequence for it, and set the column's default value to nextval(sequence).
You can mitigate gaps somewhat, like if you're testing and retesting a bunch of inserts, rolling back each time. Well, after a few tests, the next number in the sequence is far beyond the last number in the table. So you can call another sequence function, setval, to reset it. Something like:
select setval('sequence_name', (select max(id) from table));
Re: Postgres sequences can skip 32 unexpectedly
#3Thank you for uncovering this edge case. To summarize, Postgres reserves a batch of 32 serial numbers from its sequence objects. Then, in the case of a crash, or the promotion of a "follower", that batch is lost. I consider ID numbers somewhat opaque, like GUIDs but maybe not that opaque. Fretting about gaps in ID numbers can cause hair loss. This is just one of many ways gaps can happen. It is just an artifact of "s…
btw. it's easy to use FOR UPDATE to create custom sequences in a table that do not have the batch reservation feature. it's just slower if you can afford that.
the problem with resetting the sequence is that it would need a full blown serilizable transaction which is worse thatn just using for update and read commited.
Re: Postgres sequences can skip 32 unexpectedly
#4Isn'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?
Re: Postgres sequences can skip 32 unexpectedly
#5Isn'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 record 101. If not, then you will get Unauthorized.
I suppose there are situations where it's a problem if someone finds out that record 101 even exists, but not in any of my apps.
Re: Postgres sequences can skip 32 unexpectedly
#6Thank you for uncovering this edge case. To summarize, Postgres reserves a batch of 32 serial numbers from its sequence objects. Then, in the case of a crash, or the promotion of a "follower", that batch is lost. I consider ID numbers somewhat opaque, like GUIDs but maybe not that opaque. Fretting about gaps in ID numbers can cause hair loss. This is just one of many ways gaps can happen. It is just an artifact of "s…
btw. it's easy to use FOR UPDATE to create custom sequences in a table that do not have the batch reservation feature. it's just slower if you can afford that. the problem with resetting the sequence is that it would need a full blown serilizable transaction which is worse thatn just using for update and read commited.
Right. It isn't something I recommend running in production before every insert. It's just something I have done sometimes, like as part of a big database change, and only to "reference" tables. Like suppose there is a table of 10 rows to populate a dropdown menu. Now I need to add an 11th, but for some reason the sequence got out of wack, and the next sequence value is 15. So I manually set it back to 11.
Re: Postgres sequences can skip 32 unexpectedly
#7Isn'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?
> Isn't creating a sequence a bad idea in general, anyway?
No:
- sequences are very common. I recommend using them on every table for mgmt. and internal efficiency reasons.
For example, with Innodb, if you don't have a numeric id as a PK, it will assign an invisible one for internal use anyway.
Most third-party tools won't allow you to manage tables without numeric PK's.
- in most large applications, most sequence ID's are only used internally
- for public display (your concern) uuids or random numbers are possible
Source: DBA.
Re: Postgres sequences can skip 32 unexpectedly
#8Isn'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 suppose there are situations where it's a problem if someone finds out that record 101 even exists, but not in any of my apps.
Well, it's things like say, an invoice number.
I can buy something from you. And then 7 days later I buy something else from you.
If the invoice numbers are in sequence, I just gained quite a bit of information about how fast you are selling things.
That's the kind of information leak that sequences can create.
Re: Postgres sequences can skip 32 unexpectedly
#9Isn'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?
Obscurity isn’t a substitute for security. You still need to have proper authentication and authorization in place.
Re: Postgres sequences can skip 32 unexpectedly
#10Isn'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?
Obscurity isn’t a substitute for security. You still need to have proper authentication and authorization in place.
It can still leak information, like if a companies customers have sequential id's and your id is 590 then it is reasonable to assume that the company has had around 500-600 customers.
It is usually not something to worry about, but in some cases you want to avoid leaking that info.