db.go (15805B)
1 // This file is part of tdir, the Taler Directory implementation. 2 // Copyright (C) 2025 Martin Schanzenbach 3 // 4 // Taldir 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 // Taldir 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 taldir 20 21 import ( 22 "context" 23 "database/sql" 24 "errors" 25 "fmt" 26 "log" 27 "strings" 28 "time" 29 30 _ "github.com/lib/pq" 31 talerutil "github.com/schanzen/taler-go/pkg/util" 32 ) 33 34 // Validation is the object created when a registration for an entry is initiated. 35 // The Validation stores the identity key (sha256(identity)) the secret 36 // Validation reference. The Validation reference is sent to the identity 37 // depending on the out-of-band channel defined through the identity key type. 38 type Validation struct { 39 // When was this entry created in microseconds 40 CreatedAt int64 `json:"-"` 41 42 // The hash (SHA512) of the alias 43 HAlias string `json:"h_alias"` 44 45 // For how long should the registration last 46 Duration int64 `json:"duration"` 47 48 // Target URI to associate with this alias 49 TargetURI string `json:"target_uri"` 50 51 // The activation code sent to the client 52 Challenge string `json:"-"` 53 54 // The challenge has been sent already 55 ChallengeSent bool `json:"-"` 56 57 // true if this validation also requires payment 58 RequiresPayment bool `json:"-"` 59 60 // How often was a solution for this validation tried 61 SolutionAttemptCount int 62 63 // The beginning of the last solution timeframe 64 LastSolutionTimeframeStart int64 65 66 // The order ID associated with this validation 67 OrderID string `json:"-"` 68 69 // Name of the validator 70 ValidatorName string 71 } 72 73 // Entry is a mapping from the alias hash to a target URI 74 type Entry struct { 75 // When was this entry created in microseconds 76 CreatedAt int64 `json:"-"` 77 78 // The salted hash (SHA512) of the hashed alias 79 HsAlias string `json:"-"` 80 81 // Target URI to associate with this alias 82 TargetURI string `json:"target_uri"` 83 84 // How long the registration lasts in microseconds 85 Duration int64 `json:"-"` 86 } 87 88 // TaldirDatabase is the main taldir database connection handle 89 type TaldirDatabase struct { 90 // SQL connection 91 db *sql.DB 92 93 // Get entry statement 94 getEntryByHAliasStmt *sql.Stmt 95 96 // Insert entry statement 97 insertEntryStmt *sql.Stmt 98 99 // Update entry statement 100 updateEntryStmt *sql.Stmt 101 102 // Delete entry statement 103 deleteEntryStmt *sql.Stmt 104 105 // Get validation from database 106 getValidationStmt *sql.Stmt 107 108 // Get all validations from database 109 getAllValidationsByHAliasStmt *sql.Stmt 110 111 // Get first validation for a specific HAlias 112 getFirstValidationByHAliasStmt *sql.Stmt 113 114 // Insert validation 115 insertValidationStmt *sql.Stmt 116 117 // Update validation statement 118 updateValidationStmt *sql.Stmt 119 120 // Delete validation 121 deleteValidationsByHAliasStmt *sql.Stmt 122 123 // Delete stale validations 124 deleteStaleValidationsStmt *sql.Stmt 125 } 126 127 func (db *TaldirDatabase) Close() { 128 for _, s := range []*sql.Stmt{ 129 db.getEntryByHAliasStmt, 130 db.insertEntryStmt, 131 db.updateEntryStmt, 132 db.deleteEntryStmt, 133 db.getValidationStmt, 134 db.getAllValidationsByHAliasStmt, 135 db.getFirstValidationByHAliasStmt, 136 db.insertValidationStmt, 137 db.updateValidationStmt, 138 db.deleteValidationsByHAliasStmt, 139 db.deleteStaleValidationsStmt, 140 } { 141 if s != nil { 142 s.Close() 143 } 144 } 145 db.db.Close() 146 } 147 148 func OpenDatabase(psqlconn string) (*TaldirDatabase, error) { 149 db, err := sql.Open("postgres", psqlconn) 150 if err != nil { 151 return nil, err 152 } 153 segments := strings.Split(strings.Split(psqlconn, "?")[0], "/") 154 dbName := segments[len(segments)-1] 155 156 err = talerutil.DBInit(db, "../..", dbName, "taler-directory") 157 if err != nil { 158 log.Fatalf("Failed to apply versioning or patches: %v", err) 159 } 160 insertEntryStmt, err := db.Prepare(`INSERT INTO taler_directory.entries 161 VALUES (DEFAULT, $1, $2, $3, $4);`) 162 if err != nil { 163 log.Panic(err) 164 return nil, err 165 } 166 updateEntryStmt, err := db.Prepare(`UPDATE taler_directory.entries 167 SET 168 "created_at" = $2, 169 "target_uri" = $3, 170 "duration" = $4 171 WHERE "hs_alias" = $1;`) 172 if err != nil { 173 return nil, err 174 } 175 deleteEntryStmt, err := db.Prepare(`DELETE 176 FROM taler_directory.entries 177 WHERE 178 "hs_alias" = $1 179 ;`) 180 if err != nil { 181 return nil, err 182 } 183 updateValidationStmt, err := db.Prepare(`UPDATE taler_directory.validations 184 SET 185 "created_at" = $2, 186 "duration" = $3, 187 "target_uri" = $4, 188 "challenge" = $5, 189 "challenge_sent" = $6, 190 "requires_payment" = $7, 191 "solution_attempt_count" = $8, 192 "last_solution_timeframe_start" = $9, 193 "order_id" = $10, 194 "validator_name" = $11 195 WHERE "h_alias" = $1;`) 196 if err != nil { 197 return nil, err 198 } 199 insertValidationStmt, err := db.Prepare(`INSERT INTO taler_directory.validations 200 VALUES (DEFAULT, $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11);`) 201 if err != nil { 202 return nil, err 203 } 204 getValidationStmt, err := db.Prepare(`SELECT 205 "created_at", 206 "h_alias", 207 "duration", 208 "target_uri", 209 "challenge", 210 "challenge_sent", 211 "requires_payment", 212 "solution_attempt_count", 213 "last_solution_timeframe_start", 214 "order_id", 215 "validator_name" 216 FROM taler_directory.validations 217 WHERE 218 "h_alias"=$1 AND 219 "target_uri"=$2 AND 220 "duration"=$3 221 ;`) 222 if err != nil { 223 return nil, err 224 } 225 getAllValidationsByHAliasStmt, err := db.Prepare(`SELECT 226 "created_at", 227 "h_alias", 228 "duration", 229 "target_uri", 230 "challenge", 231 "challenge_sent", 232 "requires_payment", 233 "solution_attempt_count", 234 "last_solution_timeframe_start", 235 "order_id", 236 "validator_name" 237 FROM taler_directory.validations 238 WHERE 239 "h_alias" = $1 240 ;`) 241 if err != nil { 242 return nil, err 243 } 244 getFirstValidationByHAliasStmt, err := db.Prepare(`SELECT 245 "created_at", 246 "h_alias", 247 "duration", 248 "target_uri", 249 "challenge", 250 "challenge_sent", 251 "requires_payment", 252 "solution_attempt_count", 253 "last_solution_timeframe_start", 254 "order_id", 255 "validator_name" 256 FROM taler_directory.validations 257 WHERE 258 "h_alias"=$1 259 ;`) 260 if err != nil { 261 return nil, err 262 } 263 getEntryByHAliasStmt, err := db.Prepare(`SELECT 264 "hs_alias", 265 "created_at", 266 "target_uri", 267 "duration" 268 FROM taler_directory.entries 269 WHERE 270 "hs_alias"=$1 271 ;`) 272 if err != nil { 273 return nil, err 274 } 275 deleteStaleValidationsStmt, err := db.Prepare(`DELETE 276 FROM taler_directory.validations 277 WHERE 278 "created_at" < $1 279 ;`) 280 if err != nil { 281 return nil, err 282 } 283 deleteValidationsByHAliasStmt, err := db.Prepare(`DELETE 284 FROM taler_directory.validations 285 WHERE 286 "h_alias" = $1 287 ;`) 288 if err != nil { 289 return nil, err 290 } 291 return &TaldirDatabase{ 292 db: db, 293 getEntryByHAliasStmt: getEntryByHAliasStmt, 294 insertEntryStmt: insertEntryStmt, 295 updateEntryStmt: updateEntryStmt, 296 deleteEntryStmt: deleteEntryStmt, 297 getValidationStmt: getValidationStmt, 298 getAllValidationsByHAliasStmt: getAllValidationsByHAliasStmt, 299 getFirstValidationByHAliasStmt: getFirstValidationByHAliasStmt, 300 insertValidationStmt: insertValidationStmt, 301 updateValidationStmt: updateValidationStmt, 302 deleteStaleValidationsStmt: deleteStaleValidationsStmt, 303 deleteValidationsByHAliasStmt: deleteValidationsByHAliasStmt, 304 }, nil 305 } 306 307 // UpdateValidation updates a validation in database 308 func (db *TaldirDatabase) UpdateValidation(v *Validation) error { 309 rows, err := db.updateValidationStmt.Query(v.HAlias, v.CreatedAt, v.Duration, v.TargetURI, v.Challenge, v.ChallengeSent, v.RequiresPayment, v.SolutionAttemptCount, v.LastSolutionTimeframeStart, v.OrderID, v.ValidatorName) 310 if err != nil { 311 return err 312 } 313 defer rows.Close() 314 return nil 315 } 316 317 // InsertValidation inserts a new validation into database 318 func (db *TaldirDatabase) InsertValidation(v *Validation) error { 319 rows, err := db.insertValidationStmt.Query(v.CreatedAt, v.HAlias, v.Duration, v.TargetURI, v.Challenge, v.ChallengeSent, v.RequiresPayment, v.SolutionAttemptCount, v.LastSolutionTimeframeStart, v.OrderID, v.ValidatorName) 320 if err != nil { 321 return err 322 } 323 defer rows.Close() 324 return nil 325 } 326 327 // GetValidation gets a Validation from database 328 func (db *TaldirDatabase) GetValidation(v *Validation, hAlias string, targetURI string, duration time.Duration) error { 329 // Execute Query 330 rows, err := db.getValidationStmt.Query(hAlias, targetURI, duration.Microseconds()) 331 if err != nil { 332 return err 333 } 334 defer rows.Close() 335 // Iterate over first 336 if !rows.Next() { 337 fmt.Printf("error val %v\n", rows.Err()) 338 return errors.New("Validation does not exist") 339 } 340 return rows.Scan( 341 &v.CreatedAt, 342 &v.HAlias, 343 &v.Duration, 344 &v.TargetURI, 345 &v.Challenge, 346 &v.ChallengeSent, 347 &v.RequiresPayment, 348 &v.SolutionAttemptCount, 349 &v.LastSolutionTimeframeStart, 350 &v.OrderID, 351 &v.ValidatorName, 352 ) 353 } 354 355 // GetAllValidationsByHAlias gets all Validations by hash-salted alias from database 356 func (db *TaldirDatabase) GetAllValidationsByHAlias(hAlias string) ([]Validation, error) { 357 // Execute Query 358 rows, err := db.getAllValidationsByHAliasStmt.Query(hAlias) 359 if err != nil { 360 return []Validation{}, err 361 } 362 defer rows.Close() 363 var validations = make([]Validation, 0) 364 // Iterate over first 365 for rows.Next() { 366 var v Validation 367 err = rows.Scan( 368 &v.CreatedAt, 369 &v.HAlias, 370 &v.Duration, 371 &v.TargetURI, 372 &v.Challenge, 373 &v.ChallengeSent, 374 &v.RequiresPayment, 375 &v.SolutionAttemptCount, 376 &v.LastSolutionTimeframeStart, 377 &v.OrderID, 378 &v.ValidatorName, 379 ) 380 if err != nil { 381 return validations, err 382 } 383 validations = append(validations, v) 384 } 385 return validations, nil 386 } 387 388 // GetFirstValidationByHAlias gets the first Hash-salted alias from database 389 func (db *TaldirDatabase) GetFirstValidationByHAlias(v *Validation, hAlias string) error { 390 // Execute Query 391 rows, err := db.getFirstValidationByHAliasStmt.Query(hAlias) 392 if err != nil { 393 return err 394 } 395 defer rows.Close() 396 // Iterate over first 397 if !rows.Next() { 398 return errors.New("Validation not found") 399 } 400 return rows.Scan( 401 &v.CreatedAt, 402 &v.HAlias, 403 &v.Duration, 404 &v.TargetURI, 405 &v.Challenge, 406 &v.ChallengeSent, 407 &v.RequiresPayment, 408 &v.SolutionAttemptCount, 409 &v.LastSolutionTimeframeStart, 410 &v.OrderID, 411 &v.ValidatorName, 412 ) 413 } 414 415 // GetAllEntries gets all Hash-salted aliases from database 416 func (db *TaldirDatabase) GetAllEntries() ([]Entry, error) { 417 query := `SELECT 418 "hs_alias", 419 "created_at", 420 "target_uri", 421 "duration" 422 FROM taler_directory.entries 423 WHERE 424 1 = 1 425 ;` 426 // Execute Query 427 rows, err := db.db.Query(query) 428 if err != nil { 429 return []Entry{}, err 430 } 431 defer rows.Close() 432 var entries = make([]Entry, 0) 433 for rows.Next() { 434 var e Entry 435 err = rows.Scan( 436 &e.HsAlias, 437 &e.CreatedAt, 438 &e.TargetURI, 439 &e.Duration, 440 ) 441 if err != nil { 442 return entries, err 443 } 444 entries = append(entries, e) 445 } 446 return entries, nil 447 } 448 449 // GetEntryByHsAlias gets the Hash-salted alias from database 450 func (db *TaldirDatabase) GetEntryByHsAlias(e *Entry, hsAlias string) error { 451 // Execute Query 452 rows, err := db.getEntryByHAliasStmt.Query(hsAlias) 453 if err != nil { 454 return err 455 } 456 defer rows.Close() 457 // Iterate over first 458 if !rows.Next() { 459 return errors.New("Entry not found") 460 } 461 return rows.Scan( 462 &e.HsAlias, 463 &e.CreatedAt, 464 &e.TargetURI, 465 &e.Duration, 466 ) 467 } 468 469 // DeleteStaleValidations purges stale validations 470 func (db *TaldirDatabase) DeleteStaleValidations(validationExpiration time.Duration) (int64, error) { 471 var ctx context.Context 472 ctx, stop := context.WithCancel(context.Background()) 473 defer stop() 474 conn, err := db.db.Conn(ctx) 475 if err != nil { 476 return 0, err 477 } 478 defer conn.Close() 479 // Execute Query 480 cutoffTime := time.Now().Add(-validationExpiration) 481 result, err := db.deleteStaleValidationsStmt.ExecContext(ctx, cutoffTime.UnixMicro()) 482 if err != nil { 483 return 0, err 484 } 485 rows, err := result.RowsAffected() 486 if err != nil { 487 return 0, err 488 } 489 return rows, nil 490 } 491 492 func (db *TaldirDatabase) ClearDatabase() error { 493 _, err := db.DeleteAllEntries() 494 if err != nil { 495 return err 496 } 497 _, err = db.DeleteAllValidations() 498 return err 499 } 500 501 // DeleteValidationsByHAlias purges Validations 502 func (db *TaldirDatabase) DeleteValidationsByHAlias(hAlias string) (int64, error) { 503 var ctx context.Context 504 ctx, stop := context.WithCancel(context.Background()) 505 defer stop() 506 conn, err := db.db.Conn(ctx) 507 if err != nil { 508 return 0, err 509 } 510 defer conn.Close() 511 // Execute Query 512 result, err := db.deleteValidationsByHAliasStmt.ExecContext(ctx, hAlias) 513 if err != nil { 514 return 0, err 515 } 516 rows, err := result.RowsAffected() 517 if err != nil { 518 return 0, err 519 } 520 return rows, nil 521 } 522 523 // DeleteAllValidations purges all Validations 524 func (db *TaldirDatabase) DeleteAllValidations() (int64, error) { 525 var ctx context.Context 526 ctx, stop := context.WithCancel(context.Background()) 527 defer stop() 528 conn, err := db.db.Conn(ctx) 529 if err != nil { 530 return 0, err 531 } 532 defer conn.Close() 533 query := `DELETE 534 FROM taler_directory.validations 535 WHERE 536 1 = 1 537 ;` 538 // Execute Query 539 result, err := conn.ExecContext(ctx, query) 540 if err != nil { 541 return 0, err 542 } 543 rows, err := result.RowsAffected() 544 if err != nil { 545 return 0, err 546 } 547 return rows, nil 548 } 549 550 // DeleteValidation purges a Validation 551 func (db *TaldirDatabase) DeleteValidation(v *Validation) (int64, error) { 552 return db.DeleteValidationsByHAlias(v.HAlias) 553 } 554 555 // DeleteAllEntries purges Entries 556 func (db *TaldirDatabase) DeleteAllEntries() (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_directory.entries 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 // DeleteEntry deletes an Entry 583 func (db *TaldirDatabase) DeleteEntry(e *Entry) (int64, error) { 584 var ctx context.Context 585 ctx, stop := context.WithCancel(context.Background()) 586 defer stop() 587 conn, err := db.db.Conn(ctx) 588 if err != nil { 589 return 0, err 590 } 591 defer conn.Close() 592 // Execute Query 593 result, err := db.deleteEntryStmt.ExecContext(ctx, e.HsAlias) 594 if err != nil { 595 return 0, err 596 } 597 rows, err := result.RowsAffected() 598 if err != nil { 599 return 0, err 600 } 601 return rows, nil 602 } 603 604 var () 605 606 // UpdateEntry updates the Entry in database 607 func (db *TaldirDatabase) UpdateEntry(e *Entry) error { 608 rows, err := db.updateEntryStmt.Query(e.HsAlias, e.CreatedAt, e.TargetURI, e.Duration) 609 if err != nil { 610 return err 611 } 612 defer rows.Close() 613 return nil 614 } 615 616 // InsertEntry inserts new Entry into database 617 func (db *TaldirDatabase) InsertEntry(e *Entry) error { 618 var err error 619 e.CreatedAt = time.Now().UnixMicro() 620 rows, err := db.insertEntryStmt.Query(e.HsAlias, e.CreatedAt, e.TargetURI, e.Duration) 621 if err != nil { 622 return err 623 } 624 defer rows.Close() 625 return nil 626 }