Обсуждение: Sequence on a char(6) column
Is it possible to create a sequence like 'ABC012','ABC013', etc. in a char(6) column? I'm toying with some ideas... I actually need to generate random 6 digit membership number in the format 'CCCNNN' (where C is a character A-Z and D a digit 0-9). The idea is to have a number like a South African motorvehicle registration (also used elsewhere) in the format XYZ123. For security reasons a member number like this should be random and I'm wondering if anybody has done something like this in a pl/pgsql. I guess a procedure like this could be used as a default for a column, and making the column unique could prevent duplicates, although this should ideally be done as part of the generating procedure, not afterwards. A sample, or pointers would all be appreciated. Thanks Roland
On 12/14/05, Roland Giesler <roland@giesler.za.net> wrote:
> Is it possible to create a sequence like 'ABC012','ABC013', etc. in a
> char(6) column?
>
i guess you can do something like:
create sequence seq1;
create table tt (
...
fld1 char(6) default 'str' || nextval('seq1'),
...
);
--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
On Wed, Dec 14, 2005 at 16:23:14 +0200, Roland Giesler <roland@giesler.za.net> wrote: > Is it possible to create a sequence like 'ABC012','ABC013', etc. in a > char(6) column? > > I'm toying with some ideas... > > I actually need to generate random 6 digit membership number in the format > 'CCCNNN' (where C is a character A-Z and D a digit 0-9). The idea is to > have a number like a South African motorvehicle registration (also used > elsewhere) in the format XYZ123. For security reasons a member number like > this should be random and I'm wondering if anybody has done something like > this in a pl/pgsql. I guess a procedure like this could be used as a > default for a column, and making the column unique could prevent duplicates, > although this should ideally be done as part of the generating procedure, > not afterwards. 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.
On Wed, Dec 14, 2005 at 16:22:54 -0600, Bruno Wolff III <bruno@wolff.to> wrote: > On Wed, Dec 14, 2005 at 16:23:14 +0200, > Roland Giesler <roland@giesler.za.net> wrote: > > Is it possible to create a sequence like 'ABC012','ABC013', etc. in a > > char(6) column? > > > > I'm toying with some ideas... > > > > I actually need to generate random 6 digit membership number in the format > > 'CCCNNN' (where C is a character A-Z and D a digit 0-9). The idea is to > > have a number like a South African motorvehicle registration (also used > > elsewhere) in the format XYZ123. For security reasons a member number like > > this should be random and I'm wondering if anybody has done something like > > this in a pl/pgsql. I guess a procedure like this could be used as a > > default for a column, and making the column unique could prevent duplicates, > > although this should ideally be done as part of the generating procedure, > > not afterwards. > > 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.
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) Thanks in anticipation, Roland
On Thu, Dec 15, 2005 at 06:05:17 +0200, Roland Giesler <roland@giesler.za.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.