feat(hydra-cleaner): add component #61

lgourvenec · 2025-02-27T16:26:11+01:00

lgourvenec commented

2025-02-27 16:26:11 +01:00

Hydra database doesn't remove data from its table "oauth2_flow" and all the tables linked (by a foreign key) to it (code, access, oidc, etc.).

This component remove data older than a specific date, by batch with a limit.

This component rely on the component hydra-cnpg-database.

To test it, import the component

components:
- https://forge.cadoles.com/CadolesKube/sso-kustom//components/hydra-cleaner?ref=f/hydra_cleaner

and modify your NetworkPolicy if you already have one

apiVersion: networking.k8s.io/v1
kind: NetworkPolicy
metadata:
  name: allow-hydra-database
spec:
  podSelector:
    matchLabels:
      cnpg.io/cluster: hydra-postgres
  policyTypes:
  - Ingress
  ingress:
  - from:
    - podSelector:
        matchLabels:
          app.kubernetes.io/name: hydra
    - podSelector:
        matchLabels:
          app.kubernetes.io/name: hydra-cleaner
    ports:
    - port: 5432

You can also modify the configmap:

configMapGenerator:
- name: hydra-cleaner-env
  behavior: merge
  literals:
  - RETENTION_HOURS="0"
  - BATCH_SIZE="100"
  - LIMIT="1000"

Hydra database doesn't remove data from its table "oauth2_flow" and all the tables linked (by a foreign key) to it (code, access, oidc, etc.). This component remove data older than a specific date, by batch with a limit. This component rely on the component hydra-cnpg-database. To test it, import the component ``` components: - https://forge.cadoles.com/CadolesKube/sso-kustom//components/hydra-cleaner?ref=f/hydra_cleaner ``` and modify your NetworkPolicy if you already have one ``` apiVersion: networking.k8s.io/v1 kind: NetworkPolicy metadata: name: allow-hydra-database spec: podSelector: matchLabels: cnpg.io/cluster: hydra-postgres policyTypes: - Ingress ingress: - from: - podSelector: matchLabels: app.kubernetes.io/name: hydra - podSelector: matchLabels: app.kubernetes.io/name: hydra-cleaner ports: - port: 5432 ``` You can also modify the configmap: ``` configMapGenerator: - name: hydra-cleaner-env behavior: merge literals: - RETENTION_HOURS="0" - BATCH_SIZE="100" - LIMIT="1000" ```

lgourvenec added 1 commit 2025-02-27 16:26:12 +01:00

feat(hydra-cleaner): add component 7a09045e82

vcarroy requested changes 2025-03-03 10:47:44 +01:00

components/hydra-cleaner/files/hydra-cleaner.sh Outdated

						
				@ -0,0 +17,4 @@

				# -> delete "cascade" on table "flow" cleans access, code, oidc, pkce and refresh tables.

				DSN="postgresql://${HYDRA_DATABASE_USER}:${HYDRA_DATABASE_PASSWORD}@${HYDRA_DATABASE_SERVICE_NAME}:5432/hydra?sslmode=disable"

vcarroy commented

2025-03-03 10:37:49 +01:00

Port configurable ?

wpetit marked this conversation as resolved

components/hydra-cleaner/files/hydra-cleaner.sh Outdated

						
				@ -0,0 +70,4 @@

				REMAINING_ELMTS="${LIMIT}"

				while [ "${REMAINING_ELMTS}" -gt 0 ]; do

				    OUTPUT=$(psql "${DSN}" <<EOF

				DELETE

vcarroy commented

2025-03-03 10:40:04 +01:00

Ouvrir une transaction et faire des verifs sur le nombre de lignes modifiés par sécurité.

wpetit commented

2025-03-05 17:11:00 +01:00

Vu qu'on est sur une requête DELETE et que les CASCADE sont atomiques en PostgreSQL, je ne pense pas qu'ouvrir une transaction soit nécessaire (ça pourrait même être contre-productif en verrouillant les tables impactées par la/les requêtes).

On pourrait effectivement ajouter un SELECT avec les mêmes critères de filtrage en amont afin d'identifier le nombre d'item potentiellement à supprimer et afficher cette valeur en amont du traitement.

Vu qu'on est sur une requête `DELETE` et que les `CASCADE` sont atomiques en PostgreSQL, je ne pense pas qu'ouvrir une transaction soit nécessaire (ça pourrait même être contre-productif en verrouillant les tables impactées par la/les requêtes). On pourrait effectivement ajouter un `SELECT` avec les mêmes critères de filtrage en amont afin d'identifier le nombre d'item potentiellement à supprimer et afficher cette valeur en amont du traitement.

wpetit marked this conversation as resolved

wpetit added 1 commit 2025-03-05 17:12:49 +01:00

feat: integrate hydra-cleaner in example app 170b24f609

wpetit added 2 commits 2025-03-06 10:08:30 +01:00

feat(hydra-cleaner): configurable dsn and hydra port 72a8685923

fixup! feat: integrate hydra-cleaner in example app 27ef53edd1

wpetit added 1 commit 2025-03-06 10:09:15 +01:00

feat(hydra-cleaner): log deletion candidates/remaining cd9a2f6128

wpetit force-pushed f/hydra_cleaner from cd9a2f6128 to cc14a8d017

2025-03-06 10:12:24 +01:00

Compare

wpetit approved these changes 2025-03-06 10:18:29 +01:00