2
Hockeypuck - OpenPGP key server
3
Copyright (C) 2012-2014 Casey Marshall
5
This program is free software: you can redistribute it and/or modify
6
it under the terms of the GNU Affero General Public License as published by
7
the Free Software Foundation, version 3.
9
This program is distributed in the hope that it will be useful,
10
but WITHOUT ANY WARRANTY; without even the implied warranty of
11
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
12
GNU Affero General Public License for more details.
14
You should have received a copy of the GNU Affero General Public License
15
along with this program. If not, see <http://www.gnu.org/licenses/>.
27
Notes on Hockeypuck OpenPGP SQL schema
28
======================================
32
Most tables contain the columns: uuid, creation, expiration and state.
37
For public key records, the full 160-bit fingerprint is used, in a
38
Base-16, "reversed" (LSB-to-MSB) form. The reversal is performance optimal for
39
prefixed-substring "LIKE abc%" matching when searching for a shorter key ID.
41
Other packets can lack inherent content uniqueness. While uncommon, it is not
42
impossible for a User ID to have identical fields. Such a packet could even be
43
specially crafted to attack the service's ability to correctly represent a key!
45
In order to rule this out, and still keep the benefits of content-addressability,
46
a special digest is calculated on each packet's content, scoped to the primary
47
public key. This is calculated as:
49
base85 ( sha256 ( primary public key fingerprint || packet data ) )
51
For other records that do not directly represent an OpenPGP packet, the UUID
52
is a randomly generated value with an almost certain probability of uniqueness.
53
A randomly-generated Base-85 ascii string, representative of 256 bits should suffice.
57
Most tables represent an OpenPGP packet. These timestamps should copy the
58
actual packet's content meaning as defined in RFC 4880, for query purposes.
62
The 'state' is a Hockeypuck-reserved value which is intended to disposition
63
records outside of the RFC 4880 and HKP server specifications. It may lack
64
meaning initially for some records, but is reserved for later use.
67
- Flagging a key as garbage, spam, or in general disuse.
68
- Limiting the visibility or distribution of the key, subkey or signature.
70
For example, a public keyserver exposes UID certifications which can be easily
71
harvested to reproduce a social graph. Hockeypuck could hide these certifications
72
to unauthenticated queries, and only reveal them to requests that are signed with the
73
OpenPGP public keys that are actually a part of the social graph.
77
The original OpenPGP binary packet data is stored verbatim in the database.
78
All other columns that copy the content contained in packets exist for the purpose
79
of query convenience and performance. The Hockeypuck server should assert consistency
80
between these on insert/update, as well as in an integrity verification utility.
84
const Cr_openpgp_pubkey = `
85
CREATE TABLE IF NOT EXISTS openpgp_pubkey (
86
-----------------------------------------------------------------------
87
-- Full public key fingerprint, LSB-to-MSB, lowercased hex
89
-- Public key creation timestamp
90
creation TIMESTAMP WITH TIME ZONE NOT NULL,
91
-- Public key expiration timestamp (if any)
92
expiration TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT '9999-12-31 23:59:59+00',
93
-- State flag for this record
94
state INTEGER NOT NULL DEFAULT 0,
95
-- Binary contents of the OpenPGP packet
96
packet bytea NOT NULL,
97
-----------------------------------------------------------------------
98
-- Creation time of this public key fingerprint in the database
99
ctime TIMESTAMP WITH TIME ZONE NOT NULL,
100
-- Last-modified time of this public key fingerprint in the database
101
mtime TIMESTAMP WITH TIME ZONE NOT NULL,
102
-- MD5 digest of the entire public key contents, compatible with SKS
104
-- SHA256 digest of the entire public key contents, using same method
105
sha256 TEXT NOT NULL,
106
-- Reference to a revocation on this primary key
108
-- Reference to the primary User ID
110
-- Reference to the primary User Attribute
112
-----------------------------------------------------------------------
113
-- Public-key algorithm, RFC 4880, Section 9.1
114
algorithm INTEGER NOT NULL,
115
-- Public-key bit length
116
bit_len INTEGER NOT NULL,
117
-----------------------------------------------------------------------
118
-- Unsupported key material aggregated here
122
const Cr_openpgp_sig = `
123
CREATE TABLE IF NOT EXISTS openpgp_sig (
124
-----------------------------------------------------------------------
125
-- Scope- and content-unique identifer
127
-- Signature creation timestamp
128
creation TIMESTAMP WITH TIME ZONE NOT NULL,
129
-- Signature expiration timestamp (if any)
130
expiration TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT '9999-12-31 23:59:59+00',
131
-- State flag for this record
132
state INTEGER NOT NULL DEFAULT 0,
133
-- Binary contents of the OpenPGP packet
134
packet bytea NOT NULL,
135
-----------------------------------------------------------------------
136
-- Primary public key scope in which the signature occurs
137
pubkey_uuid TEXT NOT NULL,
138
-- Subkey signature target, if any
140
-- User ID signature target, if any
142
-- User attribute signature target, if any
144
-- Other signature target, if any
146
-----------------------------------------------------------------------
147
-- Signature type, RFC 4880, Section 5.2.1
148
sig_type INTEGER NOT NULL,
149
-- Key ID (16-character prefix of the public key rfingerprint)
150
signer TEXT NOT NULL,
151
-- Matched reference to the signer in *this* database, if found
153
-- Reference to a revocation on this signature, if any
157
const Cr_openpgp_subkey = `
158
CREATE TABLE IF NOT EXISTS openpgp_subkey (
159
-----------------------------------------------------------------------
160
-- Sub-key public key fingerprint, LSB-to-MSB, lowercased hex
162
-- Public key creation timestamp
163
creation TIMESTAMP WITH TIME ZONE NOT NULL,
164
-- Public key expiration timestamp (if any)
165
expiration TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT '9999-12-31 23:59:59+00',
166
-- State flag for this record
167
state INTEGER NOT NULL DEFAULT 0,
168
-- Binary contents of the OpenPGP packet
169
packet bytea NOT NULL,
170
-----------------------------------------------------------------------
171
-- Primary public key to which the subkey belongs
172
pubkey_uuid TEXT NOT NULL,
173
-- Reference to a revocation signature on this sub key, if any
175
-----------------------------------------------------------------------
176
-- Public-key algorithm, RFC 4880, Section 9.1
177
algorithm INTEGER NOT NULL,
178
-- Public-key bit length
179
bit_len INTEGER NOT NULL
182
const Cr_openpgp_uid = `
183
CREATE TABLE IF NOT EXISTS openpgp_uid (
184
-----------------------------------------------------------------------
185
-- Scope- and content-unique identifer
187
-- User ID creation timestamp. Since this packet lacks a field
188
-- for creation time, the earliest self-signature timestamp is used here.
189
creation TIMESTAMP WITH TIME ZONE NOT NULL,
190
-- User ID expiration timestamp (if any)
191
expiration TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT '9999-12-31 23:59:59+00',
192
-- State flag for this record
193
state INTEGER NOT NULL DEFAULT 0,
194
-- Binary contents of the OpenPGP packet
195
packet bytea NOT NULL,
196
-----------------------------------------------------------------------
197
-- Public key to which this identity belongs
198
pubkey_uuid TEXT NOT NULL,
199
-- Reference to a revocation signature on this identity, if any
201
-----------------------------------------------------------------------
202
-- Original text of the user identity string
203
keywords TEXT NOT NULL,
204
-- Tokenized, fulltext searchable index
205
keywords_fulltext tsvector NOT NULL
208
const Cr_openpgp_uat = `
209
CREATE TABLE IF NOT EXISTS openpgp_uat (
210
-----------------------------------------------------------------------
211
-- Scope- and content-unique identifer
213
-- User attribute creation timestamp. Since this packet lacks a field
214
-- for creation time, the earliest self-signature timestamp is used here.
215
creation TIMESTAMP WITH TIME ZONE NOT NULL,
216
-- User attribute expiration timestamp (if any)
217
expiration TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT '9999-12-31 23:59:59+00',
218
-- State flag for this record
219
state INTEGER NOT NULL DEFAULT 0,
220
-- Binary contents of the OpenPGP packet
222
-----------------------------------------------------------------------
223
-- Public key to which this identity belongs
225
-- Reference to a revocation signature on this identity, if any
229
const Cr_pks_status = `
230
CREATE TABLE IF NOT EXISTS pks_status (
231
-----------------------------------------------------------------------
232
-- Scope- and content-unique identifer
234
-- User ID creation timestamp
235
creation TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
236
-- User ID expiration timestamp (if any)
237
expiration TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT '9999-12-31 23:59:59+00',
238
-- State flag for this record. Nonzero disables.
239
state INTEGER NOT NULL DEFAULT 0,
240
-----------------------------------------------------------------------
241
-- Email address receiving PKS mail from this host
242
email_addr TEXT NOT NULL,
243
-- Last sync timestamp for this address
244
last_sync TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
245
-----------------------------------------------------------------------
250
var CreateTablesSql []string = []string{
259
var Cr_openpgp_pubkey_constraints []string = []string{
260
`ALTER TABLE openpgp_pubkey ADD CONSTRAINT openpgp_pubkey_pk PRIMARY KEY (uuid);`,
261
`ALTER TABLE openpgp_pubkey ADD CONSTRAINT openpgp_pubkey_md5 UNIQUE (md5);`,
262
`ALTER TABLE openpgp_pubkey ADD CONSTRAINT openpgp_pubkey_sha256 UNIQUE (sha256);`,
263
`CREATE INDEX openpgp_pubkey_ctime ON openpgp_pubkey (ctime);`,
264
`CREATE INDEX openpgp_pubkey_mtime ON openpgp_pubkey (mtime);`,
267
var Cr_openpgp_subkey_constraints []string = []string{
268
`ALTER TABLE openpgp_subkey ADD CONSTRAINT openpgp_subkey_pk PRIMARY KEY (uuid);`,
269
`ALTER TABLE openpgp_subkey ADD CONSTRAINT openpgp_subkey_pubkey_fk
270
FOREIGN KEY (pubkey_uuid) REFERENCES openpgp_pubkey(uuid)
271
DEFERRABLE INITIALLY DEFERRED;`,
272
`CREATE INDEX openpgp_subkey_pubkey ON openpgp_subkey (pubkey_uuid);`,
275
var Cr_openpgp_uid_constraints []string = []string{
276
`ALTER TABLE openpgp_uid ADD CONSTRAINT openpgp_uid_pk PRIMARY KEY (uuid);`,
277
`ALTER TABLE openpgp_uid ADD CONSTRAINT openpgp_uid_pubkey_fk
278
FOREIGN KEY (pubkey_uuid) REFERENCES openpgp_pubkey(uuid)
279
DEFERRABLE INITIALLY DEFERRED;`,
280
`CREATE INDEX openpgp_uid_pubkey ON openpgp_uid (pubkey_uuid);`,
281
`CREATE INDEX openpgp_uid_fulltext_idx ON openpgp_uid USING gin(keywords_fulltext);`}
283
var Cr_openpgp_uat_constraints []string = []string{
284
`ALTER TABLE openpgp_uat ADD CONSTRAINT openpgp_uat_pk PRIMARY KEY (uuid);`,
285
`ALTER TABLE openpgp_uat ADD CONSTRAINT openpgp_uat_pubkey_fk
286
FOREIGN KEY (pubkey_uuid) REFERENCES openpgp_pubkey(uuid)
287
DEFERRABLE INITIALLY DEFERRED;`,
288
`CREATE INDEX openpgp_uat_pubkey ON openpgp_uat (pubkey_uuid);`,
291
var Cr_openpgp_sig_constraints []string = []string{
292
`ALTER TABLE openpgp_sig ADD CONSTRAINT openpgp_sig_pk PRIMARY KEY (uuid);`,
293
`ALTER TABLE openpgp_sig ADD CONSTRAINT openpgp_sig_signer_fk FOREIGN KEY (signer_uuid)
294
REFERENCES openpgp_pubkey(uuid) DEFERRABLE INITIALLY DEFERRED;`,
295
`ALTER TABLE openpgp_sig ADD CONSTRAINT openpgp_sig_pubkey_fk
296
FOREIGN KEY (pubkey_uuid) REFERENCES openpgp_pubkey(uuid)
297
DEFERRABLE INITIALLY DEFERRED;`,
298
`ALTER TABLE openpgp_sig ADD CONSTRAINT openpgp_sig_subkey_fk
299
FOREIGN KEY (subkey_uuid) REFERENCES openpgp_subkey(uuid)
300
DEFERRABLE INITIALLY DEFERRED;`,
301
`ALTER TABLE openpgp_sig ADD CONSTRAINT openpgp_sig_uid_fk
302
FOREIGN KEY (uid_uuid) REFERENCES openpgp_uid(uuid)
303
DEFERRABLE INITIALLY DEFERRED;`,
304
`ALTER TABLE openpgp_sig ADD CONSTRAINT openpgp_sig_uat_fk
305
FOREIGN KEY (uat_uuid) REFERENCES openpgp_uat(uuid)
306
DEFERRABLE INITIALLY DEFERRED;`,
307
`ALTER TABLE openpgp_sig ADD CONSTRAINT openpgp_sig_sig_fk
308
FOREIGN KEY (sig_uuid) REFERENCES openpgp_sig(uuid)
309
DEFERRABLE INITIALLY DEFERRED;`,
310
`CREATE INDEX openpgp_sig_idx ON openpgp_sig (pubkey_uuid, subkey_uuid, uid_uuid, uat_uuid);`,
313
var Cr_openpgp_primary_constraints []string = []string{
314
`ALTER TABLE openpgp_pubkey ADD CONSTRAINT openpgp_pubkey_primary_uid_fk
315
FOREIGN KEY (primary_uid) REFERENCES openpgp_uid(uuid)
316
DEFERRABLE INITIALLY DEFERRED;`,
317
`ALTER TABLE openpgp_pubkey ADD CONSTRAINT openpgp_pubkey_primary_uat_fk
318
FOREIGN KEY (primary_uat) REFERENCES openpgp_uat(uuid)
319
DEFERRABLE INITIALLY DEFERRED;`,
322
var Cr_openpgp_revsig_constraints []string = []string{
323
`ALTER TABLE openpgp_pubkey ADD CONSTRAINT openpgp_pubkey_revsig_fk
324
FOREIGN KEY (revsig_uuid) REFERENCES openpgp_sig(uuid)
325
DEFERRABLE INITIALLY DEFERRED;`,
326
`ALTER TABLE openpgp_subkey ADD CONSTRAINT openpgp_subkey_revsig_fk
327
FOREIGN KEY (revsig_uuid) REFERENCES openpgp_sig(uuid)
328
DEFERRABLE INITIALLY DEFERRED;`,
329
`ALTER TABLE openpgp_uid ADD CONSTRAINT openpgp_uid_revsig_fk
330
FOREIGN KEY (revsig_uuid) REFERENCES openpgp_sig(uuid)
331
DEFERRABLE INITIALLY DEFERRED;`,
332
`ALTER TABLE openpgp_uat ADD CONSTRAINT openpgp_uat_revsig_fk
333
FOREIGN KEY (revsig_uuid) REFERENCES openpgp_sig(uuid)
334
DEFERRABLE INITIALLY DEFERRED;`,
335
`ALTER TABLE openpgp_sig ADD CONSTRAINT openpgp_sig_revsig_fk FOREIGN KEY (revsig_uuid)
336
REFERENCES openpgp_sig(uuid) DEFERRABLE INITIALLY DEFERRED;`,
339
var CreateConstraintsSql [][]string = [][]string{
340
Cr_openpgp_pubkey_constraints,
341
Cr_openpgp_subkey_constraints,
342
Cr_openpgp_uid_constraints,
343
Cr_openpgp_uat_constraints,
344
Cr_openpgp_sig_constraints,
345
Cr_openpgp_primary_constraints,
346
Cr_openpgp_revsig_constraints,
349
const dedupTemplate = `
350
{{define "cols"}}{{/*
351
*/}}{{range $i, $colname := .UniqueColumns}}{{if $i}},{{end}}{{$colname}}{{end}}{{/*
353
*/}}{{define "sql"}}{{/*
354
*/}}CREATE TABLE dedup_{{.TableName}} AS
355
SELECT DISTINCT ON ({{template "cols" .}}) * FROM {{.TableName}};
356
DROP TABLE {{.TableName}};
357
ALTER TABLE dedup_{{.TableName}} RENAME TO {{.TableName}};{{/*
358
*/}}{{end}}{{template "sql" .}}`
362
UniqueColumns []string
365
var dedups []dedup = []dedup{
366
dedup{"openpgp_sig", []string{"uuid"}},
367
dedup{"openpgp_uat", []string{"uuid"}},
368
dedup{"openpgp_uid", []string{"uuid"}},
369
dedup{"openpgp_subkey", []string{"uuid"}},
370
dedup{"openpgp_pubkey", []string{"uuid"}},
373
var DeleteDuplicatesSql []string
376
t := template.Must(template.New("DeleteDuplicates").Parse(dedupTemplate))
379
for _, dedup := range dedups {
381
if err = t.Execute(&out, dedup); err != nil {
384
sql = append(sql, out.String())
386
DeleteDuplicatesSql = sql
389
var Dr_openpgp_pubkey_constraints []string = []string{
390
`ALTER TABLE openpgp_pubkey DROP CONSTRAINT openpgp_pubkey_pk;`,
391
`ALTER TABLE openpgp_pubkey DROP CONSTRAINT openpgp_pubkey_md5;`,
392
`ALTER TABLE openpgp_pubkey DROP CONSTRAINT openpgp_pubkey_sha256;`,
393
`DROP INDEX openpgp_pubkey_ctime;`,
394
`DROP INDEX openpgp_pubkey_mtime;`,
397
var Dr_openpgp_subkey_constraints []string = []string{
398
`ALTER TABLE openpgp_subkey DROP CONSTRAINT openpgp_subkey_pk;`,
399
`ALTER TABLE openpgp_subkey DROP CONSTRAINT openpgp_subkey_pubkey_fk;`,
400
`DROP INDEX openpgp_subkey_pubkey;`,
403
var Dr_openpgp_uid_constraints []string = []string{
404
`ALTER TABLE openpgp_uid DROP CONSTRAINT openpgp_uid_pk;`,
405
`ALTER TABLE openpgp_uid DROP CONSTRAINT openpgp_uid_pubkey_fk;`,
406
`DROP INDEX openpgp_uid_pubkey;`,
407
`DROP INDEX openpgp_uid_fulltext_idx;`,
410
var Dr_openpgp_uat_constraints []string = []string{
411
`ALTER TABLE openpgp_uat DROP CONSTRAINT openpgp_uat_pk;`,
412
`ALTER TABLE openpgp_uat DROP CONSTRAINT openpgp_uat_pubkey_fk;`,
413
`DROP INDEX openpgp_uat_pubkey;`,
416
var Dr_openpgp_sig_constraints []string = []string{
417
`DROP INDEX openpgp_sig_idx;`,
418
`ALTER TABLE openpgp_sig DROP CONSTRAINT openpgp_sig_signer_fk;`,
419
`ALTER TABLE openpgp_sig DROP CONSTRAINT openpgp_sig_pubkey_fk;`,
420
`ALTER TABLE openpgp_sig DROP CONSTRAINT openpgp_sig_subkey_fk;`,
421
`ALTER TABLE openpgp_sig DROP CONSTRAINT openpgp_sig_uid_fk;`,
422
`ALTER TABLE openpgp_sig DROP CONSTRAINT openpgp_sig_uat_fk;`,
423
`ALTER TABLE openpgp_sig DROP CONSTRAINT openpgp_sig_sig_fk;`,
424
`ALTER TABLE openpgp_sig DROP CONSTRAINT openpgp_sig_pk;`,
427
var Dr_openpgp_primary_constraints []string = []string{
428
`ALTER TABLE openpgp_pubkey DROP CONSTRAINT openpgp_pubkey_primary_uid_fk;`,
429
`ALTER TABLE openpgp_pubkey DROP CONSTRAINT openpgp_pubkey_primary_uat_fk;`,
432
var Dr_openpgp_revsig_constraints []string = []string{
433
`ALTER TABLE openpgp_pubkey DROP CONSTRAINT openpgp_pubkey_revsig_fk;`,
434
`ALTER TABLE openpgp_subkey DROP CONSTRAINT openpgp_subkey_revsig_fk;`,
435
`ALTER TABLE openpgp_uid DROP CONSTRAINT openpgp_uid_revsig_fk;`,
436
`ALTER TABLE openpgp_uat DROP CONSTRAINT openpgp_uat_revsig_fk;`,
437
`ALTER TABLE openpgp_sig DROP CONSTRAINT openpgp_sig_revsig_fk;`,
440
var DropConstraintsSql [][]string = [][]string{
441
Dr_openpgp_revsig_constraints,
442
Dr_openpgp_primary_constraints,
443
Dr_openpgp_sig_constraints,
444
Dr_openpgp_uat_constraints,
445
Dr_openpgp_uid_constraints,
446
Dr_openpgp_subkey_constraints,
447
Dr_openpgp_pubkey_constraints,