{{/* Reject a username outside the charset before it is interpolated into the role SQL and the Cluster's managed roles. The charset mirrors k8s IsConfigMapKey (rejecting ".", any name starting with "..", and the metacharacters that would reach the shell). The length cap is 63: the username is 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 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. The account name the API derives from it is a DNS label with the same cap, so the API refuses such a user before the chart sees it. The charset is ASCII-only, so len (bytes) equals characters. */}} {{- range $user, $u := .Values.users }} {{- if or (not (regexMatch "^[-._a-zA-Z0-9]+$" $user)) (eq $user ".") (hasPrefix ".." $user) (gt (len $user) 63) }} {{- 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 the account name derived from it is a Kubernetes object name)" $user) }} {{- end }} {{- end }} {{/* A database name is interpolated into SQL identifiers (CREATE DATABASE "", the "_admin"/"_readonly" role names), into a single-quoted SQL string literal in the superuser DO block ('_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 "_admin" and "_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 \"_admin\" and \"_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 _admin and _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 }} {{/* 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 "== mark users" {{- if .Values.users }} {{- /* CNPG creates, passwords and alters each account from the Cluster's managed roles; a second writer here would fight it. Two things it has no field for stay: the marker membership, which CNPG grants with the default options and this re-grant narrows to a record, leaving the options of an existing membership alone; and createrole_self_grant. Both need the role, which CNPG creates within seconds of the spec but possibly after this hook starts, so the pass waits for it a bounded time; a role still missing then keeps CNPG's defaults until the next run. The createRole flag is recorded here after an install, where the pre-upgrade hook that reads it has no cluster to reach. */}} {{- $declared := list }} {{- range $user, $u := .Values.users }}{{ $declared = append $declared (printf "'%s'" $user) }}{{ end }} roles_from=$SECONDS until [ "$(psql -v ON_ERROR_STOP=1 -t -A -c "SELECT count(*) FROM pg_catalog.pg_roles WHERE rolname IN ({{ join ", " $declared }})")" = "{{ len $declared }}" ]; do if [ "$SECONDS" -ge $((roles_from + 60)) ]; then echo "not every declared role exists yet; marking those that do, the next run marks the rest" >&2 break fi sleep 2 done {{- $grantsCreateRole := false }} psql -v ON_ERROR_STOP=1 --echo-all <<\EOT {{- range $user, $u := .Values.users }} {{- if default false $u.createRole }} {{- $grantsCreateRole = true }} {{- end }} SELECT 'GRANT "cozystack:managed" TO "{{ $user }}"{{ $markOptions }};' WHERE EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = '{{ $user }}')\gexec {{- if default false $u.createRole }} SELECT 'ALTER ROLE "{{ $user }}" SET createrole_self_grant = ''set, inherit'';' WHERE EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = '{{ $user }}')\gexec {{- else }} SELECT 'ALTER ROLE "{{ $user }}" RESET createrole_self_grant;' WHERE EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = '{{ $user }}')\gexec {{- end }} {{- end }} EOT {{- if $grantsCreateRole }} psql -v ON_ERROR_STOP=1 --echo-all -c "ALTER DATABASE postgres SET cozystack.createrole_granted = 'on';" {{- 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 _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 database roles are declared in the Cluster's managed roles as well, but ALTER DATABASE ... OWNER below needs them in this run, so they are still created here, idempotently, and CNPG then finds them as its spec says. The refusal to adopt an unmarked role runs in the pre-upgrade hook. */}} psql -v ON_ERROR_STOP=1 --echo-all <<\EOT {{- range $database, $d := .Values.databases }} 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 "== 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 }}