If I declare a column to be serial in PostgreSql, it will auto-increment in a ordinal sequence. The end result may not be contigious due to rollbacks and whatnot, but it's more or less ordinal.

I have a user table where the user id's are set as serial, and are thus incremented starting from 1... but I'd rather they weren't, as it seems like asking for a sequence number attack of some kind.

Is there a relatively easy, relatively performant way to create an automatic artificial ID in PostgreSQL such that the ID's are randomly spaced apart, and you shouldn't expect that ID 987654321 follows ID 987654320?

  • A uuid would do that. – a_horse_with_no_name Jan 10 '18 at 19:34
  • Yeah but the id is numeric in all the fk references and entities... that would be a much bigger project. – Jeremy Holovacs Jan 10 '18 at 19:45
  • 3
    You could use a normal serial but hide it and make public only obfuscated values, created from the hidden ones. – ypercubeᵀᴹ Jan 10 '18 at 19:57

but I'd rather they weren't, as it seems like asking for a sequence number attack of some kind

Aside from the fact that I think you are giving yourself a false sense of security, you can do what you want by reserving the upper half of the identifier for the 'real' sequence and filling the lower half with random bits:

create sequence seq;
create table tab(
  foo bigint 
      default (nextval('seq')<<32)+(random()*(1<<32))::integer
      primary key
insert into tab select from generate_series(1,9);
select foo, foo>>32 seq from tab;
        foo | seq
----------: | --:
 4294967296 |   1
 8589934592 |   2
12884901889 |   3
17179869185 |   4
21474836481 |   5
25769803777 |   6
30064771073 |   7
34359738369 |   8
38654705664 |   9

dbfiddle here


There is no reason to ever want this. Some people think it adds something to security. They're absolutely wrong. Don't be one of those people.

If you absolutely must do something like this, own it. Generate a table that has 4.2 billion ids and randomize the order of it.

CREATE TABLE stupid_false_security(id, stupidId)
  SELECT id,
    row_number() OVER (ORDER BY random()) AS stupidId
  FROM generate_series(1,5)
    AS gs(id)
  ORDER BY id;

I think any of these methods are at least a step in the right direction, they're less insane.

  • hashids takes your number and returns a nifty alphanumeric id that resolves to it.
  • base64/base32 encoding
  • uuids

Comparison with Jack Douglas's Answer

Using the MATHS there is an idea called a surjective function. It means you're mapping one domain onto another domain. Here is an example in decimal:

f(x) = x + y;  --impure y
x = truncate(f(x));

Is true for all integers of x such that 0<y<1. So if you simply truncate down you can get back to x. This doesn't add any security,

f(42) = 42.4
f(42) = 42.000001
f(42) = 42.0588
f(42) = 42.48787

Given the output, what is x for all of these? Hint: It's 42. Jack's answer pick's a random y adds it to the input in the same fashion (but in binary) and stores it in the database. If that's what you want, then have at it (also may be important to point out that Jack's sequence is also ordinal with one coming after the other even if it's non-consecutive).

My answer takes a list of all values in the domain of int4. Randomizes the order, and stores them in a table. Without that table you can never get back to your original sequential ids: it's just a shallow and useless obfuscation table that you'll like ditch or not implement because it's pretty silly. But you can never get the original value without that table.

  • Well, I think you're wrong that's there's no reason to ever want it. Granted, it doesn't give a lot of value to security, but it does discourage probes to some degree, especially if not every request made is restricted to a specific user for various reasons; it also prevents the somewhat computer-savvy people from investigating approximately how many records of a certain type you have. My preference is definitely uuids, but like I said earlier, that would require a lot of dependent components to change. Thanks for your input. – Jeremy Holovacs Jan 14 '18 at 22:00
  • It's not a function of disagreement. It's a fact of security. It's method of obfuscation. Shy of sheer obfuscation it provides nothing. Obfuscation doesn't do anything. I'm on three sites with sequential ids, Twitter, Reddit, and StackExchange. Have at it with your probes. =) I'm not sure who started this trend, but it's not even microoptimization: it's simply not rooted in any facts at all. Respectfully. – Evan Carroll Jan 14 '18 at 22:07
  • It certainly is obfuscation, and I agree that "security through obscurity" is definitely not security; That being said, I don't know about anyone else, but when I see an endpoint in a website going to /groups/6, I will often go check out what I can find at /groups/5 and /groups/7... even if I have no business with them. Also, would you prefer to accept a personal check numbered 0004 or 3274? I'm just suggesting there are legitimate reasons to want to do this. – Jeremy Holovacs Jan 14 '18 at 22:13
  • Yes, there are always reasons to do something, not knocking those non-technical reasons. I don't let my users access things irrelevant of mucking with inputs params. If they can, there is a total failure on my part. When I'm trying to figure something out, I don't let obfuscation tactics stop me either (you can look at my questions here on SQL Server internals for proof of that). I would never do this to my data. I don't want to obfuscate my data for social reasons. Feel free to ask around though. – Evan Carroll Jan 14 '18 at 22:31
  • 1
    I like the explanation, and for the most part I agree with you; I mainly took exception to your absolutist declaration at the beginning of your answer. There is little technical value in doing this sort of obfuscation (and a lot of headache) but human nature should be taken into consideration. It's a little like putting a broken padlock on a gate; to the casual observer, the gate looks locked, and less of a target of opportunity, even if there's no actual impediment. This is why I asked if there was a relatively easy, relatively performant way of doing this. Thanks! – Jeremy Holovacs Jan 16 '18 at 15:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.