UUID, serial or identity columns for PostgreSQL auto-generated primary keys? • Hacker News Board

Re: UUID, serial or identity columns for PostgreSQL auto-generated primary keys?

#101

Post by nimish » Tue, Jun 01, 2021, 2:30 AM UTC

friend-monoid wrote: post #37

throwawayboise wrote: post #29

Earlier quoted context omitted.

I don't think a lot of the argument that integer IDs reveal too much. Yes, they are guessable but your application should not rely solely on the "secrecy" of the ID to authorize access to a record. If you are worried about someone crawling your public API with wget or curl and an incrementing counter you should re-think whether your data are really public or not, or maybe rate-limit anonymous users, etc. They also re…

I had this issue in a case I think is interesting; a customer had a database with incremental IDs of a certain product they sold. On a web platform, the product owner in turn could log in and view a list of their products and their status. The id of the product was part of the URL; /product/851. Of course, the product owners could not get any information on IDs they didn’t own, but the numbers gave away info on how m…

It's the german tank problem.

Serial IDs, with some light assumptions, leak information about the total count of items.

Re: UUID, serial or identity columns for PostgreSQL auto-generated primary keys?

#102

Post by [deleted] » Tue, Jun 01, 2021, 2:31 AM UTC

dragonwriter wrote: post #82

3pt14159 wrote: post #70

> Now, sometimes a table has a natural primary key, for example the social security number of a country’s citizens. You know, you think that, but it's never that simple. The field was added incorrectly and nobody noticed until the value is in countless tables that you now need to simultaneously update or the value is something that's supposed to be semi-secret, so now a low level support staff can't reference the row…

> > Now, sometimes a table has a natural primary key, for example the social security number of a country’s citizens. > You know, you think that, but it's never that simple. It’s that simple if you’re the Social Security Administration and its a table of Social Security Accounts, not people. Other than that, using SSNs as a primary key is just plain wrong.

[deleted]

Re: UUID, serial or identity columns for PostgreSQL auto-generated primary keys?

#103

Post by foresto » Tue, Jun 01, 2021, 2:41 AM UTC

I once pondered how I might generate IDs that were as compact as a machine word, without a value (or small set of values) revealing the size of the data set. One application might be user-visible customer numbers that don't easily reveal how many customers there are.

I eventually came across the idea of using maximal period linear-feedback shift registers to transform an integer variable through every possible value (minus one), but in a non-incremental sequence that depends on the LFSR arrangement.

I never ended up putting the idea to use, but I've always been curious about people who have and how it worked out for them. [Edit to clarify: It was meant for obfuscation, not security against a determined attacker.]

Re: UUID, serial or identity columns for PostgreSQL auto-generated primary keys?

#104

Post by [deleted] » Tue, Jun 01, 2021, 2:44 AM UTC

cratermoon wrote: post #54

Postgres (and other relational DBs) really need to implement something like snowflake[1] or ksuid[2] 1 https://blog.twitter.com/engineering/en_us/a/2010/announcing... 2 https://segment.com/blog/a-brief-history-of-the-uuid/

[deleted]

Re: UUID, serial or identity columns for PostgreSQL auto-generated primary keys?

#105

Post by slver » Tue, Jun 01, 2021, 2:58 AM UTC

foresto wrote: post #103

I once pondered how I might generate IDs that were as compact as a machine word, without a value (or small set of values) revealing the size of the data set. One application might be user-visible customer numbers that don't easily reveal how many customers there are. I eventually came across the idea of using maximal period linear-feedback shift registers to transform an integer variable through every possible value…

The problem is that if your encoding algorithm leaks, it’s game over.

Re: UUID, serial or identity columns for PostgreSQL auto-generated primary keys?

#106

Post by Thiez » Tue, Jun 01, 2021, 3:19 AM UTC

3pt14159 wrote: post #70

