PostgreSQL.php 6.6 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187
  1. <?php
  2. /**
  3. * @copyright Copyright (c) 2016, ownCloud, Inc.
  4. *
  5. * @author Bart Visscher <bartv@thisnet.nl>
  6. * @author Christoph Wurst <christoph@winzerhof-wurst.at>
  7. * @author eduardo <eduardo@vnexu.net>
  8. * @author Joas Schilling <coding@schilljs.com>
  9. * @author Lukas Reschke <lukas@statuscode.ch>
  10. * @author Morris Jobke <hey@morrisjobke.de>
  11. * @author Robin Appelman <robin@icewind.nl>
  12. * @author Vitor Mattos <vitor@php.rio>
  13. *
  14. * @license AGPL-3.0
  15. *
  16. * This code is free software: you can redistribute it and/or modify
  17. * it under the terms of the GNU Affero General Public License, version 3,
  18. * as published by the Free Software Foundation.
  19. *
  20. * This program is distributed in the hope that it will be useful,
  21. * but WITHOUT ANY WARRANTY; without even the implied warranty of
  22. * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
  23. * GNU Affero General Public License for more details.
  24. *
  25. * You should have received a copy of the GNU Affero General Public License, version 3,
  26. * along with this program. If not, see <http://www.gnu.org/licenses/>
  27. *
  28. */
  29. namespace OC\Setup;
  30. use OC\DatabaseException;
  31. use OC\DB\Connection;
  32. use OC\DB\QueryBuilder\Literal;
  33. use OCP\Security\ISecureRandom;
  34. class PostgreSQL extends AbstractDatabase {
  35. public $dbprettyname = 'PostgreSQL';
  36. /**
  37. * @param string $username
  38. * @throws \OC\DatabaseSetupException
  39. */
  40. public function setupDatabase($username) {
  41. try {
  42. $connection = $this->connect([
  43. 'dbname' => 'postgres'
  44. ]);
  45. if ($this->tryCreateDbUser) {
  46. //check for roles creation rights in postgresql
  47. $builder = $connection->getQueryBuilder();
  48. $builder->automaticTablePrefix(false);
  49. $query = $builder
  50. ->select('rolname')
  51. ->from('pg_roles')
  52. ->where($builder->expr()->eq('rolcreaterole', new Literal('TRUE')))
  53. ->andWhere($builder->expr()->eq('rolname', $builder->createNamedParameter($this->dbUser)));
  54. try {
  55. $result = $query->execute();
  56. $canCreateRoles = $result->rowCount() > 0;
  57. } catch (DatabaseException $e) {
  58. $canCreateRoles = false;
  59. }
  60. if ($canCreateRoles) {
  61. $connectionMainDatabase = $this->connect();
  62. //use the admin login data for the new database user
  63. //add prefix to the postgresql user name to prevent collisions
  64. $this->dbUser = 'oc_' . strtolower($username);
  65. //create a new password so we don't need to store the admin config in the config file
  66. $this->dbPassword = \OC::$server->getSecureRandom()->generate(30, ISecureRandom::CHAR_ALPHANUMERIC);
  67. $this->createDBUser($connection);
  68. // Go to the main database and grant create on the public schema
  69. // The code below is implemented to make installing possible with PostgreSQL version 15:
  70. // https://www.postgresql.org/docs/release/15.0/
  71. // From the release notes: For new databases having no need to defend against insider threats, granting CREATE permission will yield the behavior of prior releases
  72. // Therefore we assume that the database is only used by one user/service which is Nextcloud
  73. // Additional services should get installed in a separate database in order to stay secure
  74. // Also see https://www.postgresql.org/docs/15/ddl-schemas.html#DDL-SCHEMAS-PATTERNS
  75. $connectionMainDatabase->executeQuery('GRANT CREATE ON SCHEMA public TO "' . addslashes($this->dbUser) . '"');
  76. $connectionMainDatabase->close();
  77. }
  78. }
  79. $this->config->setValues([
  80. 'dbuser' => $this->dbUser,
  81. 'dbpassword' => $this->dbPassword,
  82. ]);
  83. //create the database
  84. $this->createDatabase($connection);
  85. // the connection to dbname=postgres is not needed anymore
  86. $connection->close();
  87. } catch (\Exception $e) {
  88. $this->logger->warning('Error trying to connect as "postgres", assuming database is setup and tables need to be created', [
  89. 'exception' => $e,
  90. ]);
  91. $this->config->setValues([
  92. 'dbuser' => $this->dbUser,
  93. 'dbpassword' => $this->dbPassword,
  94. ]);
  95. }
  96. // connect to the database (dbname=$this->dbname) and check if it needs to be filled
  97. $this->dbUser = $this->config->getValue('dbuser');
  98. $this->dbPassword = $this->config->getValue('dbpassword');
  99. $connection = $this->connect();
  100. try {
  101. $connection->connect();
  102. } catch (\Exception $e) {
  103. $this->logger->error($e->getMessage(), [
  104. 'exception' => $e,
  105. ]);
  106. throw new \OC\DatabaseSetupException($this->trans->t('PostgreSQL Login and/or password not valid'),
  107. $this->trans->t('You need to enter details of an existing account.'), 0, $e);
  108. }
  109. }
  110. private function createDatabase(Connection $connection) {
  111. if (!$this->databaseExists($connection)) {
  112. //The database does not exists... let's create it
  113. $query = $connection->prepare("CREATE DATABASE " . addslashes($this->dbName) . " OWNER \"" . addslashes($this->dbUser) . '"');
  114. try {
  115. $query->execute();
  116. } catch (DatabaseException $e) {
  117. $this->logger->error('Error while trying to create database', [
  118. 'exception' => $e,
  119. ]);
  120. }
  121. } else {
  122. $query = $connection->prepare("REVOKE ALL PRIVILEGES ON DATABASE " . addslashes($this->dbName) . " FROM PUBLIC");
  123. try {
  124. $query->execute();
  125. } catch (DatabaseException $e) {
  126. $this->logger->error('Error while trying to restrict database permissions', [
  127. 'exception' => $e,
  128. ]);
  129. }
  130. }
  131. }
  132. private function userExists(Connection $connection) {
  133. $builder = $connection->getQueryBuilder();
  134. $builder->automaticTablePrefix(false);
  135. $query = $builder->select('*')
  136. ->from('pg_roles')
  137. ->where($builder->expr()->eq('rolname', $builder->createNamedParameter($this->dbUser)));
  138. $result = $query->execute();
  139. return $result->rowCount() > 0;
  140. }
  141. private function databaseExists(Connection $connection) {
  142. $builder = $connection->getQueryBuilder();
  143. $builder->automaticTablePrefix(false);
  144. $query = $builder->select('datname')
  145. ->from('pg_database')
  146. ->where($builder->expr()->eq('datname', $builder->createNamedParameter($this->dbName)));
  147. $result = $query->execute();
  148. return $result->rowCount() > 0;
  149. }
  150. private function createDBUser(Connection $connection) {
  151. $dbUser = $this->dbUser;
  152. try {
  153. $i = 1;
  154. while ($this->userExists($connection)) {
  155. $i++;
  156. $this->dbUser = $dbUser . $i;
  157. }
  158. // create the user
  159. $query = $connection->prepare("CREATE USER \"" . addslashes($this->dbUser) . "\" CREATEDB PASSWORD '" . addslashes($this->dbPassword) . "'");
  160. $query->execute();
  161. if ($this->databaseExists($connection)) {
  162. $query = $connection->prepare('GRANT CONNECT ON DATABASE ' . addslashes($this->dbName) . ' TO "' . addslashes($this->dbUser) . '"');
  163. $query->execute();
  164. }
  165. } catch (DatabaseException $e) {
  166. $this->logger->error('Error while trying to create database user', [
  167. 'exception' => $e,
  168. ]);
  169. }
  170. }
  171. }