Konubinix' opinionated web of thoughts

Postgresql

Fleeting

inheritance

Other types of constraints (unique, primary key, and foreign key constraints) are not inherited.

https://www.postgresql.org/docs/current/ddl-inherit.html#DDL-INHERIT-CAVEATS

with bitnami

bitnami ne se configure pas bien

bitnami ne se configure pas bien sur k8s avec keycloak

Sur un cluster tout frais, le log de postgre montre

postgresql 13:15:22.82
postgresql 13:15:22.82 Welcome to the Bitnami postgresql container
postgresql 13:15:22.82 Subscribe to project updates by watching https://github.com/bitnami/bitnami-docker-postgresql
postgresql 13:15:22.82 Submit issues and feature requests at https://github.com/bitnami/bitnami-docker-postgresql/issues
postgresql 13:15:22.83
postgresql 13:15:22.84 INFO  ==> ** Starting PostgreSQL setup **
postgresql 13:15:22.88 INFO  ==> Validating settings in POSTGRESQL_* env vars..
postgresql 13:15:22.89 INFO  ==> Loading custom pre-init scripts...
postgresql 13:15:22.89 INFO  ==> Initializing PostgreSQL database...
postgresql 13:15:22.92 INFO  ==> pg_hba.conf file not detected. Generating it...
postgresql 13:15:22.92 INFO  ==> Generating local authentication configuration
postgresql 13:15:24.53 INFO  ==> Starting PostgreSQL in background...
postgresql 13:15:24.74 INFO  ==> Changing password of postgres
postgresql 13:15:24.77 INFO  ==> Creating user keycloak
postgresql 13:15:24.79 INFO  ==> Granting access to "keycloak" to the database "keycloak"
postgresql 13:15:24.82 INFO  ==> Setting ownership for the 'public' schema database "keycloak" to "keycloak"
postgresql 13:15:24.85 INFO  ==> Configuring replication parameters
postgresql 13:15:24.88 INFO  ==> Configuring synchronous_replication
postgresql 13:15:24.89 INFO  ==> Configuring fsync
postgresql 13:15:24.93 INFO  ==> Loading custom scripts...
postgresql 13:15:24.94 INFO  ==> Enabling remote connections
postgresql 13:15:24.95 INFO  ==> Stopping PostgreSQL...
waiting for server to shut down.... done
server stopped
postgresql 13:15:25.06 INFO  ==> ** PostgreSQL setup finished! **

postgresql 13:15:25.09 INFO  ==> ** Starting PostgreSQL **
2022-05-09 13:15:25.116 GMT [1] LOG:  pgaudit extension initialized

Sur un autre cluster, le log de PostgreSQL donne

postgresql 12:23:01.42
postgresql 12:23:01.42 Welcome to the Bitnami postgresql container
postgresql 12:23:01.42 Subscribe to project updates by watching https://github.com/bitnami/bitnami-docker-postgresql
postgresql 12:23:01.42 Submit issues and feature requests at https://github.com/bitnami/bitnami-docker-postgresql/issues
postgresql 12:23:01.43
postgresql 12:23:01.45 INFO  ==> ** Starting PostgreSQL setup **
postgresql 12:23:01.48 INFO  ==> Validating settings in POSTGRESQL_* env vars..
postgresql 12:23:01.49 INFO  ==> Loading custom pre-init scripts...
postgresql 12:23:01.50 INFO  ==> Initializing PostgreSQL database...
postgresql 12:23:01.53 INFO  ==> pg_hba.conf file not detected. Generating it...
postgresql 12:23:01.53 INFO  ==> Generating local authentication configuration
postgresql 12:23:01.54 INFO  ==> Deploying PostgreSQL with persisted data...
postgresql 12:23:01.56 INFO  ==> Configuring replication parameters
postgresql 12:23:01.59 INFO  ==> Configuring fsync
postgresql 12:23:01.60 INFO  ==> Configuring synchronous_replication
postgresql 12:23:01.64 INFO  ==> Loading custom scripts...
postgresql 12:23:01.64 INFO  ==> Enabling remote connections
postgresql 12:23:01.66 INFO  ==> ** PostgreSQL setup finished! **

