1
0

PostgreSQL.php 5.8 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170
  1. <?php
  2. /**
  3. * SPDX-FileCopyrightText: 2016-2024 Nextcloud GmbH and Nextcloud contributors
  4. * SPDX-FileCopyrightText: 2016 ownCloud, Inc.
  5. * SPDX-License-Identifier: AGPL-3.0-only
  6. */
  7. namespace OC\Setup;
  8. use OC\DatabaseException;
  9. use OC\DB\Connection;
  10. use OC\DB\QueryBuilder\Literal;
  11. use OCP\Security\ISecureRandom;
  12. class PostgreSQL extends AbstractDatabase {
  13. public $dbprettyname = 'PostgreSQL';
  14. /**
  15. * @param string $username
  16. * @throws \OC\DatabaseSetupException
  17. */
  18. public function setupDatabase($username) {
  19. try {
  20. $connection = $this->connect([
  21. 'dbname' => 'postgres'
  22. ]);
  23. if ($this->tryCreateDbUser) {
  24. //check for roles creation rights in postgresql
  25. $builder = $connection->getQueryBuilder();
  26. $builder->automaticTablePrefix(false);
  27. $query = $builder
  28. ->select('rolname')
  29. ->from('pg_roles')
  30. ->where($builder->expr()->eq('rolcreaterole', new Literal('TRUE')))
  31. ->andWhere($builder->expr()->eq('rolname', $builder->createNamedParameter($this->dbUser)));
  32. try {
  33. $result = $query->execute();
  34. $canCreateRoles = $result->rowCount() > 0;
  35. } catch (DatabaseException $e) {
  36. $canCreateRoles = false;
  37. }
  38. if ($canCreateRoles) {
  39. $connectionMainDatabase = $this->connect();
  40. //use the admin login data for the new database user
  41. //add prefix to the postgresql user name to prevent collisions
  42. $this->dbUser = 'oc_' . strtolower($username);
  43. //create a new password so we don't need to store the admin config in the config file
  44. $this->dbPassword = \OC::$server->get(ISecureRandom::class)->generate(30, ISecureRandom::CHAR_ALPHANUMERIC);
  45. $this->createDBUser($connection);
  46. }
  47. }
  48. $this->config->setValues([
  49. 'dbuser' => $this->dbUser,
  50. 'dbpassword' => $this->dbPassword,
  51. ]);
  52. //create the database
  53. $this->createDatabase($connection);
  54. // the connection to dbname=postgres is not needed anymore
  55. $connection->close();
  56. if ($this->tryCreateDbUser) {
  57. if ($canCreateRoles) {
  58. // Go to the main database and grant create on the public schema
  59. // The code below is implemented to make installing possible with PostgreSQL version 15:
  60. // https://www.postgresql.org/docs/release/15.0/
  61. // 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
  62. // Therefore we assume that the database is only used by one user/service which is Nextcloud
  63. // Additional services should get installed in a separate database in order to stay secure
  64. // Also see https://www.postgresql.org/docs/15/ddl-schemas.html#DDL-SCHEMAS-PATTERNS
  65. $connectionMainDatabase->executeQuery('GRANT CREATE ON SCHEMA public TO "' . addslashes($this->dbUser) . '"');
  66. $connectionMainDatabase->close();
  67. }
  68. }
  69. } catch (\Exception $e) {
  70. $this->logger->warning('Error trying to connect as "postgres", assuming database is setup and tables need to be created', [
  71. 'exception' => $e,
  72. ]);
  73. $this->config->setValues([
  74. 'dbuser' => $this->dbUser,
  75. 'dbpassword' => $this->dbPassword,
  76. ]);
  77. }
  78. // connect to the database (dbname=$this->dbname) and check if it needs to be filled
  79. $this->dbUser = $this->config->getValue('dbuser');
  80. $this->dbPassword = $this->config->getValue('dbpassword');
  81. $connection = $this->connect();
  82. try {
  83. $connection->connect();
  84. } catch (\Exception $e) {
  85. $this->logger->error($e->getMessage(), [
  86. 'exception' => $e,
  87. ]);
  88. throw new \OC\DatabaseSetupException($this->trans->t('PostgreSQL Login and/or password not valid'),
  89. $this->trans->t('You need to enter details of an existing account.'), 0, $e);
  90. }
  91. }
  92. private function createDatabase(Connection $connection) {
  93. if (!$this->databaseExists($connection)) {
  94. //The database does not exists... let's create it
  95. $query = $connection->prepare('CREATE DATABASE ' . addslashes($this->dbName) . ' OWNER "' . addslashes($this->dbUser) . '"');
  96. try {
  97. $query->execute();
  98. } catch (DatabaseException $e) {
  99. $this->logger->error('Error while trying to create database', [
  100. 'exception' => $e,
  101. ]);
  102. }
  103. } else {
  104. $query = $connection->prepare('REVOKE ALL PRIVILEGES ON DATABASE ' . addslashes($this->dbName) . ' FROM PUBLIC');
  105. try {
  106. $query->execute();
  107. } catch (DatabaseException $e) {
  108. $this->logger->error('Error while trying to restrict database permissions', [
  109. 'exception' => $e,
  110. ]);
  111. }
  112. }
  113. }
  114. private function userExists(Connection $connection) {
  115. $builder = $connection->getQueryBuilder();
  116. $builder->automaticTablePrefix(false);
  117. $query = $builder->select('*')
  118. ->from('pg_roles')
  119. ->where($builder->expr()->eq('rolname', $builder->createNamedParameter($this->dbUser)));
  120. $result = $query->execute();
  121. return $result->rowCount() > 0;
  122. }
  123. private function databaseExists(Connection $connection) {
  124. $builder = $connection->getQueryBuilder();
  125. $builder->automaticTablePrefix(false);
  126. $query = $builder->select('datname')
  127. ->from('pg_database')
  128. ->where($builder->expr()->eq('datname', $builder->createNamedParameter($this->dbName)));
  129. $result = $query->execute();
  130. return $result->rowCount() > 0;
  131. }
  132. private function createDBUser(Connection $connection) {
  133. $dbUser = $this->dbUser;
  134. try {
  135. $i = 1;
  136. while ($this->userExists($connection)) {
  137. $i++;
  138. $this->dbUser = $dbUser . $i;
  139. }
  140. // create the user
  141. $query = $connection->prepare('CREATE USER "' . addslashes($this->dbUser) . "\" CREATEDB PASSWORD '" . addslashes($this->dbPassword) . "'");
  142. $query->execute();
  143. if ($this->databaseExists($connection)) {
  144. $query = $connection->prepare('GRANT CONNECT ON DATABASE ' . addslashes($this->dbName) . ' TO "' . addslashes($this->dbUser) . '"');
  145. $query->execute();
  146. }
  147. } catch (DatabaseException $e) {
  148. $this->logger->error('Error while trying to create database user', [
  149. 'exception' => $e,
  150. ]);
  151. }
  152. }
  153. }