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]