Re: Sequence on a char(6) column

On Thu, Dec 15, 2005 at 06:05:17 +0200,
Roland Giesler <roland(at)giesler(dot)za(dot)net> wrote:
> Bruno Wolff III wrote:
> >> One option is to use a cipher (such as AES) in counter mode (you need
> >> to keep the key secret, but that shouldn't be a significant
> >> additional risk as you are keeping the ids in the database) and use
> >> the encrypted string to generate the string. The counter can be a
> >> sequence. You won't get duplicates as longer as the counter isn't
> >> reset and you don't change the key.
> >
> > I forgot something important. For there not to be duplicates
> > the function from the encrypted string to the member id needs
> > to be 1-1 (injective).
> > Your member code has just over 24 bits of information, so you
> > want to find a cipher that works on blocks of 24 bits. You
> > might have trouble finding strong ciphers that work on 24 bit blocks.
>
> I'm sorry, Bruno, I'm not following. I will probably understand what you
> mean if fill in a little more detail. The pgsql terminology is still a bit
> foreign to me, would you mind just elaborating a little on what you are
> proposing? (Maybe I'm just a bit thick-heading about this. It may dawn on
> me soon)

The idea is to encrypt the sequence number and use the output to generate
you member id. As long as all of the output bits in the encrypted string
are used, you won't get any duplicates. However, since your member id only
has 24 bits, you need to use a cipher that operates on 24 bit blocks, which
is not at all standard. (Typical block sizes are 256, 128 or 64 bits.)
There might be something out there that will work with variable block sizes.
If you don't need very high security, you could do something with linear
feedback shift registers. If people get to see a few customer ids, and have
some idea of what you are doing they will probably be able to figure out the
LFSR you are using.
If you customer base is way less than 17 million and the security requirements
are not high, you might just assign random ids using postgres' random
function and retry if you generate a duplicate.