PostgresInstaller.php

Go to the documentation of this file.

<?php
use Wikimedia\Rdbms\Database;
use Wikimedia\Rdbms\DatabasePostgres;
use Wikimedia\Rdbms\DBQueryError;
use Wikimedia\Rdbms\DBConnectionError;
 
class PostgresInstaller extends DatabaseInstaller {
 
    protected $globalNames = [
        'wgDBserver',
        'wgDBport',
        'wgDBname',
        'wgDBuser',
        'wgDBpassword',
        'wgDBmwschema',
    ];
 
    protected $internalDefaults = [
        '_InstallUser' => 'postgres',
    ];
 
    public static $minimumVersion = '9.2';
    protected static $notMinimumVersionMessage = 'config-postgres-old';
    public $maxRoleSearchDepth = 5;
 
    protected $pgConns = [];
 
    function getName() {
        return 'postgres';
    }
 
    public function isCompiled() {
        return self::checkExtension( 'pgsql' );
    }
 
    function getConnectForm() {
        return $this->getTextBox(
            'wgDBserver',
            'config-db-host',
            [],
            $this->parent->getHelpBox( 'config-db-host-help' )
        ) .
            $this->getTextBox( 'wgDBport', 'config-db-port' ) .
            Html::openElement( 'fieldset' ) .
            Html::element( 'legend', [], wfMessage( 'config-db-wiki-settings' )->text() ) .
            $this->getTextBox(
                'wgDBname',
                'config-db-name',
                [],
                $this->parent->getHelpBox( 'config-db-name-help' )
            ) .
            $this->getTextBox(
                'wgDBmwschema',
                'config-db-schema',
                [],
                $this->parent->getHelpBox( 'config-db-schema-help' )
            ) .
            Html::closeElement( 'fieldset' ) .
            $this->getInstallUserBox();
    }
 
    function submitConnectForm() {
        // Get variables from the request
        $newValues = $this->setVarsFromRequest( [
            'wgDBserver',
            'wgDBport',
            'wgDBname',
            'wgDBmwschema'
        ] );
 
        // Validate them
        $status = Status::newGood();
        if ( !strlen( $newValues['wgDBname'] ) ) {
            $status->fatal( 'config-missing-db-name' );
        } elseif ( !preg_match( '/^[a-zA-Z0-9_]+$/', $newValues['wgDBname'] ) ) {
            $status->fatal( 'config-invalid-db-name', $newValues['wgDBname'] );
        }
        if ( !preg_match( '/^[a-zA-Z0-9_]*$/', $newValues['wgDBmwschema'] ) ) {
            $status->fatal( 'config-invalid-schema', $newValues['wgDBmwschema'] );
        }
 
        // Submit user box
        if ( $status->isOK() ) {
            $status->merge( $this->submitInstallUserBox() );
        }
        if ( !$status->isOK() ) {
            return $status;
        }
 
        $status = $this->getPgConnection( 'create-db' );
        if ( !$status->isOK() ) {
            return $status;
        }
        $conn = $status->value;
 
        // Check version
        $version = $conn->getServerVersion();
        $status = static::meetsMinimumRequirement( $version );
        if ( !$status->isOK() ) {
            return $status;
        }
 
        $this->setVar( 'wgDBuser', $this->getVar( '_InstallUser' ) );
        $this->setVar( 'wgDBpassword', $this->getVar( '_InstallPassword' ) );
 
        return Status::newGood();
    }
 
    public function getConnection() {
        $status = $this->getPgConnection( 'create-tables' );
        if ( $status->isOK() ) {
            $this->db = $status->value;
        }
 
        return $status;
    }
 
    public function openConnection() {
        return $this->openPgConnection( 'create-tables' );
    }
 
    protected function openConnectionWithParams( $user, $password, $dbName, $schema ) {
        $status = Status::newGood();
        try {
            $db = Database::factory( 'postgres', [
                'host' => $this->getVar( 'wgDBserver' ),
                'port' => $this->getVar( 'wgDBport' ),
                'user' => $user,
                'password' => $password,
                'dbname' => $dbName,
                'schema' => $schema,
                'keywordTableMap' => [ 'user' => 'mwuser', 'text' => 'pagecontent' ],
            ] );
            $status->value = $db;
        } catch ( DBConnectionError $e ) {
            $status->fatal( 'config-connection-error', $e->getMessage() );
        }
 
        return $status;
    }
 
    protected function getPgConnection( $type ) {
        if ( isset( $this->pgConns[$type] ) ) {
            return Status::newGood( $this->pgConns[$type] );
        }
        $status = $this->openPgConnection( $type );
 
        if ( $status->isOK() ) {
            $conn = $status->value;
            $conn->clearFlag( DBO_TRX );
            $conn->commit( __METHOD__ );
            $this->pgConns[$type] = $conn;
        }
 
        return $status;
    }
 