> Now, sometimes a table has a natural primary key, for example the social security number of a country’s citizens. You know, you think that, but it's never that simple. The field was added incorrectly and nobody noticed until the value is in countless tables that you now need to simultaneously update or the value is something that's supposed to be semi-secret, so now a low level support staff can't reference the row…

In the Netherlands SSNs are not unique, they handed out some duplicate ones back in the day. So not great as a primary key. Besides, I think using them as primary keys is illegal anyway.

Re: UUID, serial or identity columns for PostgreSQL auto-generated primary keys?

#107

Post by BenjiWiebe » Tue, Jun 01, 2021, 3:28 AM UTC

NoNotTheDuo wrote: post #88

rsync wrote: post #85

I have no particular expertise with modern databases and it has been decades since I did any work as a DBA. However, I cannot imagine creating table entries without a datestamp. No matter what else you are doing, or what you index by, I would want YYYY-MM-DD_HH-MM-SS in every row. Maybe I'm just weird that way ...

And ideally there is a created time stamp and a last updated time stamp.

(at least in my cases) Ideally nothing ever gets updated, there's just a newer version of the row.

Re: UUID, serial or identity columns for PostgreSQL auto-generated primary keys?

#108

Post by BenjiWiebe » Tue, Jun 01, 2021, 3:30 AM UTC

nimish wrote: post #101

friend-monoid wrote: post #37

Earlier quoted context omitted.

I had this issue in a case I think is interesting; a customer had a database with incremental IDs of a certain product they sold. On a web platform, the product owner in turn could log in and view a list of their products and their status. The id of the product was part of the URL; /product/851. Of course, the product owners could not get any information on IDs they didn’t own, but the numbers gave away info on how m…

It's the german tank problem. Serial IDs, with some light assumptions, leak information about the total count of items.

Just pick a random number at the beginning, and start incrementing IDs from there. Like personal checks starting at 1000 so they're always(ish) 4 digit. Of course, maybe pick another starting number that's less obvious.

Re: UUID, serial or identity columns for PostgreSQL auto-generated primary keys?

#109

Post by dpifke » Tue, Jun 01, 2021, 3:38 AM UTC

foresto wrote: post #103

I once pondered how I might generate IDs that were as compact as a machine word, without a value (or small set of values) revealing the size of the data set. One application might be user-visible customer numbers that don't easily reveal how many customers there are. I eventually came across the idea of using maximal period linear-feedback shift registers to transform an integer variable through every possible value…

I've used a small block cipher like Skip32 or Speck to obfuscate database sequences, either on INSERT or as part of the encoding scheme.

This works well against the German Tank Problem when there's no oracle allowing an attacker to guess lots of IDs quickly (such as when there are reasonable rate limits). It does not provide enough entropy when such an oracle exists (especially an offline one).

For something like a password reset token, it still needs to be paired with suitably random bytes.

Re: UUID, serial or identity columns for PostgreSQL auto-generated primary keys?

#110

Post by Izkata » Tue, Jun 01, 2021, 3:45 AM UTC

dragonwriter wrote: post #82

3pt14159 wrote: post #70

> Now, sometimes a table has a natural primary key, for example the social security number of a country’s citizens. You know, you think that, but it's never that simple. The field was added incorrectly and nobody noticed until the value is in countless tables that you now need to simultaneously update or the value is something that's supposed to be semi-secret, so now a low level support staff can't reference the row…

> > Now, sometimes a table has a natural primary key, for example the social security number of a country’s citizens. > You know, you think that, but it's never that simple. It’s that simple if you’re the Social Security Administration and its a table of Social Security Accounts, not people. Other than that, using SSNs as a primary key is just plain wrong.

> It’s that simple if you’re the Social Security Administration and its a table of Social Security Accounts, not people.

Nah, they keep track of duplicate usage: https://www.nbcnews.com/technolog/odds-someone-else-has-your...

> The IRS often knows when this happens, when the imposter pays taxes. The Social Security Administration knows, too, for the same reason. And the nation's credit bureaus usually know, because the imposter often ends up applying for some form of credit. Plenty of financial institutions also have access to this information.