cozystack/packages/apps/postgres/templates/init-script.yaml

{{- $existingSecret := lookup "v1" "Secret" .Release.Namespace (printf "%s-credentials" .Release.Name) }}
{{- $passwords := dict }}

{{- with (index $existingSecret "data") }}
  {{- range $k, $v := . }}
    {{- $_ := set $passwords $k (b64dec $v) }}
  {{- end }}
{{- end }}

{{/*
  Reject a username outside the Secret-key charset before it is interpolated
  into the credentials Secret key, the init-script shell (pw=$(cat "/credentials/<user>"))
  and the role SQL. The charset mirrors k8s IsConfigMapKey (rejecting ".", any name
  starting with "..", and the metacharacters that would reach the shell). The
  length cap is 63, NOT the 253-char Secret-key limit: the username is also a
  PostgreSQL role name, and an identifier over NAMEDATALEN-1 (63 bytes) is silently
  TRUNCATED by CREATE ROLE with a NOTICE, not an error, so ON_ERROR_STOP never sees
  it: the role exists under its 63-byte prefix, two long names sharing that prefix
  become one role, and the stored name is not the one the tenant declared. Cap at
  63 so a too-long name is named here instead. A release that installed before the
  cap still carries such a user's key in the credentials Secret read above and its
  role under the truncated name; failing its upgrade would wedge the whole release,
  so on upgrade the cap applies only to a user that Secret does not already carry,
  i.e. a new one. The charset holds on both paths: a name
  outside it is not a valid Secret key, so it never installed. The charset is
  ASCII-only, so len (bytes) equals characters.
*/}}
{{- range $user, $u := .Values.users }}
  {{- $tooLong := and (gt (len $user) 63) (or $.Release.IsInstall (not (hasKey $passwords $user))) }}
  {{- if or (not (regexMatch "^[-._a-zA-Z0-9]+$" $user)) (eq $user ".") (hasPrefix ".." $user) $tooLong }}
    {{- fail (printf "invalid username %q: a username may contain only letters, digits and the characters -._ , must not be \".\" or start with \"..\", and must be at most 63 characters (it is a PostgreSQL role name, which CREATE ROLE silently truncates past 63 bytes, and it becomes a Kubernetes Secret key)" $user) }}
  {{- end }}
{{- end }}

{{/*
  A database name is interpolated into SQL identifiers (CREATE DATABASE "<db>", the
  "<db>_admin"/"<db>_readonly" role names), into a single-quoted SQL string literal
  in the superuser DO block ('<db>_admin' as a format() argument), and into a
  space-separated shell list (DEFINED_ROLES). Reject any name outside the same safe
  charset the username guard enforces so none of those contexts can be broken out
  of: a "'" would escape the DO-block literal into arbitrary superuser SQL, a "\""
  the CREATE DATABASE identifier, a space or ";" the shell list. The charset is the
  injection guard, so it holds on install and upgrade alike: a database added on
  upgrade is still a new name. It does not reject "." or a ".." prefix the way the
  username guard does, because a database name is not a Secret key. The length cap
  is 54 because the name also seeds the "<db>_admin" and "<db>_readonly" roles;
  "_readonly" is 9 bytes, so past 54 bytes a role truncates past NAMEDATALEN (63).
  The next init-job run then orphan-drops the truncated role, DROP OWNED only
  reaches the database it runs in, and DROP ROLE fails on the grants inside the
  long-named database, so such a release cannot survive a second run anyway. The
  cap therefore holds on upgrade too: failing the render names
  the problem instead of crash-looping the init-job. Extension names are
  PostgreSQL identifiers too, capped at 63.
*/}}
{{- range $database, $d := .Values.databases }}
  {{- if or (not (regexMatch "^[-._a-zA-Z0-9]+$" $database)) (gt (len $database) 54) }}
    {{- fail (printf "invalid database name %q: a database name may contain only letters, digits and the characters -._ and must be at most 54 characters (it is interpolated into superuser SQL and the init-script shell, and it seeds the \"<db>_admin\" and \"<db>_readonly\" PostgreSQL roles, which CREATE ROLE silently truncates past 63 bytes; the next init-job run then cannot drop the truncated role and fails). Changing only its key in values makes the init-job drop the database; to keep the data, a platform administrator first renames it as the CNPG superuser (ALTER DATABASE, and ALTER ROLE for its <db>_admin and <db>_readonly roles), then the key is changed" $database) }}
  {{- end }}
  {{- range $extension := ($d.extensions | default (list)) }}
    {{- $name := include "postgres.extensionName" $extension }}
    {{- if or (not (regexMatch "^[-._a-zA-Z0-9]+$" $name)) (eq $name ".") (hasPrefix ".." $name) (gt (len $name) 63) }}
      {{- fail (printf "invalid extension name %q for database %q: an extension name may contain only letters, digits and the characters -._ , must not be \".\" or start with \"..\", and must be at most 63 characters (it is a PostgreSQL identifier interpolated into CREATE EXTENSION in a superuser session; real names carry \"-\", e.g. uuid-ossp, so it is quoted at the call site). A clause after the name, such as CASCADE or SCHEMA, is not accepted: remove it from the value, which leaves an extension that is already installed as it is, and list the extensions it depends on before it, e.g. [cube, earthdistance]" $extension $database) }}
    {{- end }}
  {{- end }}
{{- end }}