    protected function openPgConnection( $type ) {
        switch ( $type ) {
            case 'create-db':
                return $this->openConnectionToAnyDB(
                    $this->getVar( '_InstallUser' ),
                    $this->getVar( '_InstallPassword' ) );
            case 'create-schema':
                return $this->openConnectionWithParams(
                    $this->getVar( '_InstallUser' ),
                    $this->getVar( '_InstallPassword' ),
                    $this->getVar( 'wgDBname' ),
                    $this->getVar( 'wgDBmwschema' ) );
            case 'create-tables':
                $status = $this->openPgConnection( 'create-schema' );
                if ( $status->isOK() ) {
                    $conn = $status->value;
                    $safeRole = $conn->addIdentifierQuotes( $this->getVar( 'wgDBuser' ) );
                    $conn->query( "SET ROLE $safeRole" );
                }
 
                return $status;
            default:
                throw new MWException( "Invalid special connection type: \"$type\"" );
        }
    }
 
    public function openConnectionToAnyDB( $user, $password ) {
        $dbs = [
            'template1',
            'postgres',
        ];
        if ( !in_array( $this->getVar( 'wgDBname' ), $dbs ) ) {
            array_unshift( $dbs, $this->getVar( 'wgDBname' ) );
        }
        $conn = false;
        $status = Status::newGood();
        foreach ( $dbs as $db ) {
            try {
                $p = [
                    'host' => $this->getVar( 'wgDBserver' ),
                    'user' => $user,
                    'password' => $password,
                    'dbname' => $db
                ];
                $conn = Database::factory( 'postgres', $p );
            } catch ( DBConnectionError $error ) {
                $conn = false;
                $status->fatal( 'config-pg-test-error', $db,
                    $error->getMessage() );
            }
            if ( $conn !== false ) {
                break;
            }
        }
        if ( $conn !== false ) {
            return Status::newGood( $conn );
        } else {
            return $status;
        }
    }
 
    protected function getInstallUserPermissions() {
        $status = $this->getPgConnection( 'create-db' );
        if ( !$status->isOK() ) {
            return false;
        }
        $conn = $status->value;
        $superuser = $this->getVar( '_InstallUser' );
 
        $row = $conn->selectRow( '"pg_catalog"."pg_roles"', '*',
            [ 'rolname' => $superuser ], __METHOD__ );
 
        return $row;
    }
 
    protected function canCreateAccounts() {
        $perms = $this->getInstallUserPermissions();
        if ( !$perms ) {
            return false;
        }
 
        return $perms->rolsuper === 't' || $perms->rolcreaterole === 't';
    }
 
    protected function isSuperUser() {
        $perms = $this->getInstallUserPermissions();
        if ( !$perms ) {
            return false;
        }
 
        return $perms->rolsuper === 't';
    }
 
    public function getSettingsForm() {
        if ( $this->canCreateAccounts() ) {
            $noCreateMsg = false;
        } else {
            $noCreateMsg = 'config-db-web-no-create-privs';
        }
        $s = $this->getWebUserBox( $noCreateMsg );
 
        return $s;
    }
 
    public function submitSettingsForm() {
        $status = $this->submitWebUserBox();
        if ( !$status->isOK() ) {
            return $status;
        }
 
        $same = $this->getVar( 'wgDBuser' ) === $this->getVar( '_InstallUser' );
 
        if ( $same ) {
            $exists = true;
        } else {
            // Check if the web user exists
            // Connect to the database with the install user
            $status = $this->getPgConnection( 'create-db' );
            if ( !$status->isOK() ) {
                return $status;
            }
            // @phan-suppress-next-line PhanUndeclaredMethod
            $exists = $status->value->roleExists( $this->getVar( 'wgDBuser' ) );
        }
 
        // Validate the create checkbox
        if ( $this->canCreateAccounts() && !$same && !$exists ) {
            $create = $this->getVar( '_CreateDBAccount' );
        } else {
            $this->setVar( '_CreateDBAccount', false );
            $create = false;
        }
 
        if ( !$create && !$exists ) {
            if ( $this->canCreateAccounts() ) {
                $msg = 'config-install-user-missing-create';
            } else {
                $msg = 'config-install-user-missing';
            }
 
            return Status::newFatal( $msg, $this->getVar( 'wgDBuser' ) );
        }
 
        if ( !$exists ) {
            // No more checks to do
            return Status::newGood();
        }
 
        // Existing web account. Test the connection.
        $status = $this->openConnectionToAnyDB(
            $this->getVar( 'wgDBuser' ),
            $this->getVar( 'wgDBpassword' ) );
        if ( !$status->isOK() ) {
            return $status;
        }
 
        // The web user is conventionally the table owner in PostgreSQL
        // installations. Make sure the install user is able to create
        // objects on behalf of the web user.
        if ( $same || $this->canCreateObjectsForWebUser() ) {
            return Status::newGood();
        } else {
            return Status::newFatal( 'config-pg-not-in-role' );
        }
    }
 
