Randomize DB record IDs
encryption, postgresql, random, ruby-on-rails, uuid
Solution
Note: this answers the initial version of the question, from which it was not obvious that this is not a replacement for authorization logic.
This is a wrong solution to a wrong problem.
You think the problem is: users can guess ids of "unlisted" things and use them.
Actual problem is: users can access things without authorization.
Put authorization logic in place, allow user access only to items that he can legitimately access and forbid everything else.
Also
hide how many entries there are in the DB
I think there's no shame in being small, if this is the reason. Anyway, you can start your sequence from 100000 or increment it by N or employ another similar trick :)
Problem
In our web application we want to randomize the record IDs. The reason is because we want to hide how many entries there are in the DB already and we have unlisted things. In case IDs would be simple incremental numbers it would be easy to guess the IDs of unlisted things. As I see it there are three ways to do this: Simple Random Numbers Algorithm: - Create a random number on insert. - Check if the ID is already in use. If yes goto 1. - Use this ID. Pro - easy - works with any size or type of the ID (32bit, 64bit, var. length, strings) Contra - needs a transaction for possible race conditions (algorithm is not atomic) UUIDs Pro - the likelihood of collisions is so low that you can ignore it Contra - we want to have nice short URLs with the page title as an URL comment (`"#{id}--#{page_title}`), a UUID would shift this comment all the way to the right - I guess UUIDs as primary keys would have a lower performance on joins? Encrypted IDs Algorithm: - Read number from a sequence using `nextval` (atomic!) - Encrypt the ID using a secret key and an encryption algorithm that works with the size used for the ID Pro - no race conditions (no transactions necessary) Contra - size of the ID column can never be changed - if someone can crack/guess the key all was for nothing Time-stamps Suggested by @emboss Pro - easy - will not run out of IDs Contra - might produce collisions (although one needs to test if it really happens) - maybe somewhat guessable Random Public ID/Name Based Public ID Suggested by @viktor tron A second ID for all things that occur in an URL only used to find the record. Internally normal IDs are used (for joins etc). Pro - internally everything stays sane - a good random algorithm/the naming scheme should make URL guessing impossible (enough) Contra - change a lot of things that use the IDs in public interfaces - a user might expect that they can cut down URLs with such titles in them, but in this case the URLs won't work anymore I think I'll use the third option. Or are there more arguments against it? Is there an even better solution? We use Ruby on Rails 3.x and PostgreSQL 9.x. Edit: Unlisted does not mean private! It is meant like unlisted videos on YouTube. They are normal videos that just aren't listed in searches or the uploader's profile. So you can't really find them (without trying every possible ID), but everyone who knows the URL can access them. Of course a user that makes something unlisted and sends the link to someone else has to be aware that it might not stay unknown (the URL may be passed on and through linking might end up in a search engine). We also have another option to make things private. These are two different things. (I see that assuming that everyone knows what "unlisted" means was a mistake.)