1<?php 2/** 3 * PostgreSQL-specific installer. 4 * 5 * This program is free software; you can redistribute it and/or modify 6 * it under the terms of the GNU General Public License as published by 7 * the Free Software Foundation; either version 2 of the License, or 8 * (at your option) any later version. 9 * 10 * This program is distributed in the hope that it will be useful, 11 * but WITHOUT ANY WARRANTY; without even the implied warranty of 12 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 13 * GNU General Public License for more details. 14 * 15 * You should have received a copy of the GNU General Public License along 16 * with this program; if not, write to the Free Software Foundation, Inc., 17 * 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. 18 * http://www.gnu.org/copyleft/gpl.html 19 * 20 * @file 21 * @ingroup Installer 22 */ 23 24use Wikimedia\Rdbms\Database; 25use Wikimedia\Rdbms\DatabasePostgres; 26use Wikimedia\Rdbms\DBConnectionError; 27use Wikimedia\Rdbms\DBQueryError; 28 29/** 30 * Class for setting up the MediaWiki database using Postgres. 31 * 32 * @ingroup Installer 33 * @since 1.17 34 */ 35class PostgresInstaller extends DatabaseInstaller { 36 37 protected $globalNames = [ 38 'wgDBserver', 39 'wgDBport', 40 'wgDBname', 41 'wgDBuser', 42 'wgDBpassword', 43 'wgDBmwschema', 44 ]; 45 46 protected $internalDefaults = [ 47 '_InstallUser' => 'postgres', 48 ]; 49 50 public static $minimumVersion = '9.2'; 51 protected static $notMinimumVersionMessage = 'config-postgres-old'; 52 public $maxRoleSearchDepth = 5; 53 54 protected $pgConns = []; 55 56 public function getName() { 57 return 'postgres'; 58 } 59 60 public function isCompiled() { 61 return self::checkExtension( 'pgsql' ); 62 } 63 64 public function getConnectForm() { 65 return $this->getTextBox( 66 'wgDBserver', 67 'config-db-host', 68 [], 69 $this->parent->getHelpBox( 'config-db-host-help' ) 70 ) . 71 $this->getTextBox( 'wgDBport', 'config-db-port' ) . 72 Html::openElement( 'fieldset' ) . 73 Html::element( 'legend', [], wfMessage( 'config-db-wiki-settings' )->text() ) . 74 $this->getTextBox( 75 'wgDBname', 76 'config-db-name', 77 [], 78 $this->parent->getHelpBox( 'config-db-name-help' ) 79 ) . 80 $this->getTextBox( 81 'wgDBmwschema', 82 'config-db-schema', 83 [], 84 $this->parent->getHelpBox( 'config-db-schema-help' ) 85 ) . 86 Html::closeElement( 'fieldset' ) . 87 $this->getInstallUserBox(); 88 } 89 90 public function submitConnectForm() { 91 // Get variables from the request 92 $newValues = $this->setVarsFromRequest( [ 93 'wgDBserver', 94 'wgDBport', 95 'wgDBname', 96 'wgDBmwschema' 97 ] ); 98 99 // Validate them 100 $status = Status::newGood(); 101 if ( !strlen( $newValues['wgDBname'] ) ) { 102 $status->fatal( 'config-missing-db-name' ); 103 } elseif ( !preg_match( '/^[a-zA-Z0-9_]+$/', $newValues['wgDBname'] ) ) { 104 $status->fatal( 'config-invalid-db-name', $newValues['wgDBname'] ); 105 } 106 if ( !preg_match( '/^[a-zA-Z0-9_]*$/', $newValues['wgDBmwschema'] ) ) { 107 $status->fatal( 'config-invalid-schema', $newValues['wgDBmwschema'] ); 108 } 109 110 // Submit user box 111 if ( $status->isOK() ) { 112 $status->merge( $this->submitInstallUserBox() ); 113 } 114 if ( !$status->isOK() ) { 115 return $status; 116 } 117 118 $status = $this->getPgConnection( 'create-db' ); 119 if ( !$status->isOK() ) { 120 return $status; 121 } 122 /** 123 * @var Database $conn 124 */ 125 $conn = $status->value; 126 127 // Check version 128 $version = $conn->getServerVersion(); 129 $status = static::meetsMinimumRequirement( $version ); 130 if ( !$status->isOK() ) { 131 return $status; 132 } 133 134 $this->setVar( 'wgDBuser', $this->getVar( '_InstallUser' ) ); 135 $this->setVar( 'wgDBpassword', $this->getVar( '_InstallPassword' ) ); 136 137 return Status::newGood(); 138 } 139 140 public function getConnection() { 141 $status = $this->getPgConnection( 'create-tables' ); 142 if ( $status->isOK() ) { 143 $this->db = $status->value; 144 } 145 146 return $status; 147 } 148 149 public function openConnection() { 150 return $this->openPgConnection( 'create-tables' ); 151 } 152 153 /** 154 * Open a PG connection with given parameters 155 * @param string $user User name 156 * @param string $password 157 * @param string $dbName Database name 158 * @param string $schema Database schema 159 * @return Status 160 */ 161 protected function openConnectionWithParams( $user, $password, $dbName, $schema ) { 162 $status = Status::newGood(); 163 try { 164 $db = Database::factory( 'postgres', [ 165 'host' => $this->getVar( 'wgDBserver' ), 166 'port' => $this->getVar( 'wgDBport' ), 167 'user' => $user, 168 'password' => $password, 169 'dbname' => $dbName, 170 'schema' => $schema, 171 'keywordTableMap' => [ 'user' => 'mwuser', 'text' => 'pagecontent' ], 172 ] ); 173 $status->value = $db; 174 } catch ( DBConnectionError $e ) { 175 $status->fatal( 'config-connection-error', $e->getMessage() ); 176 } 177 178 return $status; 179 } 180 181 /** 182 * Get a special type of connection 183 * @param string $type See openPgConnection() for details. 184 * @return Status 185 */ 186 protected function getPgConnection( $type ) { 187 if ( isset( $this->pgConns[$type] ) ) { 188 return Status::newGood( $this->pgConns[$type] ); 189 } 190 $status = $this->openPgConnection( $type ); 191 192 if ( $status->isOK() ) { 193 /** 194 * @var Database $conn 195 */ 196 $conn = $status->value; 197 $conn->clearFlag( DBO_TRX ); 198 $conn->commit( __METHOD__ ); 199 $this->pgConns[$type] = $conn; 200 } 201 202 return $status; 203 } 204 205 /** 206 * Get a connection of a specific PostgreSQL-specific type. Connections 207 * of a given type are cached. 208 * 209 * PostgreSQL lacks cross-database operations, so after the new database is 210 * created, you need to make a separate connection to connect to that 211 * database and add tables to it. 212 * 213 * New tables are owned by the user that creates them, and MediaWiki's 214 * PostgreSQL support has always assumed that the table owner will be 215 * $wgDBuser. So before we create new tables, we either need to either 216 * connect as the other user or to execute a SET ROLE command. Using a 217 * separate connection for this allows us to avoid accidental cross-module 218 * dependencies. 219 * 220 * @param string $type The type of connection to get: 221 * - create-db: A connection for creating DBs, suitable for pre- 222 * installation. 223 * - create-schema: A connection to the new DB, for creating schemas and 224 * other similar objects in the new DB. 225 * - create-tables: A connection with a role suitable for creating tables. 226 * 227 * @throws MWException 228 * @return Status On success, a connection object will be in the value member. 229 */ 230 protected function openPgConnection( $type ) { 231 switch ( $type ) { 232 case 'create-db': 233 return $this->openConnectionToAnyDB( 234 $this->getVar( '_InstallUser' ), 235 $this->getVar( '_InstallPassword' ) ); 236 case 'create-schema': 237 return $this->openConnectionWithParams( 238 $this->getVar( '_InstallUser' ), 239 $this->getVar( '_InstallPassword' ), 240 $this->getVar( 'wgDBname' ), 241 $this->getVar( 'wgDBmwschema' ) ); 242 case 'create-tables': 243 $status = $this->openPgConnection( 'create-schema' ); 244 if ( $status->isOK() ) { 245 /** 246 * @var Database $conn 247 */ 248 $conn = $status->value; 249 $safeRole = $conn->addIdentifierQuotes( $this->getVar( 'wgDBuser' ) ); 250 $conn->query( "SET ROLE $safeRole", __METHOD__ ); 251 } 252 253 return $status; 254 default: 255 throw new MWException( "Invalid special connection type: \"$type\"" ); 256 } 257 } 258 259 public function openConnectionToAnyDB( $user, $password ) { 260 $dbs = [ 261 'template1', 262 'postgres', 263 ]; 264 if ( !in_array( $this->getVar( 'wgDBname' ), $dbs ) ) { 265 array_unshift( $dbs, $this->getVar( 'wgDBname' ) ); 266 } 267 $conn = false; 268 $status = Status::newGood(); 269 foreach ( $dbs as $db ) { 270 try { 271 $p = [ 272 'host' => $this->getVar( 'wgDBserver' ), 273 'port' => $this->getVar( 'wgDBport' ), 274 'user' => $user, 275 'password' => $password, 276 'dbname' => $db 277 ]; 278 $conn = Database::factory( 'postgres', $p ); 279 } catch ( DBConnectionError $error ) { 280 $conn = false; 281 $status->fatal( 'config-pg-test-error', $db, 282 $error->getMessage() ); 283 } 284 if ( $conn !== false ) { 285 break; 286 } 287 } 288 if ( $conn !== false ) { 289 return Status::newGood( $conn ); 290 } else { 291 return $status; 292 } 293 } 294 295 protected function getInstallUserPermissions() { 296 $status = $this->getPgConnection( 'create-db' ); 297 if ( !$status->isOK() ) { 298 return false; 299 } 300 /** 301 * @var Database $conn 302 */ 303 $conn = $status->value; 304 $superuser = $this->getVar( '_InstallUser' ); 305 306 $row = $conn->selectRow( '"pg_catalog"."pg_roles"', '*', 307 [ 'rolname' => $superuser ], __METHOD__ ); 308 309 return $row; 310 } 311 312 protected function canCreateAccounts() { 313 $perms = $this->getInstallUserPermissions(); 314 if ( !$perms ) { 315 return false; 316 } 317 318 return $perms->rolsuper === 't' || $perms->rolcreaterole === 't'; 319 } 320 321 protected function isSuperUser() { 322 $perms = $this->getInstallUserPermissions(); 323 if ( !$perms ) { 324 return false; 325 } 326 327 return $perms->rolsuper === 't'; 328 } 329 330 public function getSettingsForm() { 331 if ( $this->canCreateAccounts() ) { 332 $noCreateMsg = false; 333 } else { 334 $noCreateMsg = 'config-db-web-no-create-privs'; 335 } 336 $s = $this->getWebUserBox( $noCreateMsg ); 337 338 return $s; 339 } 340 341 public function submitSettingsForm() { 342 $status = $this->submitWebUserBox(); 343 if ( !$status->isOK() ) { 344 return $status; 345 } 346 347 $same = $this->getVar( 'wgDBuser' ) === $this->getVar( '_InstallUser' ); 348 349 if ( $same ) { 350 $exists = true; 351 } else { 352 // Check if the web user exists 353 // Connect to the database with the install user 354 $status = $this->getPgConnection( 'create-db' ); 355 if ( !$status->isOK() ) { 356 return $status; 357 } 358 // @phan-suppress-next-line PhanUndeclaredMethod 359 $exists = $status->value->roleExists( $this->getVar( 'wgDBuser' ) ); 360 } 361 362 // Validate the create checkbox 363 if ( $this->canCreateAccounts() && !$same && !$exists ) { 364 $create = $this->getVar( '_CreateDBAccount' ); 365 } else { 366 $this->setVar( '_CreateDBAccount', false ); 367 $create = false; 368 } 369 370 if ( !$create && !$exists ) { 371 if ( $this->canCreateAccounts() ) { 372 $msg = 'config-install-user-missing-create'; 373 } else { 374 $msg = 'config-install-user-missing'; 375 } 376 377 return Status::newFatal( $msg, $this->getVar( 'wgDBuser' ) ); 378 } 379 380 if ( !$exists ) { 381 // No more checks to do 382 return Status::newGood(); 383 } 384 385 // Existing web account. Test the connection. 386 $status = $this->openConnectionToAnyDB( 387 $this->getVar( 'wgDBuser' ), 388 $this->getVar( 'wgDBpassword' ) ); 389 if ( !$status->isOK() ) { 390 return $status; 391 } 392 393 // The web user is conventionally the table owner in PostgreSQL 394 // installations. Make sure the install user is able to create 395 // objects on behalf of the web user. 396 if ( $same || $this->canCreateObjectsForWebUser() ) { 397 return Status::newGood(); 398 } else { 399 return Status::newFatal( 'config-pg-not-in-role' ); 400 } 401 } 402 403 /** 404 * Returns true if the install user is able to create objects owned 405 * by the web user, false otherwise. 406 * @return bool 407 */ 408 protected function canCreateObjectsForWebUser() { 409 if ( $this->isSuperUser() ) { 410 return true; 411 } 412 413 $status = $this->getPgConnection( 'create-db' ); 414 if ( !$status->isOK() ) { 415 return false; 416 } 417 $conn = $status->value; 418 $installerId = $conn->selectField( '"pg_catalog"."pg_roles"', 'oid', 419 [ 'rolname' => $this->getVar( '_InstallUser' ) ], __METHOD__ ); 420 $webId = $conn->selectField( '"pg_catalog"."pg_roles"', 'oid', 421 [ 'rolname' => $this->getVar( 'wgDBuser' ) ], __METHOD__ ); 422 423 return $this->isRoleMember( $conn, $installerId, $webId, $this->maxRoleSearchDepth ); 424 } 425 426 /** 427 * Recursive helper for canCreateObjectsForWebUser(). 428 * @param Database $conn 429 * @param int $targetMember Role ID of the member to look for 430 * @param int $group Role ID of the group to look for 431 * @param int $maxDepth Maximum recursive search depth 432 * @return bool 433 */ 434 protected function isRoleMember( $conn, $targetMember, $group, $maxDepth ) { 435 if ( $targetMember === $group ) { 436 // A role is always a member of itself 437 return true; 438 } 439 // Get all members of the given group 440 $res = $conn->select( '"pg_catalog"."pg_auth_members"', [ 'member' ], 441 [ 'roleid' => $group ], __METHOD__ ); 442 foreach ( $res as $row ) { 443 if ( $row->member == $targetMember ) { 444 // Found target member 445 return true; 446 } 447 // Recursively search each member of the group to see if the target 448 // is a member of it, up to the given maximum depth. 449 if ( $maxDepth > 0 && 450 $this->isRoleMember( $conn, $targetMember, $row->member, $maxDepth - 1 ) 451 ) { 452 // Found member of member 453 return true; 454 } 455 } 456 457 return false; 458 } 459 460 public function preInstall() { 461 $createDbAccount = [ 462 'name' => 'user', 463 'callback' => [ $this, 'setupUser' ], 464 ]; 465 $commitCB = [ 466 'name' => 'pg-commit', 467 'callback' => [ $this, 'commitChanges' ], 468 ]; 469 $plpgCB = [ 470 'name' => 'pg-plpgsql', 471 'callback' => [ $this, 'setupPLpgSQL' ], 472 ]; 473 $schemaCB = [ 474 'name' => 'schema', 475 'callback' => [ $this, 'setupSchema' ] 476 ]; 477 478 if ( $this->getVar( '_CreateDBAccount' ) ) { 479 $this->parent->addInstallStep( $createDbAccount, 'database' ); 480 } 481 $this->parent->addInstallStep( $commitCB, 'interwiki' ); 482 $this->parent->addInstallStep( $plpgCB, 'database' ); 483 $this->parent->addInstallStep( $schemaCB, 'database' ); 484 } 485 486 public function setupDatabase() { 487 $status = $this->getPgConnection( 'create-db' ); 488 if ( !$status->isOK() ) { 489 return $status; 490 } 491 $conn = $status->value; 492 493 $dbName = $this->getVar( 'wgDBname' ); 494 495 $exists = $conn->selectField( '"pg_catalog"."pg_database"', '1', 496 [ 'datname' => $dbName ], __METHOD__ ); 497 if ( !$exists ) { 498 $safedb = $conn->addIdentifierQuotes( $dbName ); 499 $conn->query( "CREATE DATABASE $safedb", __METHOD__ ); 500 } 501 502 return Status::newGood(); 503 } 504 505 public function setupSchema() { 506 // Get a connection to the target database 507 $status = $this->getPgConnection( 'create-schema' ); 508 if ( !$status->isOK() ) { 509 return $status; 510 } 511 /** @var DatabasePostgres $conn */ 512 $conn = $status->value; 513 '@phan-var DatabasePostgres $conn'; 514 515 // Create the schema if necessary 516 $schema = $this->getVar( 'wgDBmwschema' ); 517 $safeschema = $conn->addIdentifierQuotes( $schema ); 518 $safeuser = $conn->addIdentifierQuotes( $this->getVar( 'wgDBuser' ) ); 519 if ( !$conn->schemaExists( $schema ) ) { 520 try { 521 $conn->query( "CREATE SCHEMA $safeschema AUTHORIZATION $safeuser", __METHOD__ ); 522 } catch ( DBQueryError $e ) { 523 return Status::newFatal( 'config-install-pg-schema-failed', 524 $this->getVar( '_InstallUser' ), $schema ); 525 } 526 } 527 528 // Select the new schema in the current connection 529 $conn->determineCoreSchema( $schema ); 530 531 return Status::newGood(); 532 } 533 534 public function commitChanges() { 535 $this->db->commit( __METHOD__ ); 536 537 return Status::newGood(); 538 } 539 540 public function setupUser() { 541 if ( !$this->getVar( '_CreateDBAccount' ) ) { 542 return Status::newGood(); 543 } 544 545 $status = $this->getPgConnection( 'create-db' ); 546 if ( !$status->isOK() ) { 547 return $status; 548 } 549 /** @var DatabasePostgres $conn */ 550 $conn = $status->value; 551 '@phan-var DatabasePostgres $conn'; 552 553 $safeuser = $conn->addIdentifierQuotes( $this->getVar( 'wgDBuser' ) ); 554 $safepass = $conn->addQuotes( $this->getVar( 'wgDBpassword' ) ); 555 556 // Check if the user already exists 557 $userExists = $conn->roleExists( $this->getVar( 'wgDBuser' ) ); 558 if ( !$userExists ) { 559 // Create the user 560 try { 561 $sql = "CREATE ROLE $safeuser NOCREATEDB LOGIN PASSWORD $safepass"; 562 563 // If the install user is not a superuser, we need to make the install 564 // user a member of the new user's group, so that the install user will 565 // be able to create a schema and other objects on behalf of the new user. 566 if ( !$this->isSuperUser() ) { 567 $sql .= ' ROLE' . $conn->addIdentifierQuotes( $this->getVar( '_InstallUser' ) ); 568 } 569 570 $conn->query( $sql, __METHOD__ ); 571 } catch ( DBQueryError $e ) { 572 return Status::newFatal( 'config-install-user-create-failed', 573 $this->getVar( 'wgDBuser' ), $e->getMessage() ); 574 } 575 } 576 577 return Status::newGood(); 578 } 579 580 public function getLocalSettings() { 581 $port = $this->getVar( 'wgDBport' ); 582 $schema = $this->getVar( 'wgDBmwschema' ); 583 584 return "# Postgres specific settings 585\$wgDBport = \"{$port}\"; 586\$wgDBmwschema = \"{$schema}\";"; 587 } 588 589 public function preUpgrade() { 590 global $wgDBuser, $wgDBpassword; 591 592 # Normal user and password are selected after this step, so for now 593 # just copy these two 594 $wgDBuser = $this->getVar( '_InstallUser' ); 595 $wgDBpassword = $this->getVar( '_InstallPassword' ); 596 } 597 598 public function createTables() { 599 $schema = $this->getVar( 'wgDBmwschema' ); 600 601 $status = $this->getConnection(); 602 if ( !$status->isOK() ) { 603 return $status; 604 } 605 606 /** @var DatabasePostgres $conn */ 607 $conn = $status->value; 608 '@phan-var DatabasePostgres $conn'; 609 610 if ( $conn->tableExists( 'archive', __METHOD__ ) ) { 611 $status->warning( 'config-install-tables-exist' ); 612 $this->enableLB(); 613 614 return $status; 615 } 616 617 $conn->begin( __METHOD__ ); 618 619 if ( !$conn->schemaExists( $schema ) ) { 620 $status->fatal( 'config-install-pg-schema-not-exist' ); 621 622 return $status; 623 } 624 $error = $conn->sourceFile( $this->getSchemaPath( $conn ) ); 625 if ( $error !== true ) { 626 $conn->reportQueryError( $error, 0, '', __METHOD__ ); 627 $conn->rollback( __METHOD__ ); 628 $status->fatal( 'config-install-tables-manual-failed', $error ); 629 } else { 630 $error = $conn->sourceFile( $this->getGeneratedSchemaPath( $conn ) ); 631 if ( $error !== true ) { 632 $conn->reportQueryError( $error, 0, '', __METHOD__ ); 633 $conn->rollback( __METHOD__ ); 634 $status->fatal( 'config-install-tables-failed', $error ); 635 } else { 636 $conn->commit( __METHOD__ ); 637 } 638 } 639 // Resume normal operations 640 if ( $status->isOK() ) { 641 $this->enableLB(); 642 } 643 644 return $status; 645 } 646 647 public function getGlobalDefaults() { 648 // The default $wgDBmwschema is null, which breaks Postgres and other DBMSes that require 649 // the use of a schema, so we need to set it here 650 return array_merge( parent::getGlobalDefaults(), [ 651 'wgDBmwschema' => 'mediawiki', 652 ] ); 653 } 654 655 public function setupPLpgSQL() { 656 // Connect as the install user, since it owns the database and so is 657 // the user that needs to run "CREATE LANGUAGE" 658 $status = $this->getPgConnection( 'create-schema' ); 659 if ( !$status->isOK() ) { 660 return $status; 661 } 662 /** 663 * @var Database $conn 664 */ 665 $conn = $status->value; 666 667 $exists = $conn->selectField( '"pg_catalog"."pg_language"', '1', 668 [ 'lanname' => 'plpgsql' ], __METHOD__ ); 669 if ( $exists ) { 670 // Already exists, nothing to do 671 return Status::newGood(); 672 } 673 674 // plpgsql is not installed, but if we have a pg_pltemplate table, we 675 // should be able to create it 676 $exists = $conn->selectField( 677 [ '"pg_catalog"."pg_class"', '"pg_catalog"."pg_namespace"' ], 678 '1', 679 [ 680 'pg_namespace.oid=relnamespace', 681 'nspname' => 'pg_catalog', 682 'relname' => 'pg_pltemplate', 683 ], 684 __METHOD__ ); 685 if ( $exists ) { 686 try { 687 $conn->query( 'CREATE LANGUAGE plpgsql' ); 688 } catch ( DBQueryError $e ) { 689 return Status::newFatal( 'config-pg-no-plpgsql', $this->getVar( 'wgDBname' ) ); 690 } 691 } else { 692 return Status::newFatal( 'config-pg-no-plpgsql', $this->getVar( 'wgDBname' ) ); 693 } 694 695 return Status::newGood(); 696 } 697} 698