Hi Rafal: On Tue, Jul 21, 2015 at 12:43 PM, Rafal Pietrak <rafal@xxxxxxxxx> wrote: > W dniu 21.07.2015 o 09:34, Francisco Olarte pisze: >> In this case I think you are mixing vouchers with voucher-numbers. IMO >> you could get a better dessign by using an auxiliary table and not >> nullifying the number after been consumed. Having only 6 digits I > Hmmm. I don't think so. .... > The application just needs to create a unique (for a period of time) > number, and "consume" it at certain point. Everything else would be > "implementation burden", which should be kept to minimum. I see your points, totally opposite opinions, so no point in discussing it, discard my sugestions as not aplicable SVP. .... > This looks a bit like an overkill for the above examples. It certainly is for your style of dessign, workng target, discard it. > But I have other thoughts on the use of cryptographic sequences here. I wouldn't call it that, its misleading. It's just encrypted sequences. > It > has the pitfall of being sensitive to out-of-the-sequence poisoning, I > mean: When another instance of an application starts issuing another > sequence of vouchers, at certain point those sequences collide and > applications despite using "guaranteed lack of collisions" will have a > collision. Well, if you have aplication instance specific sequences, of course you have. But in this case even plain unencrypted sequences hae them. > So the application *will have to have* a re-issuing of an > INSERT implemented anyway. Of course, because the only point of using instance specific sequences instead of serial like you normally do must be having the possibility of collisions to justify a the existence of a re-issuing code and exercise it. > If so, the whole point of using cryptographic > sequence is missing. No. The whole point of using a global sequence ( in the db ) is avoiding collisions, be it encrypted or plain. The whole point of using crypto is to make it look random. If you use an application specific cryptographic sequence is because you want colisions ( success, as told above ) which looks random ( success too ). If you do not want colisions, use a global sequence. > So, even though this collision is not statistically > significant, but just its possibility results in that application have > to take care of re-issuing of an INSERT. I use to tell people there are three meaninful cardinalities in computing, zero, one and many. And three probabilities ( NOT possibilities ), zero, one and other==(0,1). Except in some lucky domains you have to trat every (0,1) probability as been possible ( in fact my three probability values map nicely to impossible, possible and certain ). > Using database.sequence() function to seed the cypher is not secure enough. What are you talking about? Where did you get that seeding idea? You do not seed the cipher, you use the ciphered sequence as voucher. In fact I've done this with session ids. I use a sequence for the ID and send the user the ciphered val. When it comes back I just decipher it and search. I did not have your 6-digit problems, so I just used 128 bit blocks, and it worked nicely. And I did not have any ciphered data in the DB. > On the other hand, the "ON CONFLICT RETRY" has a nice feature for an > application programmer (like myself) that it leaves us free of the > implementation of the re-issue of an INSERT. One database-schema > designer does that for all of us. > But knowing if that usage scenario is too rare to match the heavy > lifting the implementation required, is beyond my experience. Saying OCR is a nice feature is like saying MAGIC RAINBOW OVERDRIVE is a nice feature for a car. It does not exist, and nobody has described it with enough detail so people can assses its usefulness or implementation difficulty. A careful definition of the corner case will be needed. And even with it you have the possibility of inifinite colisions ( either due to generating too many 'vouchers' or to sheer bad luck ( like collisions among application instances ). If you try to write a nicely wrapped up description of the functionality maybe someone could see the usefulness and implement it, but I think this is possible but unlikely. Francisco Olarte. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
