Postgresql – How to use an RSA public key as the primary key to a table in postgres

encryptionpostgresqlprimary-key

I have a data set that needs to be looked up by RSA public keys. These keys are 392 text characters long.

What is the best way to make that a primary and/or unique key?

Should it be a BYTEA field?

Is it too long for a primary key? Should I store a shorter hashed version of it to index on and filter by first before checking for an exact match?

Best Answer

I would say store them as bytea, but not for that reason. Simply because pg_crypto expects the keys as bytea. That establishes a convention. They likely have good reason for it: it seems intuitively faster.

Also there is no reason to store them as a PRIMARY KEY. You can sanely just store them in an indexed field.

Related Solutions

Should every table have a single-field surrogate/artificial primary key

I'm going to say no, not always, but most of the time yes..

These are some circumstances in which you don't need a surrogate or artificial key:

Pure intersection tables. If there is no risk of the intersection being the target of a foreign key and if there is little or no risk of the intersection attracting independent attributes (i.e. something other than FK's to the two parent tables) then you can get away with using the combination of FKs as the PK with fair confidence.
Lookup tables with static business keys. If you have a lookup
table with a unique business key which is fixed externally to your
business and which has zero chance of ever changing for any
practical purpose, then using the business key directly can make
things simpler. An example might be a list of state or province
codes or a list of ANSI standard numbers, etc.
Tables containing data consolidated from multiple, independent sources. If your system has many sources of data that must be shoehorned together into a single table, say at head office, then sometimes you need a compound key that includes the source system key value and a code indicating what the source system was.

There are also some situations where the old-faithful monotonically increasing integer surrogate key is not ideal. You can have keys that are alphanumeric surrogates. These could include:

Situations where you need to merge data from multiple, independent sources. To avoid key collisions you might use GUIDs instead of IDENTITY keys.
Situations where you are forced to use non-numeric key representations. Let's say you've got a license plate database. Your key could be the alphanumeric value instead of a pure number.
Situations where some external requirement forces you to apply compression to your key value. Instead of using 10 digits for an int32 you can use six base 36 digits.

Why most of the time yes? The most fundamental answer to that question is that it is pure hell if you ever need to modify a primary key value on any table. Since almost anything a user can see or touch is conceivably subject to an update at some point, using a visible key value is inviting pure hell. Using a surrogate key will keep you from falling into this trap.

Having said that, remember that there is room for YAGNI in applying this concept. You don't need to go forcing code tables with IDENTITY keys into every nook and cranny of your schema, just in case someone decides that the symbol for male gender in your employee table needs to change from M to X or something silly.

Performance of multi field primary key or ‘contrived’ ‘semi artificial’ key

I'll answer obliquely...

The natural key is always the natural key and should be enforced with a unique constraint or index. This is the "primary key" that flows from your modelling phase.

The choice of an auto-number/identity surrogate key matters at implementation phase because there are good and bad choices for your clustered index (example: SQL Server, Sybase, MySQL InnoDB, Oracle IOT).

That is, primary key is orthogonal to your clustered index: don't confuse the two issues

I'd suggest using a contrived key adds no value over using an auto-number/identity column in this respect. You lose data from the natural key, probably won't be unique, is just as opaque.

FWIW, I use surrogate keys and composite keys when I need too:

Some natural keys are useful in their own right: ISO currency and country codes
A table with no secondary (non-clustered) indexes and no child table doesn't benefit from a surrogate key
If you have parent-child-grandchild, then I usually need to join parent-grandchild: with composite keys I can do so directly. Simpler JOINs, simpler indexes

Note: this assumes that every table requires a clustered index

Best Answer

Related Solutions

Should every table have a single-field surrogate/artificial primary key

Performance of multi field primary key or ‘contrived’ ‘semi artificial’ key

Related Question