{{/*
  `postgres` is the CNPG-managed cluster superuser and must never be redefined as
  an app role. Reject it here, before the bootstrap gate, not only in the
  create-users block below: that block is skipped while bootstrap.enabled is true,
  so a restore target carrying users.postgres would install, and the restore
  controller's automatic bootstrap clear (disablePostgresAppBootstrap) would then
  wedge the very next reconcile against a fail it never had to see at install while
  the RestoreJob records Succeeded. Failing regardless of bootstrap keeps the
  controller from converging a release into an unrenderable state.
*/}}
{{- if and .Values.users (hasKey .Values.users "postgres") }}
  {{- fail "`users.postgres` is forbidden by policy. Use a different username." }}
{{- end }}

{{/*
  Unlike MariaDB (secret.yaml carries the same lookup with a guard), postgres does
  NOT reject an upgrade whose credentials lookup came back empty. The credentials
  Secret here is gated on .Values.users, so a release installed with the default
  `users: {}` has no Secret at all; adding its FIRST user is an upgrade whose
  lookup is legitimately empty, and a guard on `IsUpgrade and not $existingSecret`
  cannot tell that apart from a deleted Secret (both are empty). Postgres also has
  no equivalent of MariaDB's bootstrap-only root: every role's password is applied
  by the post-upgrade init-job (ALTER ROLE), so a re-mint on a genuinely deleted
  Secret self-heals on the next reconcile rather than desyncing forever. So mint on
  an empty lookup, and let the common first-user-on-upgrade path through.
*/}}

{{/*
  Passwords are always chart-generated and preserved across reconciles via the
  lookup above: a user keeps whatever password is already stored in the Secret,
  and only a user without one yet gets a freshly generated value.
*/}}
{{- range $user, $u := .Values.users }}
  {{- if not (index $passwords $user) }}
    {{- $_ := set $passwords $user (randAlphaNum 16) }}
  {{- end }}
{{- end }}

{{- if .Values.users }}
apiVersion: v1
kind: Secret
metadata:
  name: {{ .Release.Name }}-credentials
  {{- if .Values.bootstrap.enabled }}
  annotations:
    # During a restore the roles recovered from the backup keep their original
    # password hashes, but this Secret holds freshly generated passwords and the
    # init-job that would apply them (ALTER ROLE) is skipped while bootstrap is
    # on. So the values below do NOT authenticate yet; they converge once the app
    # leaves bootstrap and the init-job runs. Flag it so a reader (dashboard,
    # tenant) does not treat the advertised password as usable mid-restore.
    postgres.cozystack.io/credentials-pending: "true"
  {{- end }}
stringData:
  {{- range $user, $u := .Values.users }}
  {{ quote $user }}: {{ quote (index $passwords $user) }}
  {{- end }}
{{- end }}
{{/*
  Skip the init-script Secret while bootstrap.recovery is in effect.
  It only feeds the init-job Job, which is also skipped in that mode
  (see init-job.yaml header comment). Keeping it would leave a stale
  Secret without a consumer; dropping it keeps the chart output minimal.
*/}}
{{- if not .Values.bootstrap.enabled }}
---
apiVersion: v1
kind: Secret
metadata:
  name: {{ .Release.Name }}-init-script
stringData:
  init.sh: |
    #!/bin/bash
    set -e

    {{- /*
      Bounded readiness wait: this script runs as a post-install,post-upgrade Helm
      hook Helm blocks on, so an unbounded `until pg_isready` would hang the hook
      forever. The bound is wall-clock elapsed ($SECONDS), not an attempt count: an
      attempt count ignores the time pg_isready itself takes, so a loop sized in
      attempts can outlive the Job deadline, and the Job is then killed before this
      diagnostic prints or the work after readiness gets any time. Each bound sits
      below the Job's
      activeDeadlineSeconds so the tail is reserved for create-users and the
      per-schema grant pass. On UPGRADE the Cluster is already up, so give up after
      250s of the 540s deadline and a purged target surfaces THIS diagnostic, leaving
      290s for grants on a large database. On INSTALL the Cluster is still
      provisioning (PVC, image pull, initdb), so allow 480s of the 570s deadline for
      a cold-but-healthy first boot; the fresh databases need little of the 90s left.
      Those tails are upper bounds: activeDeadlineSeconds counts from the Job's
      start, while $SECONDS starts at 0 when this script does, so the pod's
      scheduling, image pull and volume mounts come out of the tail first. A slow
      start eats into the install tail, which is why a cold node can still hit the
      Job deadline; the upgrade tail is wide enough not to notice. A
      deleted-superuser-Secret mount never lets this loop run at all - the Job
      deadline covers that.
    */}}
    until pg_isready ; do
      if [ "$SECONDS" -ge {{ if .Release.IsInstall }}480{{ else }}250{{ end }} ]; then
        echo "postgres did not become ready in time; on a fresh install the Cluster may still be provisioning (check the cnpg.io/Cluster), on an upgrade the restore target may have been purged by a concurrent restore - failing the init-job rather than blocking the Helm hook" >&2
        exit 1
      fi
      sleep 5
    done

    {{- /*
      Role comments cannot tell which roles this chart manages: on PostgreSQL 16 a
      CREATEROLE holder may COMMENT ON ROLE every role it created, and it may drop
      the intermediate role that created one, leaving no pg_auth_members trace of
      who did. Membership in "cozystack:managed" can only be granted by a holder of
      ADMIN on it, which no role but the superuser ever gets, so the delete passes
      and the adoption checks below trust that instead. The name sits outside the
      username and database charsets, so values can never declare it. Its first run
      marks every role the chart had commented before the marker existed; no role
      could have held CREATEROLE through the chart by then. On PostgreSQL 16 and
      later the membership carries neither INHERIT nor SET, so it is a record and
      nothing else; the role holds no privileges on older majors either.
    */}}
    {{- $pgMajor := atoi (trimPrefix "v" .Values.version) }}
    {{- $markOptions := ternary " WITH INHERIT FALSE, SET FALSE" "" (ge $pgMajor 16) }}
    echo "== mark managed roles"
    psql -v ON_ERROR_STOP=1 --echo-all <<\EOT
    DO $$
    DECLARE
        managed record;
    BEGIN
        IF NOT EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = 'cozystack:managed') THEN
            CREATE ROLE "cozystack:managed" NOLOGIN;
            FOR managed IN SELECT r.rolname FROM pg_roles r JOIN pg_shdescription s ON s.objoid = r.oid AND s.classoid = 'pg_authid'::regclass WHERE s.description IN ('user managed by helm', 'role managed by helm') LOOP
                EXECUTE format('GRANT %I TO %I{{ $markOptions }}', 'cozystack:managed', managed.rolname);
            END LOOP;
        END IF;
    END$$;
    EOT

    echo "== create users"
    {{- if .Values.users }}
    {{- /*
      A CREATEROLE holder can create a role under a name values will declare later,
      plant objects owned by it, role settings and default privileges, then drop the
      intermediate role it did this through, so the squatted role shows no member at
      all by the time it is declared. No catalog check can tell that apart from a
      role a platform administrator created, so once any user has held createRole
      the chart refuses to adopt a role it did not mark. Before that, adopting is
      safe, and it is what a declared users.app does on install with the role CNPG
      bootstraps. The flag is read at the start of this session and set only after
      it, so the run that first grants createRole still adopts, and every user is
      created and marked before any of them holds CREATEROLE.
    */}}
    {{- $grantsCreateRole := false }}
    psql -v ON_ERROR_STOP=1 --echo-all <<\EOT
    {{- range $user, $u := .Values.users }}
    {{- if and (default false $u.createRole) (lt $pgMajor 16) }}
    {{- fail (printf "SECURITY: users.%s.createRole requires PostgreSQL 16 or newer. On PostgreSQL 15 and older, a role holding CREATEROLE can grant itself any non-superuser role, including pg_execute_server_program, which escalates to command execution on the instance." $user) }}
    {{- end }}
    {{- if default false $u.createRole }}
    {{- $grantsCreateRole = true }}
    {{- end }}
    SELECT format('DO $$BEGIN RAISE EXCEPTION %L; END$$', 'role "{{ $user }}" already exists and this chart did not create it; a user of this instance has held createRole, so the role may be controlled by that user and users.{{ $user }} will not adopt it. A platform administrator must inspect the role and drop it as the superuser, or declare the user under another name')
    FROM pg_catalog.pg_roles r
    WHERE r.rolname = '{{ $user }}'
      AND current_setting('cozystack.createrole_granted', true) = 'on'
      AND NOT EXISTS (SELECT FROM pg_catalog.pg_auth_members m JOIN pg_catalog.pg_roles g ON g.oid = m.roleid WHERE g.rolname = 'cozystack:managed' AND m.member = r.oid)\gexec
    SELECT 'CREATE ROLE "{{ $user }}" LOGIN INHERIT;'
    WHERE NOT EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = '{{ $user }}')\gexec
    GRANT "cozystack:managed" TO "{{ $user }}"{{ $markOptions }};
    {{- end }}
    EOT
    {{- if $grantsCreateRole }}
    psql -v ON_ERROR_STOP=1 --echo-all -c "ALTER DATABASE postgres SET cozystack.createrole_granted = 'on';"
    {{- end }}
    {{- /*
      Each password is read from the mounted <release>-credentials Secret at run
      time rather than rendered into this script: the init-script Secret is stored
      verbatim in the Helm release manifest and kept for MaxHistory revisions, so a
      literal password here would linger in cleartext history a tenant cannot scrub.
      psql's :'pw' quotes and escapes the value, so it applies safely whatever
      characters it holds. The password does reach psql through -v, i.e. its argv,
      readable via /proc/<pid>/cmdline to a process sharing this container's PID
      namespace -- but this init container runs alone, and any code with that reach
      can already read the mounted /credentials/* files directly, so the argv path
      widens no exposure. This rationale is a template comment so it ships ONCE, not
      once per user, into the rendered init.sh.
    */}}
    {{- range $user, $u := .Values.users }}
    pw=$(cat "/credentials/{{ $user }}")
    psql -v ON_ERROR_STOP=1 -v pw="$pw" <<\EOT
    ALTER ROLE "{{ $user }}" WITH PASSWORD :'pw' LOGIN INHERIT {{ ternary "REPLICATION" "NOREPLICATION" (default false $u.replication) }} {{ ternary "CREATEROLE" "NOCREATEROLE" (default false $u.createRole) }} {{ ternary "BYPASSRLS" "NOBYPASSRLS" (default false $u.bypassRls) }};
    {{- /*
      PostgreSQL 16 grants a CREATEROLE holder only ADMIN OPTION on the roles it
      creates, governed by createrole_self_grant, whose default is empty. ADMIN
      OPTION alone allows granting the role onward but not SET ROLE to it, and
      creating an object owned by another role requires SET. Without this the
      attribute looks functional — CREATE ROLE succeeds — yet the very next
      statement a tenant writes, CREATE SCHEMA ... AUTHORIZATION <new role>,
      fails with "must be able to SET ROLE". Set as a role-level default so it
      applies to every session, and reset when the attribute is withdrawn.
    */}}
    {{- if default false $u.createRole }}
    ALTER ROLE "{{ $user }}" SET createrole_self_grant = 'set, inherit';
    {{- else }}
    ALTER ROLE "{{ $user }}" RESET createrole_self_grant;
    {{- end }}
    COMMENT ON ROLE "{{ $user }}" IS 'user managed by helm';
    EOT
    {{- end }}
    {{- end }}

    echo "== delete users"
    {{- /*
      Read the managed users from the catalog rather than by parsing `\du+`: its
      column layout differs across PostgreSQL versions (16 dropped "Member of"), and
      matching the wrong column finds nothing, so a user removed from values would
      keep its LOGIN and password. Compare against the declared names truncated to
      63 bytes, the form CREATE ROLE stores, or a long name kept on upgrade would
      count as undeclared and be dropped on every run. REASSIGN OWNED and DROP OWNED
      act only on the database they run in, so run them in every connectable
      database: an object the user owns elsewhere would otherwise fail DROP USER
      and wedge every later run. The objects go to "cozystack:orphaned", a role
      that cannot log in and that the chart grants nothing: what a tenant role
      created must not become superuser-owned. Every dropped role's objects share
      that one owner, so its memberships are revoked and, after both drop passes,
      functions it owns are switched to SECURITY INVOKER and, from PostgreSQL 15,
      its views to security_invoker. A materialized view, a rule, and a view on
      an older server have no such switch and keep reading as that owner. Grants
      and memberships of the dropped role do not carry over, so a view or function
      it owned can lose access it had through them. The ":" is outside the
      username and database charsets, so no declared user or <db>_admin role can
      take the name. In the user and role drop passes, names read from the
      catalog reach SQL only as psql variables.
    */}}
    MANAGED_USERS=$(psql -v ON_ERROR_STOP=1 -t -A -c "SELECT r.rolname FROM pg_roles r JOIN pg_shdescription s ON s.objoid = r.oid AND s.classoid = 'pg_authid'::regclass WHERE s.description = 'user managed by helm' AND r.oid IN (SELECT m.member FROM pg_auth_members m JOIN pg_roles g ON g.oid = m.roleid WHERE g.rolname = 'cozystack:managed')")
    DEFINED_USERS="{{ $defined := list }}{{ range $user, $u := .Values.users }}{{ $defined = append $defined (trunc 63 $user) }}{{ end }}{{ join " " $defined }}"
    DELETE_USERS=$(for user in $MANAGED_USERS; do case " $DEFINED_USERS " in *" $user "*) :;; *) echo $user;; esac; done)
    ALL_DBS=$(psql -v ON_ERROR_STOP=1 -t -A -c "SELECT datname FROM pg_database WHERE datallowconn AND NOT datistemplate")

    psql -v ON_ERROR_STOP=1 --echo-all <<\EOT
    SELECT 'CREATE ROLE "cozystack:orphaned" NOLOGIN NOINHERIT'
    WHERE NOT EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = 'cozystack:orphaned')\gexec
    SELECT format('REVOKE %I FROM %I', r.rolname, m.rolname) FROM pg_auth_members am JOIN pg_roles m ON am.member = m.oid JOIN pg_roles r ON am.roleid = r.oid WHERE 'cozystack:orphaned' IN (r.rolname, m.rolname)\gexec
    ALTER ROLE "cozystack:orphaned" NOLOGIN NOINHERIT NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION NOBYPASSRLS;
    EOT

    echo "users to delete: $DELETE_USERS"
    for user in $DELETE_USERS; do
    for db in $ALL_DBS; do
    psql -v ON_ERROR_STOP=1 --echo-queries --dbname "$db" -v user="$user" <<\EOT
    REASSIGN OWNED BY :"user" TO "cozystack:orphaned";
    DROP OWNED BY :"user";
    EOT
    done
    psql -v ON_ERROR_STOP=1 --echo-queries -v user="$user" <<\EOT
    DROP USER :"user";
    EOT
    done

    echo "== delete databases"
    {{- /*
      A restore replaces spec.databases with the Backup's snapshot, but recovery
      replays WAL past it, so a database created after the Backup comes back still
      commented 'database managed by helm' and would be dropped here. The first run
      after a restore is told apart by bootstrap.newServerName, which the restore
      driver sets to a value unique to each RestoreJob; the last pass records it as
      a setting of the postgres database, which the recovery replaces with the
      source's along with the rest of the catalog. Until then an undeclared managed database is released
      instead: its comment and its roles' comments are cleared, which keeps the
      orphaned-roles pass below off roles that still hold grants inside it. The
      value is free text in values, so it arrives base64-encoded and reaches SQL
      only as a psql variable.
    */}}
    RESTORE_MARK={{ with .Values.bootstrap.newServerName }}$(echo {{ b64enc . }} | base64 -d){{ end }}
    ADOPTED_MARK=$(psql -v ON_ERROR_STOP=1 -t -A --dbname postgres -c "SELECT current_setting('cozystack.adopted_restore', true)")
    MANAGED_DBS=$(psql -v ON_ERROR_STOP=1 -t -A -c "SELECT datname FROM pg_database d JOIN pg_shdescription s ON d.oid = s.objoid WHERE s.description = 'database managed by helm'")
    DEFINED_DBS="{{ join " " (keys .Values.databases) }}"
    DELETE_DBS=$(for db in $MANAGED_DBS; do case " $DEFINED_DBS " in *" $db "*) :;; *) echo $db;; esac; done)

    if [ -n "$RESTORE_MARK" ] && [ "$ADOPTED_MARK" != "$RESTORE_MARK" ]; then
    echo "databases the restore brought back that values do not declare, released from management: $DELETE_DBS"
    for db in $DELETE_DBS; do
    psql -v ON_ERROR_STOP=1 --echo-all -v db="$db" <<\EOT
    COMMENT ON DATABASE :"db" IS NULL;
    SELECT format('COMMENT ON ROLE %I IS NULL', r) FROM unnest(ARRAY[:'db' || '_admin', :'db' || '_readonly']) AS r WHERE EXISTS (SELECT FROM pg_roles WHERE rolname = r)\gexec
    EOT
    done
    else
    echo "databases to delete: $DELETE_DBS"
    for db in $DELETE_DBS; do
    psql -v ON_ERROR_STOP=1 --echo-all -c "DROP DATABASE IF EXISTS \"$db\" WITH (FORCE);"
    done
    fi

    echo "== delete orphaned managed roles"
    MANAGED_ROLES=$(psql -v ON_ERROR_STOP=1 -t -A -c "SELECT rolname FROM pg_roles r JOIN pg_shdescription s ON r.oid = s.objoid WHERE s.description = 'role managed by helm' AND r.oid IN (SELECT m.member FROM pg_auth_members m JOIN pg_roles g ON g.oid = m.roleid WHERE g.rolname = 'cozystack:managed')")
    DEFINED_ROLES="{{ range $database, $d := .Values.databases }}{{ $database }}_admin {{ $database }}_readonly {{ end }}"
    DELETE_ROLES=$(for role in $MANAGED_ROLES; do case " $DEFINED_ROLES " in *" $role "*) :;; *) echo $role;; esac; done)

    {{- /*
      Same per-database REASSIGN/DROP OWNED as the user pass, for the same reason.
      The database list is read again because the pass above may have dropped
      some of the databases the first read returned.
    */}}
    ALL_DBS=$(psql -v ON_ERROR_STOP=1 -t -A -c "SELECT datname FROM pg_database WHERE datallowconn AND NOT datistemplate")

    echo "roles to delete: $DELETE_ROLES"
    for role in $DELETE_ROLES; do
    psql -v ON_ERROR_STOP=1 --echo-queries -v role="$role" <<\EOT
    SELECT format('REVOKE %I FROM %I', r.rolname, m.rolname) FROM pg_auth_members am JOIN pg_roles m ON am.member = m.oid JOIN pg_roles r ON am.roleid = r.oid WHERE r.rolname = :'role'\gexec
    EOT
    for db in $ALL_DBS; do
    psql -v ON_ERROR_STOP=1 --echo-queries --dbname "$db" -v role="$role" <<\EOT
    REASSIGN OWNED BY :"role" TO "cozystack:orphaned";
    DROP OWNED BY :"role";
    EOT
    done
    psql -v ON_ERROR_STOP=1 --echo-queries -v role="$role" <<\EOT
    DROP ROLE IF EXISTS :"role";
    EOT
    done

    for db in $ALL_DBS; do
    psql -v ON_ERROR_STOP=1 --echo-queries --dbname "$db" <<\EOT
    SELECT format('ALTER %s %s SECURITY INVOKER', CASE p.prokind WHEN 'p' THEN 'PROCEDURE' ELSE 'FUNCTION' END, p.oid::regprocedure) FROM pg_proc p WHERE p.proowner = '"cozystack:orphaned"'::regrole AND p.prosecdef\gexec
    SELECT format('ALTER VIEW %s SET (security_invoker = true)', c.oid::regclass) FROM pg_class c WHERE c.relowner = '"cozystack:orphaned"'::regrole AND c.relkind = 'v' AND current_setting('server_version_num')::int >= 150000 AND NOT coalesce('security_invoker=true' = ANY (c.reloptions), false)\gexec
    EOT
    done

    echo "== create databases and roles"
    {{- if .Values.databases }}
    {{- /*
      The same adoption check as for users. This session starts after the create
      users pass has recorded createRole, so the run that first grants it already
      refuses: its CREATEROLE holders exist from that pass on.
    */}}
    psql -v ON_ERROR_STOP=1 --echo-all <<\EOT
    {{- range $database, $d := .Values.databases }}
    SELECT format('DO $$BEGIN RAISE EXCEPTION %L; END$$', format('role "%s" already exists and this chart did not create it; a user of this instance has held createRole, so the role may be controlled by that user and databases.{{ $database }} will not adopt it. A platform administrator must inspect the role and drop it as the superuser, or declare the database under another name', r.rolname))
    FROM pg_catalog.pg_roles r
    WHERE r.rolname IN ('{{ $database }}_admin', '{{ $database }}_readonly')
      AND current_setting('cozystack.createrole_granted', true) = 'on'
      AND NOT EXISTS (SELECT FROM pg_catalog.pg_auth_members m JOIN pg_catalog.pg_roles g ON g.oid = m.roleid WHERE g.rolname = 'cozystack:managed' AND m.member = r.oid)\gexec
    SELECT 'CREATE DATABASE "{{ $database }}"'
    WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = '{{ $database }}')\gexec
    COMMENT ON DATABASE "{{ $database }}" IS 'database managed by helm';
    SELECT 'CREATE ROLE "{{ $database }}_admin" NOINHERIT;'
    WHERE NOT EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = '{{ $database }}_admin')\gexec
    COMMENT ON ROLE "{{ $database }}_admin" IS 'role managed by helm';
    GRANT "cozystack:managed" TO "{{ $database }}_admin"{{ $markOptions }};
    SELECT 'CREATE ROLE "{{ $database }}_readonly" NOINHERIT;'
    WHERE NOT EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = '{{ $database }}_readonly')\gexec
    COMMENT ON ROLE "{{ $database }}_readonly" IS 'role managed by helm';
    GRANT "cozystack:managed" TO "{{ $database }}_readonly"{{ $markOptions }};
    {{- end }}
    EOT
    {{- end }}

    echo "== grant privileges on databases to roles"
    {{- range $database, $d := .Values.databases }}
    psql -v ON_ERROR_STOP=1 --echo-all -d "{{ $database }}" <<\EOT
    ALTER DATABASE "{{ $database }}" OWNER TO "{{ $database }}_admin";
    GRANT CONNECT ON DATABASE "{{ $database }}" TO "{{ $database }}_readonly";

    DO $$
    DECLARE
        schema_record record;
    BEGIN
        -- Loop over all schemas
        FOR schema_record IN SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN ('pg_catalog', 'information_schema') LOOP
            -- Changing Schema Ownership
            EXECUTE format('ALTER SCHEMA %I OWNER TO %I', schema_record.schema_name, '{{ $database }}_admin');

            -- Add rights for the admin role
            EXECUTE format('GRANT ALL ON SCHEMA %I TO %I', schema_record.schema_name, '{{ $database }}_admin');
            EXECUTE format('GRANT ALL ON ALL TABLES IN SCHEMA %I TO %I', schema_record.schema_name, '{{ $database }}_admin');
            EXECUTE format('GRANT ALL ON ALL SEQUENCES IN SCHEMA %I TO %I', schema_record.schema_name, '{{ $database }}_admin');
            EXECUTE format('GRANT ALL ON ALL FUNCTIONS IN SCHEMA %I TO %I', schema_record.schema_name, '{{ $database }}_admin');
            EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT ALL ON TABLES TO %I', schema_record.schema_name, '{{ $database }}_admin');
            EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT ALL ON SEQUENCES TO %I', schema_record.schema_name, '{{ $database }}_admin');
            EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT ALL ON FUNCTIONS TO %I', schema_record.schema_name, '{{ $database }}_admin');

            -- Add rights for the readonly role
            EXECUTE format('GRANT USAGE ON SCHEMA %I TO %I', schema_record.schema_name, '{{ $database }}_readonly');
            EXECUTE format('GRANT SELECT ON ALL TABLES IN SCHEMA %I TO %I', schema_record.schema_name, '{{ $database }}_readonly');
            EXECUTE format('GRANT USAGE ON ALL SEQUENCES IN SCHEMA %I TO %I', schema_record.schema_name, '{{ $database }}_readonly');
            EXECUTE format('GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA %I TO %I', schema_record.schema_name, '{{ $database }}_readonly');
            EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT SELECT ON TABLES TO %I', schema_record.schema_name, '{{ $database }}_readonly');
            EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT USAGE ON SEQUENCES TO %I', schema_record.schema_name, '{{ $database }}_readonly');
            EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT EXECUTE ON FUNCTIONS TO %I', schema_record.schema_name, '{{ $database }}_readonly');
        END LOOP;
    END$$;
    EOT

    echo "== setup event trigger for schema creation"
    psql -v ON_ERROR_STOP=1 --echo-all -d "{{ $database }}" <<\EOT
    CREATE OR REPLACE FUNCTION auto_grant_schema_privileges()
    RETURNS event_trigger LANGUAGE plpgsql AS $$
    DECLARE
        obj record;
    BEGIN
        FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands() WHERE command_tag = 'CREATE SCHEMA' LOOP
        EXECUTE format('ALTER SCHEMA %I OWNER TO %I', obj.object_identity, '{{ $database }}_admin');
        EXECUTE format('GRANT ALL ON SCHEMA %I TO %I', obj.object_identity, '{{ $database }}_admin');
        EXECUTE format('GRANT USAGE ON SCHEMA %I TO %I', obj.object_identity, '{{ $database }}_readonly');
        EXECUTE format('GRANT SELECT ON ALL TABLES IN SCHEMA %I TO %I', obj.object_identity, '{{ $database }}_readonly');
        EXECUTE format('GRANT USAGE ON ALL SEQUENCES IN SCHEMA %I TO %I', obj.object_identity, '{{ $database }}_readonly');
        EXECUTE format('GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA %I TO %I', obj.object_identity, '{{ $database }}_readonly');

        -- Set owner for schema
        EXECUTE format('ALTER SCHEMA %I OWNER TO %I', obj.object_identity, '{{ $database }}_admin');

        -- Set privileges for admin role
        EXECUTE format('GRANT ALL ON SCHEMA %I TO %I', obj.object_identity, '{{ $database }}_admin');
        EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT ALL ON TABLES TO %I', obj.object_identity, '{{ $database }}_admin');
        EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT ALL ON SEQUENCES TO %I', obj.object_identity, '{{ $database }}_admin');
        EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT ALL ON FUNCTIONS TO %I', obj.object_identity, '{{ $database }}_admin');

        -- Set privileges for readonly role
        EXECUTE format('GRANT USAGE ON SCHEMA %I TO %I', obj.object_identity, '{{ $database }}_readonly');
        EXECUTE format('GRANT SELECT ON ALL TABLES IN SCHEMA %I TO %I', obj.object_identity, '{{ $database }}_readonly');
        EXECUTE format('GRANT USAGE ON ALL SEQUENCES IN SCHEMA %I TO %I', obj.object_identity, '{{ $database }}_readonly');
        EXECUTE format('GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA %I TO %I', obj.object_identity, '{{ $database }}_readonly');
        EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT SELECT ON TABLES TO %I', obj.object_identity, '{{ $database }}_readonly');
        EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT USAGE ON SEQUENCES TO %I', obj.object_identity, '{{ $database }}_readonly');
        EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT EXECUTE ON FUNCTIONS TO %I', obj.object_identity, '{{ $database }}_readonly');
        END LOOP;
    END;
    $$;

    DROP EVENT TRIGGER IF EXISTS trigger_auto_grant;
    CREATE EVENT TRIGGER trigger_auto_grant ON ddl_command_end
    WHEN TAG IN ('CREATE SCHEMA')
    EXECUTE PROCEDURE auto_grant_schema_privileges();
    EOT
    {{- end }}

    echo "== assign roles to users"
    psql -v ON_ERROR_STOP=1 --echo-all <<\EOT
    {{- range $database, $d := .Values.databases }}
    {{- $roles := $d.roles | default dict }}
    {{- range $user, $u := $.Values.users }}
    {{- if has $user $roles.admin }}
    GRANT "{{ $database }}_admin" TO "{{ $user }}";
    {{- else }}
    REVOKE "{{ $database }}_admin" FROM "{{ $user }}";
    {{- end }}
    {{- if has $user $roles.readonly }}
    GRANT "{{ $database }}_readonly" TO "{{ $user }}";
    {{- else }}
    REVOKE "{{ $database }}_readonly" FROM "{{ $user }}";
    {{- end }}
    {{- end }}
    {{- end }}
    EOT

    echo "== create extensions"
    {{- range $database, $d := .Values.databases }}
    {{- if $d.extensions }}
    psql -v ON_ERROR_STOP=1 --echo-all -d "{{ $database }}" <<\EOT
    {{- range $extension := $d.extensions }}
    CREATE EXTENSION IF NOT EXISTS "{{ include "postgres.extensionName" $extension }}";
    {{- end }}
    EOT
    {{- end }}
    {{- end }}
    {{- if .Values.bootstrap.newServerName }}

    echo "== record the adopted restore"
    psql -v ON_ERROR_STOP=1 --echo-all -v mark="$RESTORE_MARK" <<\EOT
    ALTER DATABASE postgres SET cozystack.adopted_restore = :'mark';
    EOT
    {{- end }}
{{- end }}