    protected function canCreateObjectsForWebUser() {
        if ( $this->isSuperUser() ) {
            return true;
        }
 
        $status = $this->getPgConnection( 'create-db' );
        if ( !$status->isOK() ) {
            return false;
        }
        $conn = $status->value;
        $installerId = $conn->selectField( '"pg_catalog"."pg_roles"', 'oid',
            [ 'rolname' => $this->getVar( '_InstallUser' ) ], __METHOD__ );
        $webId = $conn->selectField( '"pg_catalog"."pg_roles"', 'oid',
            [ 'rolname' => $this->getVar( 'wgDBuser' ) ], __METHOD__ );
 
        return $this->isRoleMember( $conn, $installerId, $webId, $this->maxRoleSearchDepth );
    }
 
    protected function isRoleMember( $conn, $targetMember, $group, $maxDepth ) {
        if ( $targetMember === $group ) {
            // A role is always a member of itself
            return true;
        }
        // Get all members of the given group
        $res = $conn->select( '"pg_catalog"."pg_auth_members"', [ 'member' ],
            [ 'roleid' => $group ], __METHOD__ );
        foreach ( $res as $row ) {
            if ( $row->member == $targetMember ) {
                // Found target member
                return true;
            }
            // Recursively search each member of the group to see if the target
            // is a member of it, up to the given maximum depth.
            if ( $maxDepth > 0 &&
                $this->isRoleMember( $conn, $targetMember, $row->member, $maxDepth - 1 )
            ) {
                // Found member of member
                return true;
            }
        }
 
        return false;
    }
 
    public function preInstall() {
        $createDbAccount = [
            'name' => 'user',
            'callback' => [ $this, 'setupUser' ],
        ];
        $commitCB = [
            'name' => 'pg-commit',
            'callback' => [ $this, 'commitChanges' ],
        ];
        $plpgCB = [
            'name' => 'pg-plpgsql',
            'callback' => [ $this, 'setupPLpgSQL' ],
        ];
        $schemaCB = [
            'name' => 'schema',
            'callback' => [ $this, 'setupSchema' ]
        ];
 
        if ( $this->getVar( '_CreateDBAccount' ) ) {
            $this->parent->addInstallStep( $createDbAccount, 'database' );
        }
        $this->parent->addInstallStep( $commitCB, 'interwiki' );
        $this->parent->addInstallStep( $plpgCB, 'database' );
        $this->parent->addInstallStep( $schemaCB, 'database' );
    }
 
    function setupDatabase() {
        $status = $this->getPgConnection( 'create-db' );
        if ( !$status->isOK() ) {
            return $status;
        }
        $conn = $status->value;
 
        $dbName = $this->getVar( 'wgDBname' );
 
        $exists = $conn->selectField( '"pg_catalog"."pg_database"', '1',
            [ 'datname' => $dbName ], __METHOD__ );
        if ( !$exists ) {
            $safedb = $conn->addIdentifierQuotes( $dbName );
            $conn->query( "CREATE DATABASE $safedb", __METHOD__ );
        }
 
        return Status::newGood();
    }
 
    function setupSchema() {
        // Get a connection to the target database
        $status = $this->getPgConnection( 'create-schema' );
        if ( !$status->isOK() ) {
            return $status;
        }
        $conn = $status->value;
        '@phan-var DatabasePostgres $conn';
 
        // Create the schema if necessary
        $schema = $this->getVar( 'wgDBmwschema' );
        $safeschema = $conn->addIdentifierQuotes( $schema );
        $safeuser = $conn->addIdentifierQuotes( $this->getVar( 'wgDBuser' ) );
        if ( !$conn->schemaExists( $schema ) ) {
            try {
                $conn->query( "CREATE SCHEMA $safeschema AUTHORIZATION $safeuser" );
            } catch ( DBQueryError $e ) {
                return Status::newFatal( 'config-install-pg-schema-failed',
                    $this->getVar( '_InstallUser' ), $schema );
            }
        }
 
        // Select the new schema in the current connection
        $conn->determineCoreSchema( $schema );
 
        return Status::newGood();
    }
 
    function commitChanges() {
        $this->db->commit( __METHOD__ );
 
        return Status::newGood();
    }
 
