Function WriteUsersSchemasInPostgreSQL

internal/postgres/users.go:184–252

WriteUsersSchemasInPostgreSQL will create a schema for each user in each database that user has access to

(ctx context.Context, exec Executor,
	users []v1beta1.PostgresUserSpec)

// WriteUsersSchemasInPostgreSQL will create a schema for each user in each database that user has access to
func WriteUsersSchemasInPostgreSQL(ctx context.Context, exec Executor,
	users []v1beta1.PostgresUserSpec) error {

	log := logging.FromContext(ctx)

	var err error
	var stdout string
	var stderr string

	for i := range users {
		spec := users[i]

		// We skip if the user has the name of a reserved schema
		if RESERVED_SCHEMA_NAMES[spec.Name] {
			log.V(1).Info("Skipping schema creation for user with reserved name",
				"name", spec.Name)
			continue
		}

		// We skip if the user has no databases
		if len(spec.Databases) == 0 {
			continue
		}

		var sql bytes.Buffer

		// Prevent unexpected dereferences by emptying "search_path". The "pg_catalog"
		// schema is still searched, and only temporary objects can be created.
		// - https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-SEARCH-PATH
		_, _ = sql.WriteString(`SET search_path TO '';`)

		_, _ = sql.WriteString(`SELECT * FROM json_array_elements_text(:'databases');`)

		databases, _ := json.Marshal(spec.Databases)

		stdout, stderr, err = exec.ExecInDatabasesFromQuery(ctx,
			sql.String(),
			strings.Join([]string{
				// Quiet NOTICE messages from IF EXISTS statements.
				// - https://www.postgresql.org/docs/current/runtime-config-client.html
				`SET client_min_messages = WARNING;`,

				// Do not wait for changes to be replicated. [Since PostgreSQL v9.1]
				// - https://www.postgresql.org/docs/current/runtime-config-wal.html
				`SET synchronous_commit = LOCAL;`,

				// Creates a schema named after and owned by the user
				// - https://www.postgresql.org/docs/current/ddl-schemas.html
				// - https://www.postgresql.org/docs/current/sql-createschema.html

				// We create a schema named after the user because
				// the PG search_path does not need to be updated,
				// since search_path defaults to "$user", public.
				// - https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH
				`CREATE SCHEMA IF NOT EXISTS :"username" AUTHORIZATION :"username";`,
			}, "\n"),
			map[string]string{
				"databases": string(databases),