pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedGuarantee uniqueness of PGP key
5+ messages / 4 participants
[nested] [flat]
* Guarantee uniqueness of PGP key
@ 2002-05-20 20:17 andre <andre@liquidprivacy.net>
0 siblings, 3 replies; 5+ messages in thread
From: andre @ 2002-05-20 20:17 UTC (permalink / raw)
To: pgsql-sql; +Cc: pgsql-novice@postgresql.org
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SQL] Guarantee uniqueness of PGP key
@ 2002-05-21 00:21 Joel Burton <joel@joelburton.com>
parent: andre <andre@liquidprivacy.net>
2 siblings, 0 replies; 5+ messages in thread
From: Joel Burton @ 2002-05-21 00:21 UTC (permalink / raw)
To: andre <andre@liquidprivacy.net>; pgsql-sql; +Cc: pgsql-novice@postgresql.org
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
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Guarantee uniqueness of PGP key
@ 2002-05-21 02:37 D. Duccini <duccini@backpack.com>
parent: andre <andre@liquidprivacy.net>
2 siblings, 0 replies; 5+ messages in thread
From: D. Duccini @ 2002-05-21 02:37 UTC (permalink / raw)
To: andre <andre@liquidprivacy.net>; +Cc: pgsql-sql; pgsql-novice@postgresql.org
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!"
-----------------------------------------------------------------------------
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Guarantee uniqueness of PGP key
@ 2002-05-21 11:54 Bruno Wolff III <bruno@wolff.to>
parent: andre <andre@liquidprivacy.net>
2 siblings, 1 reply; 5+ messages in thread
From: Bruno Wolff III @ 2002-05-21 11:54 UTC (permalink / raw)
To: andre <andre@liquidprivacy.net>; +Cc: pgsql-sql; pgsql-novice@postgresql.org
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Guarantee uniqueness of PGP key
@ 2002-05-21 11:56 andre <andre@liquidprivacy.net>
parent: Bruno Wolff III <bruno@wolff.to>
0 siblings, 0 replies; 5+ messages in thread
From: andre @ 2002-05-21 11:56 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: pgsql-sql; pgsql-novice@postgresql.org
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2002-05-21 11:56 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-05-20 20:17 Guarantee uniqueness of PGP key andre <andre@liquidprivacy.net>
2002-05-21 00:21 ` Joel Burton <joel@joelburton.com>
2002-05-21 02:37 ` D. Duccini <duccini@backpack.com>
2002-05-21 11:54 ` Bruno Wolff III <bruno@wolff.to>
2002-05-21 11:56 ` andre <andre@liquidprivacy.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox