61–70 of 81 posts
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 cann…
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?
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.
In your opinion. There are advantages of sharding tables by tenant boundaries. Data isolation and query speed to name a couple.
Earlier quoted context omitted.
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.
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.
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.
Google Analytics either doesn't do this or got really unlucky. But some day I had to troubleshoot an issue where the data for a totally unrelated website ended up in someone's GA data set. Not just the usual spam, but millions of visits to the wrong website which had an account ID very similar to the client's.
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.
Earlier quoted context omitted.
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.
with u as (
update organizations
set last_external_id = last_external_id + 1
where id = 123
returning last_external_id
)
insert into incidents (organization_id, external_id, title)
select 123, last_external_id, 'Blah blah blah'
from u;
Earlier quoted context omitted.
That may be your opinion, but an invoice in at least the Netherlands needs an monotonically increasing number. And it needs to be on the invoice and your customer needs to be able to see it. [edit] It's even EU-wide, see article 226(2) of directive 2006/112/EC: > a sequential number, based on one or more series, which uniquely identifies the invoice;
Nothing stops you from using the "...or more series" part to generate numbers that are specific to the day, the hour or, if you feel like it, the minute. Today's first invoice could be 2021-07-16-001, the second one 2021-07-16-002, etc. If you really don't want people to be able to guess your invoice volume from numbers alone, there are various ways to do that while still being compliant to EU laws.
The Italian authorities seem to see this the same way - https://vatdesk.eu/en/eu-vat-news/italy-mandatory-mentions-o...
(Annoyingly the directive doesn't give a definition for "a sequential number" itself.)