mirror of
https://github.com/cozystack/cozystack.git
synced 2026-11-03 18:47:04 +09:00
Timofei Larkin
500c39ad7d
The init job may start before CloudNativePG has created the roles from the spec, and the marking pass skipped any role that did not exist yet. Such a role then kept the marker with CNPG's default options, SET and INHERIT on, and a createRole user had no createrole_self_grant, until the next upgrade ran the job again. The pass now waits up to a minute for every declared role. Assisted-by: LLM Signed-off-by: Timofei Larkin <lllamnyp@gmail.com>
427 lines
27 KiB
YAML
427 lines
27 KiB
YAML
{{/*
|
|
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 "<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 }}
|
|
|
|
{{/*
|
|
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 <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 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 }}
|