Обсуждение: (psuedo) random serial for PK or at least UNIQUE NOT NULL?
Hi all. I'm willing to change an BIGINT ID column (actually a SERIAL8) with a BIGINT whose valules are (pseudo)random. The main objective is to avoid guessability. I whish I could also use it as the PK (as it's now) but that's not really important now. Any hint? -- Vincenzo Romano NotOrAnd Information Technologies NON QVIETIS MARIBVS NAVTA PERITVS
Vincenzo Romano wrote: > Hi all. > > I'm willing to change an BIGINT ID column (actually a SERIAL8) with a > BIGINT whose valules are (pseudo)random. > The main objective is to avoid guessability. > I whish I could also use it as the PK (as it's now) but that's not > really important now. > Any hint? http://wiki.postgresql.org/wiki/Pseudo_encrypt -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
2010/5/4 Alvaro Herrera <alvherre@commandprompt.com>: > Vincenzo Romano wrote: >> Hi all. >> >> I'm willing to change an BIGINT ID column (actually a SERIAL8) with a >> BIGINT whose valules are (pseudo)random. >> The main objective is to avoid guessability. >> I whish I could also use it as the PK (as it's now) but that's not >> really important now. >> Any hint? > > http://wiki.postgresql.org/wiki/Pseudo_encrypt That's a nice starting point. I should apply that function twice for each 32-bit chunk of a 64-bit BIGINT and then merge the results into a single BIGINT ... Boring but doable! -- Vincenzo Romano NotOrAnd Information Technologies NON QVIETIS MARIBVS NAVTA PERITVS
2010/5/5 Vincenzo Romano <vincenzo.romano@notorand.it>: > 2010/5/4 Alvaro Herrera <alvherre@commandprompt.com>: >> Vincenzo Romano wrote: >>> Hi all. >>> >>> I'm willing to change an BIGINT ID column (actually a SERIAL8) with a >>> BIGINT whose valules are (pseudo)random. >>> The main objective is to avoid guessability. >>> I whish I could also use it as the PK (as it's now) but that's not >>> really important now. >>> Any hint? >> >> http://wiki.postgresql.org/wiki/Pseudo_encrypt > > That's a nice starting point. > I should apply that function twice for each 32-bit chunk of a > 64-bit BIGINT and then merge the results into a single BIGINT ... > Boring but doable! Hum, not sure. If I understand correctly, you won't be able to get the original int64 from the generated one. Better rewrite the function for 2^64 isn't it ? > > -- > Vincenzo Romano > NotOrAnd Information Technologies > NON QVIETIS MARIBVS NAVTA PERITVS > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Cédric Villemain
>>>>>> http://wiki.postgresql.org/wiki/Pseudo_encrypt >> That's a nice starting point. >> I should apply that function twice for each 32-bit chunk of a >> 64-bit BIGINT and then merge the results into a single BIGINT ... >> Boring but doable! > > Hum, not sure. If I understand correctly, you won't be able to get the > original int64 from the generated one. Why should I bother? > Better rewrite the function for 2^64 isn't it ? Sure!. -- Vincenzo Romano NotOrAnd Information Technologies NON QVIETIS MARIBVS NAVTA PERITVS
2010/5/5 Vincenzo Romano <vincenzo.romano@notorand.it>: >>>>>>> http://wiki.postgresql.org/wiki/Pseudo_encrypt >>> That's a nice starting point. >>> I should apply that function twice for each 32-bit chunk of a >>> 64-bit BIGINT and then merge the results into a single BIGINT ... >>> Boring but doable! >> >> Hum, not sure. If I understand correctly, you won't be able to get the >> original int64 from the generated one. > > Why should I bother? Dunno, but it was in the original spec. > >> Better rewrite the function for 2^64 isn't it ? > > Sure!. > > -- > Vincenzo Romano > NotOrAnd Information Technologies > NON QVIETIS MARIBVS NAVTA PERITVS > -- Cédric Villemain