postgresql 12:23:01.69 INFO  ==> ** Starting PostgreSQL **
2022-05-09 12:23:01.729 GMT [1] LOG:  pgaudit extension initialized
2022-05-09 12:23:01.735 GMT [1] LOG:  starting PostgreSQL 14.2 on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
2022-05-09 12:23:01.737 GMT [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2022-05-09 12:23:01.737 GMT [1] LOG:  listening on IPv6 address "::", port 5432
2022-05-09 12:23:01.743 GMT [1] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2022-05-09 12:23:01.751 GMT [92] LOG:  database system was shut down at 2022-05-05 12:43:18 GMT
2022-05-09 12:23:01.764 GMT [1] LOG:  database system is ready to accept connections
2022-05-09 12:23:18.228 GMT [107] FATAL:  password authentication failed for user "keycloak"
2022-05-09 12:23:18.228 GMT [107] DETAIL:  Connection matched pg_hba.conf line 1: "host     all             all             0.0.0.0/0               md5"
2022-05-09 12:23:18.388 GMT [109] FATAL:  password authentication failed for user "keycloak"
2022-05-09 12:23:18.388 GMT [109] DETAIL:  Connection matched pg_hba.conf line 1: "host     all             all             0.0.0.0/0               md5"
2022-05-09 12:23:18.863 GMT [111] FATAL:  password authentication failed for user "keycloak"
2022-05-09 12:23:18.863 GMT [111] DETAIL:  Connection matched pg_hba.conf line 1: "host     all             all             0.0.0.0/0               md5"
2022-05-09 12:23:19.156 GMT [112] FATAL:  password authentication failed for user "keycloak"

Il semble qu’il ne se configure pas.

La différence réside sur

postgresql 13:15:24.53 INFO  ==> Starting PostgreSQL in background...
postgresql 13:15:24.74 INFO  ==> Changing password of postgres
postgresql 13:15:24.77 INFO  ==> Creating user keycloak
postgresql 13:15:24.79 INFO  ==> Granting access to "keycloak" to the database "keycloak"
postgresql 13:15:24.82 INFO  ==> Setting ownership for the 'public' schema database "keycloak" to "keycloak"
...
postgresql 13:15:24.95 INFO  ==> Stopping PostgreSQL...
waiting for server to shut down.... done
server stopped

contre

postgresql 12:23:01.54 INFO  ==> Deploying PostgreSQL with persisted data...

On remarque que bitnami utilise un volume pour stocker les données PostgreSQL dans bitnami/postgresql alors que les données de configuration sont dans /opt/bitnami. Aussi,

Je pense que le point d’intérêt est dans cette zone.

if ! is_dir_empty "$POSTGRESQL_DATA_DIR"; then
    info "Deploying PostgreSQL with persisted data..."
    export POSTGRESQL_FIRST_BOOT="no"
    is_boolean_yes "$create_pghba_file" && postgresql_restrict_pghba
    is_boolean_yes "$create_conf_file" && postgresql_configure_replication_parameters
    is_boolean_yes "$create_conf_file" && postgresql_configure_fsync
    is_boolean_yes "$create_conf_file" && is_boolean_yes "$POSTGRESQL_ENABLE_TLS" && postgresql_configure_tls
    [[ "$POSTGRESQL_REPLICATION_MODE" = "master" ]] && [[ -n "$POSTGRESQL_REPLICATION_USER" ]] && is_boolean_yes "$create_pghba_file" && postgresql_add_replication_to_pghba
    [[ "$POSTGRESQL_REPLICATION_MODE" = "master" ]] && is_boolean_yes "$create_pghba_file" && postgresql_configure_synchronous_replication
    [[ "$POSTGRESQL_REPLICATION_MODE" = "slave" ]] && postgresql_configure_recovery
else
    if [[ "$POSTGRESQL_REPLICATION_MODE" = "master" ]]; then
        postgresql_master_init_db
        postgresql_start_bg "false"
        [[ -n "${POSTGRESQL_DATABASE}" ]] && [[ "$POSTGRESQL_DATABASE" != "postgres" ]] && postgresql_create_custom_database
        if [[ "$POSTGRESQL_USERNAME" = "postgres" ]]; then
            postgresql_alter_postgres_user "$POSTGRESQL_PASSWORD"
        else
            if [[ -n "$POSTGRESQL_POSTGRES_PASSWORD" ]]; then
                postgresql_alter_postgres_user "$POSTGRESQL_POSTGRES_PASSWORD"
            fi
            postgresql_create_admin_user
        fi
        is_boolean_yes "$create_pghba_file" && postgresql_restrict_pghba
        [[ -n "$POSTGRESQL_REPLICATION_USER" ]] && postgresql_create_replication_user
        is_boolean_yes "$create_conf_file" && postgresql_configure_replication_parameters
        is_boolean_yes "$create_pghba_file" && postgresql_configure_synchronous_replication
        is_boolean_yes "$create_conf_file" && postgresql_configure_fsync
        is_boolean_yes "$create_conf_file" && is_boolean_yes "$POSTGRESQL_ENABLE_TLS" && postgresql_configure_tls
        [[ -n "$POSTGRESQL_REPLICATION_USER" ]] && is_boolean_yes "$create_pghba_file" && postgresql_add_replication_to_pghba
    else
        postgresql_slave_init_db
        is_boolean_yes "$create_pghba_file" && postgresql_restrict_pghba
        is_boolean_yes "$create_conf_file" && postgresql_configure_replication_parameters
        is_boolean_yes "$create_conf_file" && postgresql_configure_fsync
        is_boolean_yes "$create_conf_file" && is_boolean_yes "$POSTGRESQL_ENABLE_TLS" && postgresql_configure_tls
        postgresql_configure_recovery
    fi
fi

Si les fichiers sont déjà là, on ne va pas appliquer les configurations issues des variables d’environnement $POSTGRESQL_PASSWORD et $POSTGRESQL_USERNAME.

Effectivement, sur le cluster à soucis

  psql -h localhost -p 5432 -U keycloak
Password for user keycloak:
psql: error: connection to server at "localhost" (127.0.0.1), port 5432 failed: FATAL:  password authentication failed for user "keycloak"

Sur le cluster OK

psql -h localhost -p 5432 -U keycloak
Password for user keycloak:
psql (14.2)
Type "help" for help.

keycloak=>

Il semble en fait que le mot de passe de keycloak ne soit pas le bon.

Résolution du problème

  • Nous posons le mot de passe de l’utilisateur dans un secret k8s.
  • Or le cluster s’est mis à ne plus fonctionner après un helm uninstall && helm install
  • helm uninstall supprime le secret mais pas le pvc
  • le mot de passe est généré dans le secret de façon aléatoire
  • quand on réinstalle avec helm, la base de donnée continue à utiliser l’ancien mot de passe, alors qu’un nouveau secret, avec un nouveau mot de passe est créé

comment bitnami génère le mot de passe

comment bitnami

data:
  {{- if .Values.auth.enablePostgresUser }}
  postgres-password: {{ include "common.secrets.passwords.manage" (dict "secret" (include "common.names.fullname" .) "key" "postgres-password" "providedValues" (list "global.postgresql.auth.postgresPassword" "auth.postgresPassword") "context" $) }}
  {{- end }}
  {{- if not (empty (include "postgresql.username" .)) }}
  password: {{ include "common.secrets.passwords.manage " (dict "secret" (include "common.names.fullname" .) "key" "password" "providedValues" (list "global.postgresql.auth.password" "auth.password") "context" $) }}
  {{- end }}
  {{- if eq .Values.architecture "replication" }}
  replication-password: {{ include "common.secrets.passwords.manage" (dict "secret" (include "common.names.fullname" .) "key" "replication-password" "providedValues" (list "auth.replicationPassword") "context" $) }}
  {{- end }}
  # We don't auto-generate LDAP password when it's not provided as we do for other passwords
  {{- if and .Values.ldap.enabled .Values.ldap.bind_password }}
  ldap-password: {{ .Values.ldap.bind_password | b64enc | quote }}
  {{- end }}

puis, dans la dépendance common de postgre de bitnami

{{- define "common.secrets.passwords.manage" -}}

{{- $password := "" }}
{{- $subchart := "" }}
{{- $chartName := default "" .chartName }}
{{- $passwordLength := default 10 .length }}
{{- $providedPasswordKey := include "common.utils.getKeyFromList" (dict "keys" .providedValues "context" $.context) }}
{{- $providedPasswordValue := include "common.utils.getValueFromKey" (dict "key" $providedPasswordKey "context" $.context) }}
{{- $secretData := (lookup "v1" "Secret" $.context.Release.Namespace .secret).data }}
{{- if $secretData }}
  {{- if hasKey $secretData .key }}
    {{- $password = index $secretData .key }}
  {{- else }}
    {{- printf "\nPASSWORDS ERROR: The secret \"%s\" does not contain the key \"%s\"\n" .secret .key | fail -}}
  {{- end -}}
{{- else if $providedPasswordValue }}
  {{- $password = $providedPasswordValue | toString | b64enc | quote }}
{{- else }}

  {{- if .context.Values.enabled }}
    {{- $subchart = $chartName }}
  {{- end -}}

  {{- $requiredPassword := dict "valueKey" $providedPasswordKey "secret" .secret "field" .key "subchart" $subchart "context" $.context -}}
  {{- $requiredPasswordError := include "common.validations.values.single.empty" $requiredPassword -}}
  {{- $passwordValidationErrors := list $requiredPasswordError -}}
  {{- include "common.errors.upgrade.passwords.empty" (dict "validationErrors" $passwordValidationErrors "context" $.context) -}}

  {{- if .strong }}
    {{- $subStr := list (lower (randAlpha 1)) (randNumeric 1) (upper (randAlpha 1)) | join "_" }}
    {{- $password = randAscii $passwordLength }}
    {{- $password = regexReplaceAllLiteral "\\W" $password "@" | substr 5 $passwordLength }}
    {{- $password = printf "%s%s" $subStr $password | toString | shuffle | b64enc | quote }}
  {{- else }}
    {{- $password = randAlphaNum $passwordLength | b64enc | quote }}
  {{- end }}
{{- end -}}
{{- printf "%s" $password -}}
{{- end -}}

pg_dump

deduplicate rows

Provided there is no already existing id columns.

alter table mytable add column id serial

Then, partition other some commun attribute and find the row number.

delete from mytable where id in (
        select id from (
               select id, cid, row_number() over(partition by cid) from mytable -- group by the cid and show the row number
        ) as withrows where row_number > 1 -- find only the ones with more than 1 match
) -- remove them

Then, remove the temporary id column.

alter table mytable drop column id

Notes pointant ici