db.go (17326B)
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 "errors" 25 "log" 26 "strings" 27 "time" 28 29 _ "github.com/lib/pq" 30 talerutil "github.com/schanzen/taler-go/pkg/util" 31 ) 32 33 type Timestamp struct { 34 Seconds uint64 `json:"t_s"` 35 } 36 37 type MailboxMetadata struct { 38 // ORM 39 Serial int64 `json:"-"` 40 41 // ORM helper hash of signing key 42 HashedSigningKey string `json:"-"` 43 44 // The mailbox signing key. 45 // Note that $H_MAILBOX == H(singingKey). 46 // Note also how this key cannot be updated 47 // as it identifies the mailbox. 48 SigningKey string `json:"signing_key"` 49 50 // Type of key. 51 // Optional, as currently only 52 // EdDSA keys are supported. 53 SigningKeyType string `json:"signing_key_type"` 54 55 // The mailbox encryption key. 56 // This is an HPKE public key 57 // in the X25519 format for use 58 // in a X25519-DHKEM (RFC 9180). 59 // Base32 crockford-encoded. 60 EncryptionKey string `json:"encryption_key"` 61 62 // Type of key. 63 // Optional, as currently only 64 // X25519 keys are supported. 65 EncryptionKeyType string `json:"encryption_key_type"` 66 67 // Expiration of this mapping (UNIX Epoch seconds). 68 Expiration Timestamp `json:"expiration"` 69 70 // Info field (e.g for Keyoxide claim proof) 71 Info string `json:"info,omitempty"` 72 } 73 74 type PendingMailboxRegistration struct { 75 // ORM 76 Serial int64 `json:"-"` 77 78 // Created timestamp (in Seconds / UNIX Epoch) 79 CreatedAt int64 80 81 // Hash of the inbox for this entry 82 HashedSigningKey string // Requested registration duration 83 84 // Registration duration (in Seconds) 85 Duration int64 86 87 // The order ID associated with this registration 88 OrderID string `json:"-"` 89 } 90 91 type InboxEntry struct { 92 // ORM 93 Serial int64 `json:"-"` 94 95 // Encrypted message. Must be exactly 256-32 bytes long. 96 Body []byte 97 98 // Hash of the inbox for this entry 99 HashedSigningKey string 100 } 101 102 // MailboxDatabase is the main taldir database connection handle 103 type MailboxDatabase struct { 104 // SQL connection 105 db *sql.DB 106 107 // Get mailbox by key 108 getMailboxMetadataBySigningKeyStmt *sql.Stmt 109 110 // Get entry by key and body 111 getInboxEntryBySigningKeyAndBodyStmt *sql.Stmt 112 113 // Get entry statement 114 getInboxEntryBySerialStmt *sql.Stmt 115 116 // Insert entry statement 117 insertInboxEntryStmt *sql.Stmt 118 119 // Delete entry statement 120 deleteInboxEntryBySerialStmt *sql.Stmt 121 122 // Insert pending registration 123 insertPendingRegistrationStmt *sql.Stmt 124 125 // Update pending registration oder ID 126 updatePendingRegistrationOrderIdStmt *sql.Stmt 127 128 // Delete pending registration 129 deletePendingRegistrationStmt *sql.Stmt 130 131 // Insert mailbox registration 132 insertMailboxRegistrationStmt *sql.Stmt 133 134 // Update the expiration of a Mailbox 135 updateMailboxExpirationStmt *sql.Stmt 136 137 // Get pending registration by signing key 138 getPendingMailboxRegistrationBySigningKeyStmt *sql.Stmt 139 140 // Delete stale registrations 141 deleteStaleRegistrationsStmt *sql.Stmt 142 143 // Delete stale pending registrations 144 deleteStalePendingRegistrationsStmt *sql.Stmt 145 146 // Get messages 147 getMessagesBySigningKeyStmt *sql.Stmt 148 149 // Count messages 150 countMessagesBySigningKeyStmt *sql.Stmt 151 } 152 153 func (db *MailboxDatabase) Close() { 154 for _, s := range []*sql.Stmt{ 155 db.insertInboxEntryStmt, 156 db.getMailboxMetadataBySigningKeyStmt, 157 db.getInboxEntryBySerialStmt, 158 db.getInboxEntryBySigningKeyAndBodyStmt, 159 db.deleteInboxEntryBySerialStmt, 160 db.insertPendingRegistrationStmt, 161 db.updatePendingRegistrationOrderIdStmt, 162 db.deletePendingRegistrationStmt, 163 db.insertMailboxRegistrationStmt, 164 db.updateMailboxExpirationStmt, 165 db.getPendingMailboxRegistrationBySigningKeyStmt, 166 db.deleteStalePendingRegistrationsStmt, 167 db.deleteStaleRegistrationsStmt, 168 db.getMessagesBySigningKeyStmt, 169 db.countMessagesBySigningKeyStmt, 170 } { 171 if s != nil { 172 s.Close() 173 } 174 } 175 db.db.Close() 176 } 177 178 func OpenDatabase(psqlconn string) (*MailboxDatabase, error) { 179 db, err := sql.Open("postgres", psqlconn) 180 if err != nil { 181 return nil, err 182 } 183 segments := strings.Split(strings.Split(psqlconn, "?")[0], "/") 184 dbName := segments[len(segments)-1] 185 186 err = talerutil.DBInit(db, "../..", dbName, "taler-directory") 187 if err != nil { 188 log.Fatalf("Failed to apply versioning or patches: %v", err) 189 } 190 insertInboxEntryStmt, err := db.Prepare(`INSERT INTO taler_mailbox.inbox_entries 191 VALUES (DEFAULT, $1, $2);`) 192 if err != nil { 193 return nil, err 194 } 195 insertPendingRegistrationStmt, err := db.Prepare(`INSERT INTO taler_mailbox.pending_mailbox_registrations 196 VALUES (DEFAULT, $1, $2, $3, $4);`) 197 if err != nil { 198 return nil, err 199 } 200 insertMailboxRegistrationStmt, err := db.Prepare(`INSERT INTO taler_mailbox.mailbox_metadata 201 VALUES (DEFAULT, $1, $2, $3, $4, $5, $6, $7);`) 202 if err != nil { 203 return nil, err 204 } 205 updatePendingRegistrationOrderIdStmt, err := db.Prepare(`UPDATE taler_mailbox.pending_mailbox_registrations 206 SET 207 "order_id" = $2 208 WHERE "hashed_signing_key" = $1;`) 209 if err != nil { 210 return nil, err 211 } 212 updateMailboxExpirationStmt, err := db.Prepare(`UPDATE taler_mailbox.mailbox_metadata 213 SET 214 "expiration" = $2 215 WHERE "hashed_signing_key" = $1;`) 216 getPendingRegistrationBySingingKeyStmt, err := db.Prepare(`SELECT 217 "serial", 218 "hashed_signing_key", 219 "registration_duration", 220 "order_id" 221 FROM taler_mailbox.pending_mailbox_registrations 222 WHERE 223 "hashed_signing_key"=$1 224 LIMIT 1 225 ;`) 226 if err != nil { 227 return nil, err 228 } 229 getMailboxMetadataBySigningKey, err := db.Prepare(`SELECT 230 "serial", 231 "hashed_signing_key", 232 "signing_key", 233 "signing_key_type", 234 "encryption_key", 235 "encryption_key_type", 236 "expiration", 237 "info" 238 FROM taler_mailbox.mailbox_metadata 239 WHERE 240 "hashed_signing_key"=$1 241 LIMIT 1 242 ;`) 243 if err != nil { 244 return nil, err 245 } 246 getInboxEntryBySigningKeyAndBodyStmt, err := db.Prepare(`SELECT 247 "serial", 248 "hashed_signing_key", 249 "body" 250 FROM taler_mailbox.inbox_entries 251 WHERE 252 "hashed_signing_key"=$1 AND 253 "body"=$2 254 ;`) 255 if err != nil { 256 return nil, err 257 } 258 getInboxEntryBySerialStmt, err := db.Prepare(`SELECT 259 "serial", 260 "hashed_signing_key", 261 "body" 262 FROM taler_mailbox.inbox_entries 263 WHERE 264 "serial"=$1 AND 265 "hashed_signing_key"=$2 266 ;`) 267 if err != nil { 268 return nil, err 269 } 270 deletePendingRegistrationStmt, err := db.Prepare(`DELETE 271 FROM taler_mailbox.pending_mailbox_registrations 272 WHERE 273 "serial" = $1 274 ;`) 275 if err != nil { 276 return nil, err 277 } 278 deleteInboxEntryBySerialStmt, err := db.Prepare(`DELETE FROM taler_mailbox.inbox_entries 279 WHERE serial IN ( 280 SELECT serial FROM taler_mailbox.inbox_entries 281 WHERE 282 "hashed_signing_key"=$1 AND 283 "serial">=$2 284 LIMIT $3 285 );`) 286 if err != nil { 287 return nil, err 288 } 289 deleteStaleRegistrationsStmt, err := db.Prepare(`DELETE 290 FROM taler_mailbox.mailbox_metadata 291 WHERE 292 "expiration" < $1 293 ;`) 294 if err != nil { 295 return nil, err 296 } 297 deleteStalePendingRegistrationsStmt, err := db.Prepare(`DELETE 298 FROM taler_mailbox.pending_mailbox_registrations 299 WHERE 300 "created_at" < $1 301 ;`) 302 if err != nil { 303 return nil, err 304 } 305 getMessagesBySigningKeyStmt, err := db.Prepare(`SELECT 306 "serial", 307 "hashed_signing_key", 308 "body" 309 FROM taler_mailbox.inbox_entries 310 WHERE 311 "hashed_signing_key" = $1 312 LIMIT $2 313 ;`) 314 if err != nil { 315 return nil, err 316 } 317 countMessagesBySigningKeyStmt, err := db.Prepare(`SELECT COUNT(*) AS num_messages 318 FROM taler_mailbox.inbox_entries 319 WHERE 320 "hashed_signing_key"=$1 321 ;`) 322 if err != nil { 323 return nil, err 324 } 325 return &MailboxDatabase{ 326 db: db, 327 insertInboxEntryStmt: insertInboxEntryStmt, 328 insertPendingRegistrationStmt: insertPendingRegistrationStmt, 329 insertMailboxRegistrationStmt: insertMailboxRegistrationStmt, 330 updatePendingRegistrationOrderIdStmt: updatePendingRegistrationOrderIdStmt, 331 updateMailboxExpirationStmt: updateMailboxExpirationStmt, 332 getPendingMailboxRegistrationBySigningKeyStmt: getPendingRegistrationBySingingKeyStmt, 333 getMailboxMetadataBySigningKeyStmt: getMailboxMetadataBySigningKey, 334 getInboxEntryBySigningKeyAndBodyStmt: getInboxEntryBySigningKeyAndBodyStmt, 335 getInboxEntryBySerialStmt: getInboxEntryBySerialStmt, 336 deletePendingRegistrationStmt: deletePendingRegistrationStmt, 337 deleteInboxEntryBySerialStmt: deleteInboxEntryBySerialStmt, 338 deleteStaleRegistrationsStmt: deleteStaleRegistrationsStmt, 339 deleteStalePendingRegistrationsStmt: deleteStalePendingRegistrationsStmt, 340 getMessagesBySigningKeyStmt: getMessagesBySigningKeyStmt, 341 countMessagesBySigningKeyStmt: countMessagesBySigningKeyStmt, 342 }, nil 343 } 344 345 func (db *MailboxDatabase) InsertInboxEntry(e *InboxEntry) error { 346 rows, err := db.insertInboxEntryStmt.Query(e.HashedSigningKey, e.Body) 347 if err != nil { 348 return err 349 } 350 defer rows.Close() 351 return nil 352 } 353 354 func (db *MailboxDatabase) InsertPendingMailboxRegistration(pr *PendingMailboxRegistration) error { 355 pr.CreatedAt = time.Now().Unix() 356 rows, err := db.insertPendingRegistrationStmt.Query(pr.CreatedAt, pr.HashedSigningKey, pr.OrderID, pr.Duration) 357 if err != nil { 358 return err 359 } 360 defer rows.Close() 361 return nil 362 } 363 364 func (db *MailboxDatabase) InsertMailboxRegistration(mb *MailboxMetadata) error { 365 rows, err := db.insertMailboxRegistrationStmt.Query(mb.HashedSigningKey, mb.SigningKey, mb.SigningKeyType, mb.EncryptionKey, mb.EncryptionKeyType, mb.Expiration.Seconds, mb.Info) 366 if err != nil { 367 return err 368 } 369 defer rows.Close() 370 return nil 371 } 372 373 func (db *MailboxDatabase) UpdatePendingMailboxRegistrationOrderId(pr *PendingMailboxRegistration) error { 374 rows, err := db.updatePendingRegistrationOrderIdStmt.Query(pr.HashedSigningKey, pr.OrderID) 375 if err != nil { 376 return err 377 } 378 defer rows.Close() 379 return nil 380 } 381 382 func (db *MailboxDatabase) UpdateMailboxExpiration(mb *MailboxMetadata) error { 383 rows, err := db.updateMailboxExpirationStmt.Query(mb.HashedSigningKey, mb.Expiration.Seconds) 384 if err != nil { 385 return err 386 } 387 defer rows.Close() 388 return nil 389 } 390 391 func (db *MailboxDatabase) GetPendingMailboxRegistrationBySigningKey(pr *PendingMailboxRegistration, hashedKey string) error { 392 // Execute Query 393 rows, err := db.getPendingMailboxRegistrationBySigningKeyStmt.Query(hashedKey) 394 if err != nil { 395 return err 396 } 397 defer rows.Close() 398 // Iterate over first 399 if !rows.Next() { 400 return errors.New("mailbox metadata does not exist") 401 } 402 return rows.Scan( 403 &pr.Serial, 404 &pr.HashedSigningKey, 405 &pr.Duration, 406 &pr.OrderID, 407 ) 408 } 409 410 func (db *MailboxDatabase) GetMailboxMetadataBySigningKey(mb *MailboxMetadata, hashedKey string) error { 411 // Execute Query 412 rows, err := db.getMailboxMetadataBySigningKeyStmt.Query(hashedKey) 413 if err != nil { 414 return err 415 } 416 defer rows.Close() 417 // Iterate over first 418 if !rows.Next() { 419 return errors.New("Mailbox metadata does not exist") 420 } 421 return rows.Scan( 422 &mb.Serial, 423 &mb.HashedSigningKey, 424 &mb.SigningKey, 425 &mb.SigningKeyType, 426 &mb.EncryptionKey, 427 &mb.EncryptionKeyType, 428 &mb.Expiration.Seconds, 429 &mb.Info, 430 ) 431 } 432 433 func (db *MailboxDatabase) GetInboxEntryBySigningKeyAndBody(e *InboxEntry, hashedKey string, body []byte) error { 434 // Execute Query 435 rows, err := db.getInboxEntryBySigningKeyAndBodyStmt.Query(hashedKey, body) 436 if err != nil { 437 return err 438 } 439 defer rows.Close() 440 // Iterate over first 441 if !rows.Next() { 442 return errors.New("inbox entry does not exist") 443 } 444 return rows.Scan( 445 &e.Serial, 446 &e.HashedSigningKey, 447 &e.Body, 448 ) 449 } 450 451 func (db *MailboxDatabase) GetInboxEntryBySerial(e *InboxEntry, hashedKey string, serial int64) error { 452 // Execute Query 453 rows, err := db.getInboxEntryBySerialStmt.Query(serial, hashedKey) 454 if err != nil { 455 return err 456 } 457 defer rows.Close() 458 // Iterate over first 459 if !rows.Next() { 460 return errors.New("inbox entry does not exist") 461 } 462 return rows.Scan( 463 &e.Serial, 464 &e.HashedSigningKey, 465 &e.Body, 466 ) 467 } 468 469 func (db *MailboxDatabase) DeletePendingRegistration(pr *PendingMailboxRegistration) (int64, error) { 470 var ctx context.Context 471 ctx, stop := context.WithCancel(context.Background()) 472 defer stop() 473 conn, err := db.db.Conn(ctx) 474 if err != nil { 475 return 0, err 476 } 477 defer conn.Close() 478 // Execute Query 479 result, err := db.deletePendingRegistrationStmt.ExecContext(ctx, pr.Serial) 480 if err != nil { 481 return 0, err 482 } 483 rows, err := result.RowsAffected() 484 if err != nil { 485 return 0, err 486 } 487 return rows, nil 488 } 489 490 // DeleteInboxEntryBySerial Deletes all entries starting from given serial 491 func (db *MailboxDatabase) DeleteInboxEntryBySerial(e *InboxEntry, count int) (int64, error) { 492 var ctx context.Context 493 ctx, stop := context.WithCancel(context.Background()) 494 defer stop() 495 conn, err := db.db.Conn(ctx) 496 if err != nil { 497 return 0, err 498 } 499 defer conn.Close() 500 // Execute Query 501 result, err := db.deleteInboxEntryBySerialStmt.ExecContext(ctx, e.HashedSigningKey, e.Serial, count) 502 if err != nil { 503 return 0, err 504 } 505 rows, err := result.RowsAffected() 506 if err != nil { 507 return 0, err 508 } 509 return rows, nil 510 } 511 512 // DeleteStaleRegstrations purges stale registrations 513 func (db *MailboxDatabase) DeleteStaleRegistrations(registrationExpiration time.Time) (int64, error) { 514 var ctx context.Context 515 ctx, stop := context.WithCancel(context.Background()) 516 defer stop() 517 conn, err := db.db.Conn(ctx) 518 if err != nil { 519 return 0, err 520 } 521 defer conn.Close() 522 // Execute Query 523 result, err := db.deleteStaleRegistrationsStmt.ExecContext(ctx, registrationExpiration.Unix()) 524 if err != nil { 525 return 0, err 526 } 527 rows, err := result.RowsAffected() 528 if err != nil { 529 return 0, err 530 } 531 return rows, nil 532 } 533 534 // DeleteStalePendingRegstrations purges stale registrations 535 func (db *MailboxDatabase) DeleteStalePendingRegistrations(registrationExpiration time.Time) (int64, error) { 536 var ctx context.Context 537 ctx, stop := context.WithCancel(context.Background()) 538 defer stop() 539 conn, err := db.db.Conn(ctx) 540 if err != nil { 541 return 0, err 542 } 543 defer conn.Close() 544 // Execute Query 545 result, err := db.deleteStalePendingRegistrationsStmt.ExecContext(ctx, registrationExpiration.Unix()) 546 if err != nil { 547 return 0, err 548 } 549 rows, err := result.RowsAffected() 550 if err != nil { 551 return 0, err 552 } 553 return rows, nil 554 } 555 556 func (db *MailboxDatabase) DeleteAllPendingRegistrations() (int64, error) { 557 var ctx context.Context 558 ctx, stop := context.WithCancel(context.Background()) 559 defer stop() 560 conn, err := db.db.Conn(ctx) 561 if err != nil { 562 return 0, err 563 } 564 defer conn.Close() 565 query := `DELETE 566 FROM taler_mailbox.pending_mailbox_registrations 567 WHERE 568 1=1 569 ;` 570 // Execute Query 571 result, err := conn.ExecContext(ctx, query) 572 if err != nil { 573 return 0, err 574 } 575 rows, err := result.RowsAffected() 576 if err != nil { 577 return 0, err 578 } 579 return rows, nil 580 } 581 582 func (db *MailboxDatabase) DeleteAllMailboxes() (int64, error) { 583 var ctx context.Context 584 ctx, stop := context.WithCancel(context.Background()) 585 defer stop() 586 conn, err := db.db.Conn(ctx) 587 if err != nil { 588 return 0, err 589 } 590 defer conn.Close() 591 query := `DELETE 592 FROM taler_mailbox.mailbox_metadata 593 WHERE 594 1=1 595 ;` 596 // Execute Query 597 result, err := conn.ExecContext(ctx, query) 598 if err != nil { 599 return 0, err 600 } 601 rows, err := result.RowsAffected() 602 if err != nil { 603 return 0, err 604 } 605 return rows, nil 606 } 607 608 func (db *MailboxDatabase) DeleteAllInboxEntries() (int64, error) { 609 var ctx context.Context 610 ctx, stop := context.WithCancel(context.Background()) 611 defer stop() 612 conn, err := db.db.Conn(ctx) 613 if err != nil { 614 return 0, err 615 } 616 defer conn.Close() 617 query := `DELETE 618 FROM taler_mailbox.inbox_entries 619 WHERE 620 1=1 621 ;` 622 // Execute Query 623 result, err := conn.ExecContext(ctx, query) 624 if err != nil { 625 return 0, err 626 } 627 rows, err := result.RowsAffected() 628 if err != nil { 629 return 0, err 630 } 631 return rows, nil 632 } 633 634 // Get Hash-salted alias from database 635 func (db *MailboxDatabase) GetMessages(hashedKey string, limit int) ([]InboxEntry, error) { 636 // Execute Query 637 rows, err := db.getMessagesBySigningKeyStmt.Query(hashedKey, limit) 638 if err != nil { 639 return []InboxEntry{}, err 640 } 641 defer rows.Close() 642 var entries = make([]InboxEntry, 0) 643 for rows.Next() { 644 var e InboxEntry 645 err = rows.Scan( 646 &e.Serial, 647 &e.HashedSigningKey, 648 &e.Body, 649 ) 650 if err != nil { 651 return entries, err 652 } 653 entries = append(entries, e) 654 } 655 return entries, nil 656 } 657 658 // Get Hash-salted alias from database 659 func (db *MailboxDatabase) GetMessagesCount(hashedKey string) (int64, error) { 660 // Execute Query 661 rows, err := db.countMessagesBySigningKeyStmt.Query(hashedKey) 662 if err != nil { 663 return 0, err 664 } 665 defer rows.Close() 666 if !rows.Next() { 667 return 0, nil 668 } 669 var res int64 670 err = rows.Scan( 671 &res, 672 ) 673 if err != nil { 674 return 0, err 675 } 676 return res, nil 677 }