Horton

Thread

  1. Guarantee uniqueness of PGP key

    andre <andre@liquidprivacy.net> — 2002-05-20T20:17:09Z

    Hi,

More generically, how do I best guarantee that any large varchar is unique 
before inserting?

In my application, I need to ensure that each e-mail address and 
corresponding PGP public key are both unique.

I originally implemented this by adding a uniqueness contrainst to both 
columns.  easy.

The problem is that some PGP keys are over 2713 bytes, and postgres (7.2) 
gives the error:

ERROR: btree: index item size 4336 exceeds maximum 2713

I thought perhaps I could use a hash or rtree index, but neither of them work 
for unique-ness.

I saw in another posting that it may be possible to raise this limit to 8k or 
so, but I don't want to do this because 1) it requires a recompile of 
postgres (and rollout to dev, stage, prod machines), and 2) a key could 
theoretically be longer than 8k anyway.

I can guarantee unique-ness by removing the index and doing a select before 
the insert.  If there is a matching row, then I am trying to insert a dup. 
bail.

My concern is that doing this without an index will be monumentally slow for 
any large-ish number of keys.

Any bright ideas? All suggestions appreciated.

/andre

  2. Re: [SQL] Guarantee uniqueness of PGP key

    Joel Burton <joel@joelburton.com> — 2002-05-21T00:21:53Z

    Haven't tried, but would the md5 routine in pg_crypto (contrib/) work for
you? The odds of your discovering two keys that reduce to the same md5 key
is incredibly small, so it's close enough to check for uniqueness.

Don't know if that will take input that large, tho'.

Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant

> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of andre
> Sent: Monday, May 20, 2002 4:17 PM
> To: pgsql-sql@postgresql.org
> Cc: pgsql-novice@postgresql.org
> Subject: [SQL] Guarantee uniqueness of PGP key
>
>
> Hi,
>
> More generically, how do I best guarantee that any large varchar
> is unique
> before inserting?
>
> In my application, I need to ensure that each e-mail address and
> corresponding PGP public key are both unique.
>
> I originally implemented this by adding a uniqueness contrainst to both
> columns.  easy.
>
> The problem is that some PGP keys are over 2713 bytes, and postgres (7.2)
> gives the error:
>
> ERROR: btree: index item size 4336 exceeds maximum 2713
>
> I thought perhaps I could use a hash or rtree index, but neither
> of them work
> for unique-ness.
>
> I saw in another posting that it may be possible to raise this
> limit to 8k or
> so, but I don't want to do this because 1) it requires a recompile of
> postgres (and rollout to dev, stage, prod machines), and 2) a key could
> theoretically be longer than 8k anyway.
>
> I can guarantee unique-ness by removing the index and doing a
> select before
> the insert.  If there is a matching row, then I am trying to
> insert a dup.
> bail.
>
> My concern is that doing this without an index will be
> monumentally slow for
> any large-ish number of keys.
>
> Any bright ideas? All suggestions appreciated.
>
> /andre
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

  3. Re: Guarantee uniqueness of PGP key

    D. Duccini <duccini@backpack.com> — 2002-05-21T02:37:33Z

    good grief

yeah, look at using the MD5 or SHA1 hash of the key itself

-d

On Mon, 20 May 2002, andre wrote:

> Hi,
> 
> More generically, how do I best guarantee that any large varchar is unique 
> before inserting?
> 
> In my application, I need to ensure that each e-mail address and 
> corresponding PGP public key are both unique.
> 
> I originally implemented this by adding a uniqueness contrainst to both 
> columns.  easy.
> 
> The problem is that some PGP keys are over 2713 bytes, and postgres (7.2) 
> gives the error:
> 
> ERROR: btree: index item size 4336 exceeds maximum 2713
> 
> I thought perhaps I could use a hash or rtree index, but neither of them work 
> for unique-ness.
> 
> I saw in another posting that it may be possible to raise this limit to 8k or 
> so, but I don't want to do this because 1) it requires a recompile of 
> postgres (and rollout to dev, stage, prod machines), and 2) a key could 
> theoretically be longer than 8k anyway.
> 
> I can guarantee unique-ness by removing the index and doing a select before 
> the insert.  If there is a matching row, then I am trying to insert a dup. 
> bail.
> 
> My concern is that doing this without an index will be monumentally slow for 
> any large-ish number of keys.
> 
> Any bright ideas? All suggestions appreciated.
> 
> /andre
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org
> 


-----------------------------------------------------------------------------
david@backpack.com            BackPack Software, Inc.        www.backpack.com
+1 651.645.7550 voice       "Life is an Adventure.    
+1 651.645.9798 fax            Don't forget your BackPack!"   
-----------------------------------------------------------------------------

  4. Re: Guarantee uniqueness of PGP key

    Bruno Wolff III <bruno@wolff.to> — 2002-05-21T11:54:42Z

    On Mon, May 20, 2002 at 15:17:09 -0500,
  andre <andre@liquidprivacy.net> wrote:
> 
> The problem is that some PGP keys are over 2713 bytes, and postgres (7.2) 
> gives the error:

You might want to rethink your design. You probably want just the RSA or
DSA public key to be unique. Otherwise you will treat different versions of
what is essentially the same key as being different. (This might be due
to be signed by a different set of keys.) I suspect that this isn't what
you want. I think even the craziest people are using keys that should
be around 1000 bytes. Paranoid people (or those needing very long lived
public keys) are using ones half that size.

  5. Re: Guarantee uniqueness of PGP key

    andre <andre@liquidprivacy.net> — 2002-05-21T11:56:16Z

    Yes, I've decided to relax the unique-ness constraint on the public key.  The 
email address is still guaranteed unique. And we send out an encrypted 
verification email that only the person with the private key can decrypt and 
respond to. Thus, it doesn't really hurt to have multiple people paste in the 
same pub key.

If I really wanted uniqueness, I think you (and the others who replied) are 
correct that I should be storing the 'fingerprint' in a separate column.

/andre  

On Tuesday 21 May 2002 06:54 am, Bruno Wolff III wrote:
> On Mon, May 20, 2002 at 15:17:09 -0500,
>
>   andre <andre@liquidprivacy.net> wrote:
> > The problem is that some PGP keys are over 2713 bytes, and postgres (7.2)
> > gives the error:
>
> You might want to rethink your design. You probably want just the RSA or
> DSA public key to be unique. Otherwise you will treat different versions of
> what is essentially the same key as being different. (This might be due
> to be signed by a different set of keys.) I suspect that this isn't what
> you want. I think even the craziest people are using keys that should
> be around 1000 bytes. Paranoid people (or those needing very long lived
> public keys) are using ones half that size.