WriteUsersInPostgreSQL calls exec to create users that do not exist in PostgreSQL. Once they exist, it updates their options and passwords and grants them access to their specified databases. The databases must already exist.
( ctx context.Context, cluster *v1beta1.PostgresCluster, exec Executor, users []v1beta1.PostgresUserSpec, verifiers map[string]string, )
| 61 | // grants them access to their specified databases. The databases must already |
| 62 | // exist. |
| 63 | func WriteUsersInPostgreSQL( |
| 64 | ctx context.Context, cluster *v1beta1.PostgresCluster, exec Executor, |
| 65 | users []v1beta1.PostgresUserSpec, verifiers map[string]string, |
| 66 | ) error { |
| 67 | log := logging.FromContext(ctx) |
| 68 | |
| 69 | var err error |
| 70 | var sql bytes.Buffer |
| 71 | |
| 72 | // Do not wait for changes to be replicated. [Since PostgreSQL v9.1] |
| 73 | // - https://www.postgresql.org/docs/current/runtime-config-wal.html |
| 74 | _, _ = sql.WriteString(`SET synchronous_commit = LOCAL;`) |
| 75 | |
| 76 | // Prevent unexpected dereferences by emptying "search_path". The "pg_catalog" |
| 77 | // schema is still searched, and only temporary objects can be created. |
| 78 | // - https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-SEARCH-PATH |
| 79 | _, _ = sql.WriteString(`SET search_path TO '';`) |
| 80 | |
| 81 | // Fill a temporary table with the JSON of the user specifications. |
| 82 | // "\copy" reads from subsequent lines until the special line "\.". |
| 83 | // - https://www.postgresql.org/docs/current/app-psql.html#APP-PSQL-META-COMMANDS-COPY |
| 84 | _, _ = sql.WriteString(` |
| 85 | CREATE TEMPORARY TABLE input (id serial, data json); |
| 86 | \copy input (data) from stdin with (format text) |
| 87 | `) |
| 88 | encoder := json.NewEncoder(&sql) |
| 89 | encoder.SetEscapeHTML(false) |
| 90 | |
| 91 | for i := range users { |
| 92 | spec := users[i] |
| 93 | |
| 94 | databases := spec.Databases |
| 95 | options := sanitizeAlterRoleOptions(spec.Options) |
| 96 | |
| 97 | // The "postgres" user must always be a superuser that can login to |
| 98 | // the "postgres" database. |
| 99 | if spec.Name == "postgres" { |
| 100 | databases = append(databases[:0:0], "postgres") |
| 101 | options = `LOGIN SUPERUSER` |
| 102 | } |
| 103 | |
| 104 | if err == nil { |
| 105 | err = encoder.Encode(map[string]any{ |
| 106 | "databases": databases, |
| 107 | "options": options, |
| 108 | "username": spec.Name, |
| 109 | "verifier": verifiers[spec.Name], |
| 110 | }) |
| 111 | } |
| 112 | } |
| 113 | _, _ = sql.WriteString(`\.` + "\n") |
| 114 | |
| 115 | // Create the following objects in a transaction so that permissions are |
| 116 | // correct before any other session sees them. |
| 117 | // - https://www.postgresql.org/docs/current/ddl-priv.html |
| 118 | _, _ = sql.WriteString(`BEGIN;`) |
| 119 | |
| 120 | // Create users that do not already exist. Permissions are granted later. |