Ensure that schema search path is set with every connection on postgres (#14131) (#14216)

Backport #14131

Unfortunately every connection to postgres requires that the search path is
set appropriately.

This PR shadows the postgres driver to ensure that as soon as a connection
is open, the search_path is set appropriately.

Fix #14088

Signed-off-by: Andrew Thornton <art27@cantab.net>

1 files changed, 75 insertions, 0 deletions

diff --git a/models/sql_postgres_with_schema.go b/models/sql_postgres_with_schema.go
new file mode 100644
index 0000000000..0c8893ea27
--- /dev/null
+++ b/models/sql_postgres_with_schema.go
@@ -0,0 +1,75 @@
+// Copyright 2020 The Gitea Authors. All rights reserved.
+// Use of this source code is governed by a MIT-style
+// license that can be found in the LICENSE file.
+
+package models
+
+import (
+	"database/sql"
+	"database/sql/driver"
+	"sync"
+
+	"code.gitea.io/gitea/modules/setting"
+
+	"github.com/lib/pq"
+	"xorm.io/xorm/dialects"
+)
+
+var registerOnce sync.Once
+
+func registerPostgresSchemaDriver() {
+	registerOnce.Do(func() {
+		sql.Register("postgresschema", &postgresSchemaDriver{})
+		dialects.RegisterDriver("postgresschema", dialects.QueryDriver("postgres"))
+	})
+}
+
+type postgresSchemaDriver struct {
+	pq.Driver
+}
+
+// Open opens a new connection to the database. name is a connection string.
+// This function opens the postgres connection in the default manner but immediately
+// runs set_config to set the search_path appropriately
+func (d *postgresSchemaDriver) Open(name string) (driver.Conn, error) {
+	conn, err := d.Driver.Open(name)
+	if err != nil {
+		return conn, err
+	}
+	schemaValue, _ := driver.String.ConvertValue(setting.Database.Schema)
+
+	// golangci lint is incorrect here - there is no benefit to using driver.ExecerContext here
+	// and in any case pq does not implement it
+	if execer, ok := conn.(driver.Execer); ok { //nolint
+		_, err := execer.Exec(`SELECT set_config(
+			'search_path',
+			$1 || ',' || current_setting('search_path'),
+			false)`, []driver.Value{schemaValue}) //nolint
+		if err != nil {
+			_ = conn.Close()
+			return nil, err
+		}
+		return conn, nil
+	}
+
+	stmt, err := conn.Prepare(`SELECT set_config(
+		'search_path',
+		$1 || ',' || current_setting('search_path'),
+		false)`)
+	if err != nil {
+		_ = conn.Close()
+		return nil, err
+	}
+	defer stmt.Close()
+
+	// driver.String.ConvertValue will never return err for string
+
+	// golangci lint is incorrect here - there is no benefit to using stmt.ExecWithContext here
+	_, err = stmt.Exec([]driver.Value{schemaValue}) //nolint
+	if err != nil {
+		_ = conn.Close()
+		return nil, err
+	}
+
+	return conn, nil
+}