Add support for database schema in PostgreSQL (#8819)

* Add support for database schema

* Require setting search_path for the db user

* Add schema setting to admin/config.tmpl

* Use a schema different from default for psql tests

* Update postgres scripts to use custom schema

* Update to xorm/core 0.7.3 and xorm/xorm c37aff9b3a

* Fix migration test

Co-authored-by: Antoine GIRARD <sapk@users.noreply.github.com>
Co-authored-by: Lunny Xiao <xiaolunwen@gmail.com>

6 files changed, 67 insertions, 5 deletions

diff --git a/integrations/integration_test.go b/integrations/integration_test.go
index bf363f3b4d..138d751859 100644
--- a/integrations/integration_test.go
+++ b/integrations/integration_test.go
@@ -153,18 +153,53 @@ func initIntegrationTest() {
 		if err != nil {
 			log.Fatalf("sql.Open: %v", err)
 		}
-		rows, err := db.Query(fmt.Sprintf("SELECT 1 FROM pg_database WHERE datname = '%s'", setting.Database.Name))
+		dbrows, err := db.Query(fmt.Sprintf("SELECT 1 FROM pg_database WHERE datname = '%s'", setting.Database.Name))
 		if err != nil {
 			log.Fatalf("db.Query: %v", err)
 		}
-		defer rows.Close()
+		defer dbrows.Close()
 
-		if rows.Next() {
+		if !dbrows.Next() {
+			if _, err = db.Exec(fmt.Sprintf("CREATE DATABASE %s", setting.Database.Name)); err != nil {
+				log.Fatalf("db.Exec: CREATE DATABASE: %v", err)
+			}
+		}
+		// Check if we need to setup a specific schema
+		if len(setting.Database.Schema) == 0 {
 			break
 		}
-		if _, err = db.Exec(fmt.Sprintf("CREATE DATABASE %s", setting.Database.Name)); err != nil {
-			log.Fatalf("db.Exec: %v", err)
+		db.Close()
+
+		db, err = sql.Open("postgres", fmt.Sprintf("postgres://%s:%s@%s/%s?sslmode=%s",
+			setting.Database.User, setting.Database.Passwd, setting.Database.Host, setting.Database.Name, setting.Database.SSLMode))
+		// This is a different db object; requires a different Close()
+		defer db.Close()
+		if err != nil {
+			log.Fatalf("sql.Open: %v", err)
+		}
+		schrows, err := db.Query(fmt.Sprintf("SELECT 1 FROM information_schema.schemata WHERE schema_name = '%s'", setting.Database.Schema))
+		if err != nil {
+			log.Fatalf("db.Query: %v", err)
+		}
+		defer schrows.Close()
+
+		if !schrows.Next() {
+			// Create and setup a DB schema
+			if _, err = db.Exec(fmt.Sprintf("CREATE SCHEMA %s", setting.Database.Schema)); err != nil {
+				log.Fatalf("db.Exec: CREATE SCHEMA: %v", err)
+			}
+		}
+
+		// Make the user's default search path the created schema; this will affect new connections
+		if _, err = db.Exec(fmt.Sprintf(`ALTER USER "%s" SET search_path = %s`, setting.Database.User, setting.Database.Schema)); err != nil {
+			log.Fatalf("db.Exec: ALTER USER SET search_path: %v", err)
+		}
+
+		// Make the current connection's search the created schema
+		if _, err = db.Exec(fmt.Sprintf(`SET search_path = %s`, setting.Database.Schema)); err != nil {
+			log.Fatalf("db.Exec: ALTER USER SET search_path: %v", err)
 		}
+
 	case setting.Database.UseMSSQL:
 		host, port := setting.ParseMSSQLHostPort(setting.Database.Host)
 		db, err := sql.Open("mssql", fmt.Sprintf("server=%s; port=%s; database=%s; user id=%s; password=%s;",
diff --git a/integrations/migration-test/gitea-v1.5.3.postgres.sql.gz b/integrations/migration-test/gitea-v1.5.3.postgres.sql.gz
index 2fcad82111..2380f8dd7f 100644
--- a/integrations/migration-test/gitea-v1.5.3.postgres.sql.gz
+++ b/integrations/migration-test/gitea-v1.5.3.postgres.sql.gz
diff --git a/integrations/migration-test/gitea-v1.6.4.postgres.sql.gz b/integrations/migration-test/gitea-v1.6.4.postgres.sql.gz
index 8db9022d1c..bd66f6ba4f 100644
--- a/integrations/migration-test/gitea-v1.6.4.postgres.sql.gz
+++ b/integrations/migration-test/gitea-v1.6.4.postgres.sql.gz
diff --git a/integrations/migration-test/gitea-v1.7.0.postgres.sql.gz b/integrations/migration-test/gitea-v1.7.0.postgres.sql.gz
index ed66d41b89..e4716c6b43 100644
--- a/integrations/migration-test/gitea-v1.7.0.postgres.sql.gz
+++ b/integrations/migration-test/gitea-v1.7.0.postgres.sql.gz
diff --git a/integrations/migration-test/migration_test.go b/integrations/migration-test/migration_test.go
index c274d482da..6cdfbf7d7d 100644
--- a/integrations/migration-test/migration_test.go
+++ b/integrations/migration-test/migration_test.go
@@ -168,6 +168,32 @@ func restoreOldDB(t *testing.T, version string) bool {
 		assert.NoError(t, err)
 		db.Close()
 
+		// Check if we need to setup a specific schema
+		if len(setting.Database.Schema) != 0 {
+			db, err = sql.Open("postgres", fmt.Sprintf("postgres://%s:%s@%s/%s?sslmode=%s",
+				setting.Database.User, setting.Database.Passwd, setting.Database.Host, setting.Database.Name, setting.Database.SSLMode))
+			if !assert.NoError(t, err) {
+				return false
+			}
+			schrows, err := db.Query(fmt.Sprintf("SELECT 1 FROM information_schema.schemata WHERE schema_name = '%s'", setting.Database.Schema))
+			if !assert.NoError(t, err) || !assert.NotEmpty(t, schrows) {
+				return false
+			}
+
+			if !schrows.Next() {
+				// Create and setup a DB schema
+				_, err = db.Exec(fmt.Sprintf("CREATE SCHEMA %s", setting.Database.Schema))
+				assert.NoError(t, err)
+			}
+			schrows.Close()
+
+			// Make the user's default search path the created schema; this will affect new connections
+			_, err = db.Exec(fmt.Sprintf(`ALTER USER "%s" SET search_path = %s`, setting.Database.User, setting.Database.Schema))
+			assert.NoError(t, err)
+
+			db.Close()
+		}
+
 		db, err = sql.Open("postgres", fmt.Sprintf("postgres://%s:%s@%s/%s?sslmode=%s",
 			setting.Database.User, setting.Database.Passwd, setting.Database.Host, setting.Database.Name, setting.Database.SSLMode))
 		assert.NoError(t, err)
diff --git a/integrations/pgsql.ini.tmpl b/integrations/pgsql.ini.tmpl
index 6265e0d98e..f337d98fb4 100644
--- a/integrations/pgsql.ini.tmpl
+++ b/integrations/pgsql.ini.tmpl
@@ -7,6 +7,7 @@ HOST     = {{TEST_PGSQL_HOST}}
 NAME     = {{TEST_PGSQL_DBNAME}}
 USER     = {{TEST_PGSQL_USERNAME}}
 PASSWD   = {{TEST_PGSQL_PASSWORD}}
+SCHEMA   = {{TEST_PGSQL_SCHEMA}}
 SSL_MODE = disable
 
 [indexer]

path: root/integrations

<?php
/**
 * @copyright Copyright (c) 2016 Lukas Reschke <lukas@statuscode.ch>
 *
 * @author Lukas Reschke <lukas@statuscode.ch>
 *
 * @license GNU AGPL version 3 or any later version
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU Affero General Public License as
 * published by the Free Software Foundation, either version 3 of the
 * License, or (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU Affero General Public License for more details.
 *
 * You should have received a copy of the GNU Affero General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 *
 */

namespace Tests\Settings;

use OC\Accounts\AccountManager;
use OC\Settings\Admin\Sharing;
use OC\Settings\Manager;
use OC\Settings\Mapper;
use OC\Settings\Personal\Security;
use OC\Settings\Section;
use OCP\App\IAppManager;
use OCP\Encryption\IManager;
use OCP\IConfig;
use OCP\IDBConnection;
use OCP\IGroupManager;
use OCP\IL10N;
use OCP\ILogger;
use OCP\IRequest;
use OCP\IURLGenerator;
use OCP\IUserManager;
use OCP\L10N\IFactory;
use OCP\Lock\ILockingProvider;
use Test\TestCase;

class ManagerTest extends TestCase {
	/** @var Manager|\PHPUnit_Framework_MockObject_MockObject */
	private $manager;
	/** @var ILogger|\PHPUnit_Framework_MockObject_MockObject */
	private $logger;
	/** @var IDBConnection|\PHPUnit_Framework_MockObject_MockObject */
	private $dbConnection;
	/** @var IL10N|\PHPUnit_Framework_MockObject_MockObject */
	private $l10n;
	/** @var IConfig|\PHPUnit_Framework_MockObject_MockObject */
	private $config;
	/** @var IManager|\PHPUnit_Framework_MockObject_MockObject */
	private $encryptionManager;
	/** @var IUserManager|\PHPUnit_Framework_MockObject_MockObject */
	private $userManager;
	/** @var ILockingProvider|\PHPUnit_Framework_MockObject_MockObject */
	private $lockingProvider;
	/** @var IRequest|\PHPUnit_Framework_MockObject_MockObject */
	private $request;
	/** @var IURLGenerator|\PHPUnit_Framework_MockObject_MockObject */
	private $url;
	/** @var AccountManager|\PHPUnit_Framework_MockObject_MockObject */
	private $accountManager;
	/** @var  IGroupManager|\PHPUnit_Framework_MockObject_MockObject */
	private $groupManager;
	/** @var  IFactory|\PHPUnit_Framework_MockObject_MockObject */
	private $l10nFactory;
	/** @var IAppManager */
	private $appManager;

	public function setUp() {
		parent::setUp();

		$this->logger = $this->createMock(ILogger::class);
		$this->dbConnection = $this->createMock(IDBConnection::class);
		$this->l10n = $this->createMock(IL10N::class);
		$this->config = $this->createMock(IConfig::class);
		$this->encryptionManager = $this->createMock(IManager::class);
		$this->userManager = $this->createMock(IUserManager::class);
		$this->lockingProvider = $this->createMock(ILockingProvider::class);
		$this->request = $this->createMock(IRequest::class);
		$this->url = $this->createMock(IURLGenerator::class);
		$this->accountManager = $this->createMock(AccountManager::class);
		$this->groupManager = $this->createMock(IGroupManager::class);
		$this->l10nFactory = $this->createMock(IFactory::class);
		$this->appManager = $this->createMock(IAppManager::class);

		$this->manager = new Manager(
			$this->logger,
			$this->dbConnection,
			$this->l10n,
			$this->config,
			$this->encryptionManager,
			$this->userManager,
			$this->lockingProvider,
			$this->request,
			$this->url,
			$this->accountManager,
			$this->groupManager,
			$this->l10nFactory,
			$this->appManager
		);
	}

	public function testGetAdminSections() {
		$this->l10n
			->expects($this->any())
			->method('t')
			->will($this->returnArgument(0));

		$this->manager->registerSection('admin', \OCA\WorkflowEngine\Settings\Section::class);

		$this->url->expects($this->exactly(6))
			->method('imagePath')
			->willReturnMap([
				['settings', 'admin.svg', '0'],
				['core', 'actions/settings-dark.svg', '1'],
				['core', 'actions/share.svg', '2'],
				['core', 'actions/password.svg', '3'],
				['core', 'places/contacts.svg', '5'],
				['settings', 'help.svg', '4'],
			]);

		$this->assertEquals([
			0 => [new Section('overview', 'Overview', 0, '0')],
			1 => [new Section('server', 'Basic settings', 0, '1')],
			5 => [new Section('sharing', 'Sharing', 0, '2')],
			10 => [new Section('security', 'Security', 0, '3')],
			50 => [new Section('groupware', 'Groupware', 0, '5')],
			55 => [\OC::$server->query(\OCA\WorkflowEngine\Settings\Section::class)],
			98 => [new Section('additional', 'Additional settings', 0, '1')],
		], $this->manager->getAdminSections());
	}

	public function testGetPersonalSections() {
		$this->l10n
			->expects($this->any())
			->method('t')
			->will($this->returnArgument(0));

		$this->manager->registerSection('personal', \OCA\WorkflowEngine\Settings\Section::class);

		$this->url->expects($this->exactly(3))
			->method('imagePath')
			->willReturnMap([
				['core', 'actions/info.svg', '1'],
				['settings', 'password.svg', '2'],
				['core', 'clients/phone.svg', '3'],
			]);

		$this->assertEquals([
			0 => [new Section('personal-info', 'Personal info', 0, '1')],
			5 => [new Section('security', 'Security', 0, '2')],
			15 => [new Section('sync-clients', 'Mobile & desktop', 0, '3')],
			55 => [\OC::$server->query(\OCA\WorkflowEngine\Settings\Section::class)],
		], $this->manager->getPersonalSections());
	}

	public function testGetAdminSectionsEmptySection() {
		$this->l10n
			->expects($this->any())
			->method('t')
			->will($this->returnArgument(0));

		$this->url->expects($this->exactly(6))
			->method('imagePath')
			->willReturnMap([
				['settings', 'admin.svg', '0'],
				['core', 'actions/settings-dark.svg', '1'],
				['core', 'actions/share.svg', '2'],
				['core', 'actions/password.svg', '3'],
				['core', 'places/contacts.svg', '5'],
				['settings', 'help.svg', '4'],
			]);

		$this->assertEquals([
			0 => [new Section('overview', 'Overview', 0, '0')],
			1 => [new Section('server', 'Basic settings', 0, '1')],
			5 => [new Section('sharing', 'Sharing', 0, '2')],
			10 => [new Section('security', 'Security', 0, '3')],
			50 => [new Section('groupware', 'Groupware', 0, '5')],
			98 => [new Section('additional', 'Additional settings', 0, '1')],
		], $this->manager->getAdminSections());
	}

	public function testGetPersonalSectionsEmptySection() {
		$this->l10n
			->expects($this->any())
			->method('t')
			->will($this->returnArgument(0));

		$this->url->expects($this->exactly(3))
			->method('imagePath')
			->willReturnMap([
				['core', 'actions/info.svg', '1'],
				['settings', 'password.svg', '2'],
				['core', 'clients/phone.svg', '3'],
			]);

		$this->assertArraySubset([
			0 => [new Section('personal-info', 'Personal info', 0, '1')],
			5 => [new Section('security', 'Security', 0, '2')],
			15 => [new Section('sync-clients', 'Mobile & desktop', 0, '3')],
		], $this->manager->getPersonalSections());
	}

	public function testGetAdminSettings() {
		$this->assertEquals([
			0 => [new Sharing($this->config, $this->l10n)],
		], $this->manager->getAdminSettings('sharing'));
	}

	public function testGetPersonalSettings() {
		$this->assertEquals([
			10 => [new Security($this->userManager)],
		], $this->manager->getPersonalSettings('security'));
	}

	public function testSameSectionAsPersonalAndAdmin() {
		$this->l10n
			->expects($this->any())
			->method('t')
			->will($this->returnArgument(0));

		$this->manager->registerSection('personal', \OCA\WorkflowEngine\Settings\Section::class);
		$this->manager->registerSection('admin', \OCA\WorkflowEngine\Settings\Section::class);

		$this->url->expects($this->exactly(9))
			->method('imagePath')
			->willReturnMap([
				['core', 'actions/info.svg', '1'],
				['settings', 'password.svg', '2'],
				['core', 'clients/phone.svg', '3'],
				['settings', 'admin.svg', '0'],
				['core', 'actions/settings-dark.svg', '1'],
				['core', 'actions/share.svg', '2'],
				['core', 'actions/password.svg', '3'],
				['core', 'places/contacts.svg', '5'],
				['settings', 'help.svg', '4'],
			]);

		$this->assertEquals([
			0 => [new Section('personal-info', 'Personal info', 0, '1')],
			5 => [new Section('security', 'Security', 0, '2')],
			15 => [new Section('sync-clients', 'Mobile & desktop', 0, '3')],
			55 => [\OC::$server->query(\OCA\WorkflowEngine\Settings\Section::class)],
		], $this->manager->getPersonalSections());

		$this->assertEquals([
			0 => [new Section('overview', 'Overview', 0, '0')],
			1 => [new Section('server', 'Basic settings', 0, '1')],
			5 => [new Section('sharing', 'Sharing', 0, '2')],
			10 => [new Section('security', 'Security', 0, '3')],
			50 => [new Section('groupware', 'Groupware', 0, '5')],
			55 => [\OC::$server->query(\OCA\WorkflowEngine\Settings\Section::class)],
			98 => [new Section('additional', 'Additional settings', 0, '1')],
		], $this->manager->getAdminSections());
	}
}