plugin_challengerdb_postgres.c (13035B)
1 /* 2 This file is part of Challenger 3 (C) 2023 Taler Systems SA 4 5 Challenger is free software; you can redistribute it and/or modify it under the 6 terms of the GNU Lesser General Public License as published by the Free Software 7 Foundation; either version 3, or (at your option) any later version. 8 9 Challenger is distributed in the hope that it will be useful, but WITHOUT ANY 10 WARRANTY; without even the implied warranty of ANASTASISABILITY or FITNESS FOR 11 A PARTICULAR PURPOSE. See the GNU General Public License for more details. 12 13 You should have received a copy of the GNU General Public License along with 14 Challenger; see the file COPYING. If not, see <http://www.gnu.org/licenses/> 15 */ 16 /** 17 * @file challengerdb/plugin_challengerdb_postgres.c 18 * @brief database helper functions for postgres used by challenger 19 * @author Christian Grothoff 20 */ 21 #include "platform.h" 22 #include <gnunet/gnunet_util_lib.h> 23 #include <gnunet/gnunet_db_lib.h> 24 #include <gnunet/gnunet_pq_lib.h> 25 #include <taler/taler_pq_lib.h> 26 #include "challenger_database_plugin.h" 27 #include "challenger_database_lib.h" 28 #include "pg_helper.h" 29 #include "pg_address_get.h" 30 #include "pg_client_add.h" 31 #include "pg_client_modify.h" 32 #include "pg_client_delete.h" 33 #include "pg_info_get_token.h" 34 #include "pg_token_add_token.h" 35 #include "pg_client_check.h" 36 #include "pg_setup_nonce.h" 37 #include "pg_authorize_start.h" 38 #include "pg_challenge_set_address_and_pin.h" 39 #include "pg_validate_solve_pin.h" 40 #include "pg_validation_get.h" 41 #include "pg_validation_get_pkce.h" 42 43 /** 44 * Drop challenger tables 45 * 46 * @param cls closure our `struct Plugin` 47 * @return #GNUNET_OK upon success; #GNUNET_SYSERR upon failure 48 */ 49 static enum GNUNET_GenericReturnValue 50 postgres_drop_tables (void *cls) 51 { 52 struct PostgresClosure *pg = cls; 53 struct GNUNET_PQ_Context *conn; 54 enum GNUNET_GenericReturnValue ret; 55 56 if (NULL != pg->conn) 57 { 58 GNUNET_PQ_disconnect (pg->conn); 59 pg->conn = NULL; 60 } 61 conn = GNUNET_PQ_connect_with_cfg (pg->cfg, 62 "challengerdb-postgres", 63 NULL, 64 NULL, 65 NULL); 66 if (NULL == conn) 67 return GNUNET_SYSERR; 68 ret = GNUNET_PQ_exec_sql (conn, 69 "drop"); 70 GNUNET_PQ_disconnect (conn); 71 return ret; 72 } 73 74 75 /** 76 * Roll back the current transaction of a database connection. 77 * 78 * @param cls the `struct PostgresClosure` with the plugin-specific state 79 */ 80 static void 81 postgres_rollback (void *cls) 82 { 83 struct PostgresClosure *pg = cls; 84 struct GNUNET_PQ_ExecuteStatement es[] = { 85 GNUNET_PQ_make_execute ("ROLLBACK"), 86 GNUNET_PQ_EXECUTE_STATEMENT_END 87 }; 88 89 if (GNUNET_OK != 90 GNUNET_PQ_exec_statements (pg->conn, 91 es)) 92 { 93 TALER_LOG_ERROR ("Failed to rollback transaction\n"); 94 GNUNET_break (0); 95 } 96 pg->transaction_name = NULL; 97 } 98 99 100 /** 101 * Connect to the database if the connection does not exist yet. 102 * 103 * @param pg the plugin-specific state 104 * @return #GNUNET_OK on success 105 */ 106 static enum GNUNET_GenericReturnValue 107 internal_setup (struct PostgresClosure *pg) 108 { 109 if (NULL == pg->conn) 110 { 111 #if AUTO_EXPLAIN 112 /* Enable verbose logging to see where queries do not 113 properly use indices */ 114 struct GNUNET_PQ_ExecuteStatement es[] = { 115 GNUNET_PQ_make_try_execute ("LOAD 'auto_explain';"), 116 GNUNET_PQ_make_try_execute ("SET auto_explain.log_min_duration=50;"), 117 GNUNET_PQ_make_try_execute ("SET auto_explain.log_timing=TRUE;"), 118 GNUNET_PQ_make_try_execute ("SET auto_explain.log_analyze=TRUE;"), 119 /* https://wiki.postgresql.org/wiki/Serializable suggests to really 120 force the default to 'serializable' if SSI is to be used. */ 121 GNUNET_PQ_make_try_execute ( 122 "SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE;"), 123 GNUNET_PQ_make_execute ("SET search_path TO challenger;"), 124 GNUNET_PQ_EXECUTE_STATEMENT_END 125 }; 126 #else 127 struct GNUNET_PQ_ExecuteStatement es[] = { 128 GNUNET_PQ_make_execute ("SET search_path TO challenger;"), 129 GNUNET_PQ_EXECUTE_STATEMENT_END 130 }; 131 #endif 132 struct GNUNET_PQ_Context *db_conn; 133 134 db_conn = GNUNET_PQ_connect_with_cfg2 (pg->cfg, 135 "challengerdb-postgres", 136 "challenger-", 137 es, 138 NULL, 139 GNUNET_PQ_FLAG_CHECK_CURRENT); 140 if (NULL == db_conn) 141 return GNUNET_SYSERR; 142 pg->conn = db_conn; 143 pg->prep_gen++; 144 } 145 if (NULL == pg->transaction_name) 146 GNUNET_PQ_reconnect_if_down (pg->conn); 147 return GNUNET_OK; 148 } 149 150 151 /** 152 * Do a pre-flight check that we are not in an uncommitted transaction. 153 * If we are, try to commit the previous transaction and output a warning. 154 * Does not return anything, as we will continue regardless of the outcome. 155 * 156 * @param cls the `struct PostgresClosure` with the plugin-specific state 157 * @return #GNUNET_OK if everything is fine 158 * #GNUNET_NO if a transaction was rolled back 159 * #GNUNET_SYSERR on hard errors 160 */ 161 static enum GNUNET_GenericReturnValue 162 postgres_preflight (void *cls) 163 { 164 struct PostgresClosure *pg = cls; 165 struct GNUNET_PQ_ExecuteStatement es[] = { 166 GNUNET_PQ_make_execute ("ROLLBACK"), 167 GNUNET_PQ_EXECUTE_STATEMENT_END 168 }; 169 170 if (NULL == pg->conn) 171 { 172 if (GNUNET_OK != 173 internal_setup (pg)) 174 { 175 GNUNET_log (GNUNET_ERROR_TYPE_ERROR, 176 "Failed to ensure DB is initialized\n"); 177 return GNUNET_SYSERR; 178 } 179 } 180 GNUNET_PQ_reconnect_if_down (pg->conn); 181 if (NULL == pg->transaction_name) 182 return GNUNET_OK; /* all good */ 183 if (GNUNET_OK == 184 GNUNET_PQ_exec_statements (pg->conn, 185 es)) 186 { 187 GNUNET_log (GNUNET_ERROR_TYPE_ERROR, 188 "BUG: Preflight check rolled back transaction `%s'!\n", 189 pg->transaction_name); 190 } 191 else 192 { 193 GNUNET_log (GNUNET_ERROR_TYPE_ERROR, 194 "BUG: Preflight check failed to rollback transaction `%s'!\n", 195 pg->transaction_name); 196 } 197 pg->transaction_name = NULL; 198 return GNUNET_NO; 199 } 200 201 202 /** 203 * Check that the database connection is still up. 204 * 205 * @param cls a `struct PostgresClosure` with connection to check 206 */ 207 void 208 CH_PG_check_connection (void *cls) 209 { 210 struct PostgresClosure *pg = cls; 211 212 GNUNET_PQ_reconnect_if_down (pg->conn); 213 } 214 215 216 /** 217 * Start a transaction. 218 * 219 * @param cls the `struct PostgresClosure` with the plugin-specific state 220 * @param name unique name identifying the transaction (for debugging), 221 * must point to a constant 222 * @return #GNUNET_OK on success 223 */ 224 static enum GNUNET_GenericReturnValue 225 postgres_begin_transaction (void *cls, 226 const char *name) 227 { 228 struct PostgresClosure *pg = cls; 229 struct GNUNET_PQ_ExecuteStatement es[] = { 230 GNUNET_PQ_make_execute ("START TRANSACTION ISOLATION LEVEL SERIALIZABLE"), 231 GNUNET_PQ_EXECUTE_STATEMENT_END 232 }; 233 234 CH_PG_check_connection (pg); 235 postgres_preflight (pg); 236 pg->transaction_name = name; 237 if (GNUNET_OK != 238 GNUNET_PQ_exec_statements (pg->conn, 239 es)) 240 { 241 TALER_LOG_ERROR ("Failed to start transaction\n"); 242 GNUNET_break (0); 243 return GNUNET_SYSERR; 244 } 245 return GNUNET_OK; 246 } 247 248 249 /** 250 * Commit the current transaction of a database connection. 251 * 252 * @param cls the `struct PostgresClosure` with the plugin-specific state 253 * @return transaction status code 254 */ 255 static enum GNUNET_DB_QueryStatus 256 postgres_commit_transaction (void *cls) 257 { 258 struct PostgresClosure *pg = cls; 259 enum GNUNET_DB_QueryStatus qs; 260 struct GNUNET_PQ_QueryParam no_params[] = { 261 GNUNET_PQ_query_param_end 262 }; 263 264 PREPARE (pg, 265 "do_commit", 266 "COMMIT"); 267 qs = GNUNET_PQ_eval_prepared_non_select (pg->conn, 268 "do_commit", 269 no_params); 270 pg->transaction_name = NULL; 271 return qs; 272 } 273 274 275 /** 276 * Function called to perform "garbage collection" on the 277 * database, expiring records we no longer require. 278 * 279 * @param cls closure 280 * @param expire older than the given time stamp should be garbage collected 281 * @return transaction status 282 */ 283 static enum GNUNET_DB_QueryStatus 284 postgres_gc (void *cls, 285 struct GNUNET_TIME_Absolute expire) 286 { 287 struct PostgresClosure *pg = cls; 288 struct GNUNET_PQ_QueryParam params[] = { 289 GNUNET_PQ_query_param_absolute_time (&expire), 290 GNUNET_PQ_query_param_end 291 }; 292 enum GNUNET_DB_QueryStatus qs; 293 294 CH_PG_check_connection (pg); 295 PREPARE (pg, 296 "gc_validations", 297 "DELETE FROM validations" 298 " WHERE expiration_time < $1;"); 299 PREPARE (pg, 300 "gc_tokens", 301 "DELETE FROM tokens" 302 " WHERE token_expiration_time < $1;"); 303 postgres_preflight (pg); 304 qs = GNUNET_PQ_eval_prepared_non_select (pg->conn, 305 "gc_validations", 306 params); 307 if (qs < 0) 308 return qs; 309 return GNUNET_PQ_eval_prepared_non_select (pg->conn, 310 "gc_tokens", 311 params); 312 } 313 314 315 /** 316 * Initialize tables. 317 * 318 * @param cls the `struct PostgresClosure` with the plugin-specific state 319 * @return #GNUNET_OK upon success; #GNUNET_SYSERR upon failure 320 */ 321 static enum GNUNET_GenericReturnValue 322 postgres_create_tables (void *cls) 323 { 324 struct PostgresClosure *pc = cls; 325 struct GNUNET_PQ_Context *conn; 326 struct GNUNET_PQ_ExecuteStatement es[] = { 327 GNUNET_PQ_make_execute ("SET search_path TO challenger;"), 328 GNUNET_PQ_EXECUTE_STATEMENT_END 329 }; 330 enum GNUNET_GenericReturnValue ret; 331 332 conn = GNUNET_PQ_connect_with_cfg (pc->cfg, 333 "challengerdb-postgres", 334 "challenger-", 335 es, 336 NULL); 337 if (NULL == conn) 338 return GNUNET_SYSERR; 339 ret = GNUNET_PQ_exec_sql (conn, 340 "procedures"); 341 GNUNET_PQ_disconnect (conn); 342 return ret; 343 } 344 345 346 /** 347 * Initialize Postgres database subsystem. 348 * 349 * @param cls a configuration instance 350 * @return NULL on error, otherwise a `struct TALER_CHALLENGERDB_Plugin` 351 */ 352 void * 353 libchallenger_plugin_db_postgres_init (void *cls); 354 355 /* Declaration to suppress compiler warning */ 356 void * 357 libchallenger_plugin_db_postgres_init (void *cls) 358 { 359 struct GNUNET_CONFIGURATION_Handle *cfg = cls; 360 struct PostgresClosure *pg; 361 struct CHALLENGER_DatabasePlugin *plugin; 362 363 pg = GNUNET_new (struct PostgresClosure); 364 pg->cfg = cfg; 365 if (GNUNET_OK != 366 GNUNET_CONFIGURATION_get_value_filename (cfg, 367 "challengerdb-postgres", 368 "SQL_DIR", 369 &pg->sql_dir)) 370 { 371 GNUNET_log_config_missing (GNUNET_ERROR_TYPE_ERROR, 372 "challengerdb-postgres", 373 "SQL_DIR"); 374 GNUNET_free (pg); 375 return NULL; 376 } 377 plugin = GNUNET_new (struct CHALLENGER_DatabasePlugin); 378 plugin->cls = pg; 379 plugin->create_tables 380 = &postgres_create_tables; 381 plugin->drop_tables 382 = &postgres_drop_tables; 383 plugin->preflight 384 = &postgres_preflight; 385 plugin->gc 386 = &postgres_gc; 387 plugin->begin_transaction 388 = &postgres_begin_transaction; 389 plugin->commit_transaction 390 = &postgres_commit_transaction; 391 plugin->rollback 392 = &postgres_rollback; 393 plugin->address_get 394 = &CH_PG_address_get; 395 plugin->client_add 396 = &CH_PG_client_add; 397 plugin->client_modify 398 = &CH_PG_client_modify; 399 plugin->client_delete 400 = &CH_PG_client_delete; 401 plugin->client_check 402 = &CH_PG_client_check; 403 plugin->client_check2 404 = &CH_PG_client_check2; 405 plugin->setup_nonce 406 = &CH_PG_setup_nonce; 407 plugin->authorize_start 408 = &CH_PG_authorize_start; 409 plugin->challenge_set_address_and_pin 410 = &CH_PG_challenge_set_address_and_pin; 411 plugin->validate_solve_pin 412 = &CH_PG_validate_solve_pin; 413 plugin->validation_get 414 = &CH_PG_validation_get; 415 plugin->validation_get_pkce 416 = &CH_PG_validation_get_pkce; 417 plugin->info_get_token 418 = &CH_PG_info_get_token; 419 plugin->token_add_token 420 = &CH_PG_token_add_token; 421 return plugin; 422 } 423 424 425 /** 426 * Shutdown Postgres database subsystem. 427 * 428 * @param cls a `struct CHALLENGER_DB_Plugin` 429 * @return NULL (always) 430 */ 431 void * 432 libchallenger_plugin_db_postgres_done (void *cls); 433 434 /* Declaration to suppress compiler warning */ 435 void * 436 libchallenger_plugin_db_postgres_done (void *cls) 437 { 438 struct CHALLENGER_DatabasePlugin *plugin = cls; 439 struct PostgresClosure *pg = plugin->cls; 440 441 GNUNET_PQ_disconnect (pg->conn); 442 GNUNET_free (pg->sql_dir); 443 GNUNET_free (pg); 444 GNUNET_free (plugin); 445 return NULL; 446 } 447 448 449 /* end of plugin_challengerdb_postgres.c */