    function setupUser() {
        if ( !$this->getVar( '_CreateDBAccount' ) ) {
            return Status::newGood();
        }
 
        $status = $this->getPgConnection( 'create-db' );
        if ( !$status->isOK() ) {
            return $status;
        }
        $conn = $status->value;
        '@phan-var DatabasePostgres $conn';
 
        $safeuser = $conn->addIdentifierQuotes( $this->getVar( 'wgDBuser' ) );
        $safepass = $conn->addQuotes( $this->getVar( 'wgDBpassword' ) );
 
        // Check if the user already exists
        $userExists = $conn->roleExists( $this->getVar( 'wgDBuser' ) );
        if ( !$userExists ) {
            // Create the user
            try {
                $sql = "CREATE ROLE $safeuser NOCREATEDB LOGIN PASSWORD $safepass";
 
                // If the install user is not a superuser, we need to make the install
                // user a member of the new user's group, so that the install user will
                // be able to create a schema and other objects on behalf of the new user.
                if ( !$this->isSuperUser() ) {
                    $sql .= ' ROLE' . $conn->addIdentifierQuotes( $this->getVar( '_InstallUser' ) );
                }
 
                $conn->query( $sql, __METHOD__ );
            } catch ( DBQueryError $e ) {
                return Status::newFatal( 'config-install-user-create-failed',
                    $this->getVar( 'wgDBuser' ), $e->getMessage() );
            }
        }
 
        return Status::newGood();
    }
 
    function getLocalSettings() {
        $port = $this->getVar( 'wgDBport' );
        $schema = $this->getVar( 'wgDBmwschema' );
 
        return "# Postgres specific settings
\$wgDBport = \"{$port}\";
\$wgDBmwschema = \"{$schema}\";";
    }
 
    public function preUpgrade() {
        global $wgDBuser, $wgDBpassword;
 
        # Normal user and password are selected after this step, so for now
        # just copy these two
        $wgDBuser = $this->getVar( '_InstallUser' );
        $wgDBpassword = $this->getVar( '_InstallPassword' );
    }
 
    public function createTables() {
        $schema = $this->getVar( 'wgDBmwschema' );
 
        $status = $this->getConnection();
        if ( !$status->isOK() ) {
            return $status;
        }
 
        $conn = $status->value;
        '@phan-var DatabasePostgres $conn';
 
        if ( $conn->tableExists( 'archive' ) ) {
            $status->warning( 'config-install-tables-exist' );
            $this->enableLB();
 
            return $status;
        }
 
        $conn->begin( __METHOD__ );
 
        if ( !$conn->schemaExists( $schema ) ) {
            $status->fatal( 'config-install-pg-schema-not-exist' );
 
            return $status;
        }
        $error = $conn->sourceFile( $this->getSchemaPath( $conn ) );
        if ( $error !== true ) {
            $conn->reportQueryError( $error, 0, '', __METHOD__ );
            $conn->rollback( __METHOD__ );
            $status->fatal( 'config-install-tables-failed', $error );
        } else {
            $conn->commit( __METHOD__ );
        }
        // Resume normal operations
        if ( $status->isOK() ) {
            $this->enableLB();
        }
 
        return $status;
    }
 
    public function getGlobalDefaults() {
        // The default $wgDBmwschema is null, which breaks Postgres and other DBMSes that require
        // the use of a schema, so we need to set it here
        return array_merge( parent::getGlobalDefaults(), [
            'wgDBmwschema' => 'mediawiki',
        ] );
    }
 
    public function setupPLpgSQL() {
        // Connect as the install user, since it owns the database and so is
        // the user that needs to run "CREATE LANGUAGE"
        $status = $this->getPgConnection( 'create-schema' );
        if ( !$status->isOK() ) {
            return $status;
        }
        $conn = $status->value;
 
        $exists = $conn->selectField( '"pg_catalog"."pg_language"', 1,
            [ 'lanname' => 'plpgsql' ], __METHOD__ );
        if ( $exists ) {
            // Already exists, nothing to do
            return Status::newGood();
        }
 
        // plpgsql is not installed, but if we have a pg_pltemplate table, we
        // should be able to create it
        $exists = $conn->selectField(
            [ '"pg_catalog"."pg_class"', '"pg_catalog"."pg_namespace"' ],
            1,
            [
                'pg_namespace.oid=relnamespace',
                'nspname' => 'pg_catalog',
                'relname' => 'pg_pltemplate',
            ],
            __METHOD__ );
        if ( $exists ) {
            try {
                $conn->query( 'CREATE LANGUAGE plpgsql' );
            } catch ( DBQueryError $e ) {
                return Status::newFatal( 'config-pg-no-plpgsql', $this->getVar( 'wgDBname' ) );
            }
        } else {
            return Status::newFatal( 'config-pg-no-plpgsql', $this->getVar( 'wgDBname' ) );
        }
 
        return Status::newGood();
    }
}