db.go (11314B)
1 // This file is part of taler-mailbox, the Taler Mailbox implementation. 2 // Copyright (C) 2026 Martin Schanzenbach 3 // 4 // taler-mailbox is free software: you can redistribute it and/or modify it 5 // under the terms of the GNU Affero General Public License as published 6 // by the Free Software Foundation, either version 3 of the License, 7 // or (at your option) any later version. 8 // 9 // taler-mailbox is distributed in the hope that it will be useful, but 10 // WITHOUT ANY WARRANTY; without even the implied warranty of 11 // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU 12 // Affero General Public License for more details. 13 // 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/>. 16 // 17 // SPDX-License-Identifier: AGPL3.0-or-later 18 19 package mailbox 20 21 import ( 22 "context" 23 "database/sql" 24 _ "github.com/lib/pq" 25 "errors" 26 "time" 27 ) 28 29 type Timestamp struct { 30 Seconds uint64 `json:"t_s"` 31 } 32 33 type MailboxMetadata struct { 34 // ORM 35 Serial int64 `json:"-"` 36 37 // ORM helper hash of signing key 38 HashedSigningKey string `json:"-"` 39 40 // The mailbox signing key. 41 // Note that $H_MAILBOX == H(singingKey). 42 // Note also how this key cannot be updated 43 // as it identifies the mailbox. 44 SigningKey string `json:"signing_key"` 45 46 // Type of key. 47 // Optional, as currently only 48 // EdDSA keys are supported. 49 SigningKeyType string `json:"signing_key_type"` 50 51 // The mailbox encryption key. 52 // This is an HPKE public key 53 // in the X25519 format for use 54 // in a X25519-DHKEM (RFC 9180). 55 // Base32 crockford-encoded. 56 EncryptionKey string `json:"encryption_key"` 57 58 // Type of key. 59 // Optional, as currently only 60 // X25519 keys are supported. 61 EncryptionKeyType string `json:"encryption_key_type"` 62 63 // Expiration of this mapping (UNIX Epoch seconds). 64 Expiration Timestamp `json:"expiration"` 65 66 // Info field (e.g for Keyoxide claim proof) 67 Info string `json:"info,omitempty"` 68 } 69 70 type PendingMailboxRegistration struct { 71 // ORM 72 Serial int64 `json:"-"` 73 74 // Created timestamp (in Seconds / UNIX Epoch) 75 CreatedAt int64 76 77 // Hash of the inbox for this entry 78 HashedSigningKey string // Requested registration duration 79 80 // Registration duration (in Seconds) 81 Duration int64 82 83 // The order ID associated with this registration 84 OrderID string `json:"-"` 85 } 86 87 type InboxEntry struct { 88 // ORM 89 Serial int64 `json:"-"` 90 91 // Encrypted message. Must be exactly 256-32 bytes long. 92 Body []byte 93 94 // Hash of the inbox for this entry 95 HashedSigningKey string 96 } 97 98 func InsertInboxEntryIntoDatabase(db *sql.DB, e *InboxEntry) error { 99 query := `INSERT INTO taler_mailbox.inbox_entries 100 VALUES (DEFAULT, $1, $2);` 101 rows, err := db.Query(query, e.HashedSigningKey, e.Body) 102 if err != nil { 103 return err 104 } 105 defer rows.Close() 106 return nil 107 } 108 109 func InsertPendingRegistrationIntoDatabase(db *sql.DB, pr *PendingMailboxRegistration) error { 110 pr.CreatedAt = time.Now().Unix() 111 query := `INSERT INTO taler_mailbox.pending_mailbox_registrations 112 VALUES (DEFAULT, $1, $2, $3, $4);` 113 rows, err := db.Query(query, pr.CreatedAt, pr.HashedSigningKey, pr.OrderID, pr.Duration) 114 if err != nil { 115 return err 116 } 117 defer rows.Close() 118 return nil 119 } 120 121 func InsertMailboxRegistrationIntoDatabase(db *sql.DB, mb *MailboxMetadata) error { 122 query := `INSERT INTO taler_mailbox.mailbox_metadata 123 VALUES (DEFAULT, $1, $2, $3, $4, $5, $6, $7);` 124 rows, err := db.Query(query, mb.HashedSigningKey, mb.SigningKey, mb.SigningKeyType, mb.EncryptionKey, mb.EncryptionKeyType, mb.Expiration.Seconds, mb.Info) 125 if err != nil { 126 return err 127 } 128 defer rows.Close() 129 return nil 130 } 131 132 func UpdatePendingRegistrationOrderIdInDatabase(db *sql.DB, pr *PendingMailboxRegistration) error { 133 query := `UPDATE taler_mailbox.pending_mailbox_registrations 134 SET 135 "order_id" = $2 136 WHERE "hashed_signing_key" = $1;` 137 rows, err := db.Query(query, pr.HashedSigningKey, pr.OrderID) 138 if err != nil { 139 return err 140 } 141 defer rows.Close() 142 return nil 143 } 144 145 func UpdateMailboxExpirationInDatabase(db *sql.DB, mb *MailboxMetadata) error { 146 query := `UPDATE taler_mailbox.mailbox_metadata 147 SET 148 "expiration" = $2 149 WHERE "hashed_signing_key" = $1;` 150 rows, err := db.Query(query, mb.HashedSigningKey, mb.Expiration.Seconds) 151 if err != nil { 152 return err 153 } 154 defer rows.Close() 155 return nil 156 } 157 158 func GetPendingRegistrationFromDatabaseBySigningKey(db *sql.DB, pr *PendingMailboxRegistration, hashedKey string) error { 159 query := `SELECT 160 "serial", 161 "hashed_signing_key", 162 "registration_duration", 163 "order_id" 164 FROM taler_mailbox.pending_mailbox_registrations 165 WHERE 166 "hashed_signing_key"=$1 167 LIMIT 1 168 ;` 169 // Execute Query 170 rows, err := db.Query(query, hashedKey) 171 if err != nil { 172 return err 173 } 174 defer rows.Close() 175 // Iterate over first 176 if !rows.Next() { 177 return errors.New("mailbox metadata does not exist") 178 } 179 return rows.Scan( 180 &pr.Serial, 181 &pr.HashedSigningKey, 182 &pr.Duration, 183 &pr.OrderID, 184 ) 185 } 186 187 func GetMailboxMetadataFromDatabaseBySigningKey(db *sql.DB, mb *MailboxMetadata, hashedKey string) error { 188 query := `SELECT 189 "serial", 190 "hashed_signing_key", 191 "signing_key", 192 "signing_key_type", 193 "encryption_key", 194 "encryption_key_type", 195 "expiration", 196 "info" 197 FROM taler_mailbox.mailbox_metadata 198 WHERE 199 "hashed_signing_key"=$1 200 LIMIT 1 201 ;` 202 // Execute Query 203 rows, err := db.Query(query, hashedKey) 204 if err != nil { 205 return err 206 } 207 defer rows.Close() 208 // Iterate over first 209 if !rows.Next() { 210 return errors.New("Mailbox metadata does not exist") 211 } 212 return rows.Scan( 213 &mb.Serial, 214 &mb.HashedSigningKey, 215 &mb.SigningKey, 216 &mb.SigningKeyType, 217 &mb.EncryptionKey, 218 &mb.EncryptionKeyType, 219 &mb.Expiration.Seconds, 220 &mb.Info, 221 ) 222 } 223 224 func GetInboxEntryFromDatabaseBySigningKeyAndBody(db *sql.DB, e *InboxEntry, hashedKey string, body []byte) error { 225 query := `SELECT 226 "serial", 227 "hashed_signing_key", 228 "body" 229 FROM taler_mailbox.inbox_entries 230 WHERE 231 "hashed_signing_key"=$1 AND 232 "body"=$2 233 ;` 234 // Execute Query 235 rows, err := db.Query(query, hashedKey, body) 236 if err != nil { 237 return err 238 } 239 defer rows.Close() 240 // Iterate over first 241 if !rows.Next() { 242 return errors.New("inbox entry does not exist") 243 } 244 return rows.Scan( 245 &e.Serial, 246 &e.HashedSigningKey, 247 &e.Body, 248 ) 249 } 250 251 func GetInboxEntryFromDatabaseBySerial(db *sql.DB, e *InboxEntry, hashedKey string, serial int64) error { 252 query := `SELECT 253 "serial", 254 "hashed_signing_key", 255 "body" 256 FROM taler_mailbox.inbox_entries 257 WHERE 258 "serial"=$1 AND 259 "hashed_signing_key"=$2 260 ;` 261 // Execute Query 262 rows, err := db.Query(query, serial, hashedKey) 263 if err != nil { 264 return err 265 } 266 defer rows.Close() 267 // Iterate over first 268 if !rows.Next() { 269 return errors.New("inbox entry does not exist") 270 } 271 return rows.Scan( 272 &e.Serial, 273 &e.HashedSigningKey, 274 &e.Body, 275 ) 276 } 277 278 func DeletePendingRegistrationFromDatabase(db *sql.DB, pr *PendingMailboxRegistration) (int64, error) { 279 var ctx context.Context 280 ctx, stop := context.WithCancel(context.Background()) 281 defer stop() 282 conn, err := db.Conn(ctx) 283 if err != nil { 284 return 0, err 285 } 286 defer conn.Close() 287 query := `DELETE 288 FROM taler_mailbox.pending_mailbox_registrations 289 WHERE 290 "serial" = $1 291 ;` 292 // Execute Query 293 result, err := conn.ExecContext(ctx, query, pr.Serial) 294 if err != nil { 295 return 0, err 296 } 297 rows, err := result.RowsAffected() 298 if err != nil { 299 return 0, err 300 } 301 return rows, nil 302 } 303 304 // DeleteInboxEntryFromDatabaseBySerial Deletes all entries starting from given serial 305 func DeleteInboxEntryFromDatabaseBySerial(db *sql.DB, e *InboxEntry, count int) (int64, error) { 306 var ctx context.Context 307 ctx, stop := context.WithCancel(context.Background()) 308 defer stop() 309 conn, err := db.Conn(ctx) 310 if err != nil { 311 return 0, err 312 } 313 defer conn.Close() 314 query := `DELETE FROM taler_mailbox.inbox_entries 315 WHERE serial IN ( 316 SELECT serial FROM taler_mailbox.inbox_entries 317 WHERE 318 "hashed_signing_key"=$1 AND 319 "serial">=$2 320 LIMIT $3 321 ) 322 ;` 323 // Execute Query 324 result, err := conn.ExecContext(ctx, query, e.HashedSigningKey, e.Serial, count) 325 if err != nil { 326 return 0, err 327 } 328 rows, err := result.RowsAffected() 329 if err != nil { 330 return 0, err 331 } 332 return rows, nil 333 } 334 335 // DeleteStaleRegstrationsFromDatabase purges stale registrations 336 func DeleteStaleRegistrationsFromDatabase(db *sql.DB, registrationExpiration time.Time) (int64, error) { 337 var ctx context.Context 338 ctx, stop := context.WithCancel(context.Background()) 339 defer stop() 340 conn, err := db.Conn(ctx) 341 if err != nil { 342 return 0, err 343 } 344 defer conn.Close() 345 query := `DELETE 346 FROM taler_mailbox.mailbox_metadata 347 WHERE 348 "expiration" < $1 349 ;` 350 // Execute Query 351 result, err := conn.ExecContext(ctx, query, registrationExpiration.Unix()) 352 if err != nil { 353 return 0, err 354 } 355 rows, err := result.RowsAffected() 356 if err != nil { 357 return 0, err 358 } 359 return rows, nil 360 } 361 362 // DeleteStalePendingRegstrationsFromDatabase purges stale registrations 363 func DeleteStalePendingRegistrationsFromDatabase(db *sql.DB, registrationExpiration time.Time) (int64, error) { 364 var ctx context.Context 365 ctx, stop := context.WithCancel(context.Background()) 366 defer stop() 367 conn, err := db.Conn(ctx) 368 if err != nil { 369 return 0, err 370 } 371 defer conn.Close() 372 query := `DELETE 373 FROM taler_mailbox.pending_mailbox_registrations 374 WHERE 375 "created_at" < $1 376 ;` 377 // Execute Query 378 result, err := conn.ExecContext(ctx, query, registrationExpiration.Unix()) 379 if err != nil { 380 return 0, err 381 } 382 rows, err := result.RowsAffected() 383 if err != nil { 384 return 0, err 385 } 386 return rows, nil 387 } 388 389 func DeleteAllPendingRegistrationsFromDatabase(db *sql.DB) (int64, error) { 390 var ctx context.Context 391 ctx, stop := context.WithCancel(context.Background()) 392 defer stop() 393 conn, err := db.Conn(ctx) 394 if err != nil { 395 return 0, err 396 } 397 defer conn.Close() 398 query := `DELETE 399 FROM taler_mailbox.pending_mailbox_registrations 400 WHERE 401 1=1 402 ;` 403 // Execute Query 404 result, err := conn.ExecContext(ctx, query) 405 if err != nil { 406 return 0, err 407 } 408 rows, err := result.RowsAffected() 409 if err != nil { 410 return 0, err 411 } 412 return rows, nil 413 } 414 415 func DeleteAllMailboxesFromDatabase(db *sql.DB) (int64, error) { 416 var ctx context.Context 417 ctx, stop := context.WithCancel(context.Background()) 418 defer stop() 419 conn, err := db.Conn(ctx) 420 if err != nil { 421 return 0, err 422 } 423 defer conn.Close() 424 query := `DELETE 425 FROM taler_mailbox.mailbox_metadata 426 WHERE 427 1=1 428 ;` 429 // Execute Query 430 result, err := conn.ExecContext(ctx, query) 431 if err != nil { 432 return 0, err 433 } 434 rows, err := result.RowsAffected() 435 if err != nil { 436 return 0, err 437 } 438 return rows, nil 439 } 440 441 func DeleteAllInboxEntriesFromDatabase(db *sql.DB) (int64, error) { 442 var ctx context.Context 443 ctx, stop := context.WithCancel(context.Background()) 444 defer stop() 445 conn, err := db.Conn(ctx) 446 if err != nil { 447 return 0, err 448 } 449 defer conn.Close() 450 query := `DELETE 451 FROM taler_mailbox.inbox_entries 452 WHERE 453 1=1 454 ;` 455 // Execute Query 456 result, err := conn.ExecContext(ctx, query) 457 if err != nil { 458 return 0, err 459 } 460 rows, err := result.RowsAffected() 461 if err != nil { 462 return 0, err 463 } 464 return rows, nil 465 }