Etude - Récupérer la liste des comptes expirés avant la réactivation automatique du module actuel du nettoyage de printemps (fin novembre ?)

julie-ri commented 3 months ago

decider de la date à laquelle on fige la liste de compte expirer et à laquelle on desactive les comptes

mcalinghee commented 3 months ago

Des elements de reponse:

Nombre d'utilisateurs par date d'expiration :

ansible -m shell -b -a "sudo -u postgres psql synapse -c \"SELECT to_char(to_timestamp(expiration_ts_ms/1000),'YYYY-MM-DD'), count(*) FROM email_account_validity GROUP BY to_char(to_timestamp(expiration_ts_ms/1000),'YYYY-MM-DD') ORDER BY to_char(to_timestamp(expiration_ts_ms/1000),'YYYY-MM-DD') LIMIT 40;\"" synapse-db01*

synapse-db01.culture01.tchap.gouv.fr | CHANGED | rc=0 >>
  to_char   | count
------------+-------
 2024-06-28 |   284
 2024-06-29 |   343
 2024-06-30 |   374
 2024-07-01 |   349
 2024-07-02 |   206
 2024-11-18 |     1
 2024-11-19 |     2
 2024-11-20 |     1
 2024-11-25 |    46
 2024-11-26 |   102
 2024-11-27 |    85
 2024-11-28 |    18
 2024-11-29 |    22
 2024-11-30 |   103
 2024-12-01 |    10
 2024-12-02 |     4
 2024-12-03 |     2
 2024-12-04 |     5
 2024-12-07 |     3
 2024-12-08 |     1
 2024-12-09 |     6
 2024-12-10 |     3
 2024-12-11 |     4
 2024-12-14 |     1
 2024-12-15 |     4
 2024-12-16 |     2
 2024-12-17 |     4
 2024-12-18 |    15
 2024-12-19 |     6
 2024-12-20 |    16
 2024-12-21 |    52
 2024-12-22 |    39
 2024-12-23 |    17
 2024-12-24 |    12
 2024-12-25 |    14
 2024-12-26 |     5
 2024-12-27 |     1
 2024-12-28 |    16
 2024-12-29 |     7
 2024-12-30 |    11
(40 rows)
synapse-db01.agent01.tchap.gouv.fr | CHANGED | rc=0 >>
  to_char   | count
------------+-------
 2024-06-28 |  1937
 2024-06-29 |  2440
 2024-06-30 |  2627
 2024-07-01 |  2578
 2024-07-02 |  1362
 2024-11-16 |     1
 2024-11-17 |    22
 2024-11-18 |    18
 2024-11-19 |    25
 2024-11-20 |    35
 2024-11-21 |     1
 2024-11-23 |    28
 2024-11-24 |    55
 2024-11-25 |   639
 2024-11-26 |  1122
 2024-11-27 |  1036
 2024-11-28 |   126
 2024-11-29 |   184
 2024-11-30 |  1645
 2024-12-01 |   271
 2024-12-02 |   123
 2024-12-03 |    79
 2024-12-04 |    51
 2024-12-05 |     3
 2024-12-06 |     5
 2024-12-07 |   103
 2024-12-08 |    70
 2024-12-09 |    33
 2024-12-10 |    42
 2024-12-11 |    26
 2024-12-13 |     1
 2024-12-14 |    34
 2024-12-15 |    41
 2024-12-16 |    29
 2024-12-17 |    35
 2024-12-18 |   126
 2024-12-19 |    31
 2024-12-20 |    57
 2024-12-21 |   593
 2024-12-22 |   339
(40 rows)
synapse-db01.dev-durable01.tchap.gouv.fr | CHANGED | rc=0 >>
  to_char   | count
------------+-------
 2024-06-28 |  1289
 2024-06-29 |  1570
 2024-06-30 |  1581
 2024-07-01 |  1619
 2024-07-02 |   884
 2024-11-17 |    26
 2024-11-18 |    24
 2024-11-19 |    53
 2024-11-20 |    23
 2024-11-21 |     1
 2024-11-23 |    19
 2024-11-24 |    28
 2024-11-25 |   456
 2024-11-26 |  1058
 2024-11-27 |   912
 2024-11-28 |   185
 2024-11-29 |   227
 2024-11-30 |  1367
 2024-12-01 |   198
 2024-12-02 |    93
 2024-12-03 |    69
 2024-12-04 |    33
 2024-12-05 |     5
 2024-12-06 |     4
 2024-12-07 |    67
 2024-12-08 |    49
 2024-12-09 |    30
 2024-12-10 |    22
 2024-12-11 |    23
 2024-12-12 |     1
 2024-12-14 |    36
 2024-12-15 |    32
 2024-12-16 |    32
 2024-12-17 |    17
 2024-12-18 |    98
 2024-12-19 |    24
 2024-12-20 |    53
 2024-12-21 |   466
 2024-12-22 |   239
 2024-12-23 |   103
(40 rows)
synapse-db01.agriculture01.tchap.gouv.fr | CHANGED | rc=0 >>
  to_char   | count
------------+-------
 2024-06-28 |   449
 2024-06-29 |   619
 2024-06-30 |   598
 2024-07-01 |   613
 2024-07-02 |   302
 2024-11-17 |    16
 2024-11-18 |     9
 2024-11-19 |     9
 2024-11-20 |     4
 2024-11-21 |     1
 2024-11-23 |     9
 2024-11-24 |     7
 2024-11-25 |   172
 2024-11-26 |   411
 2024-11-27 |   374
 2024-11-28 |    84
 2024-11-29 |   101
 2024-11-30 |   484
 2024-12-01 |    88
 2024-12-02 |    47
 2024-12-03 |    21
 2024-12-04 |    13
 2024-12-05 |     1
 2024-12-06 |     2
 2024-12-07 |    21
 2024-12-08 |    17
 2024-12-09 |     9
 2024-12-10 |    11
 2024-12-11 |     5
 2024-12-13 |     1
 2024-12-14 |    14
 2024-12-15 |     8
 2024-12-16 |    13
 2024-12-17 |     6
 2024-12-18 |    35
 2024-12-19 |    13
 2024-12-20 |    24
 2024-12-21 |   213
 2024-12-22 |    90
 2024-12-23 |    43
(40 rows)
synapse-db01.collectivites01.tchap.gouv.fr | CHANGED | rc=0 >>
  to_char   | count
------------+-------
 2024-06-28 |  1221
 2024-06-29 |  1549
 2024-06-30 |  1627
 2024-07-01 |  1591
 2024-07-02 |   890
 2024-11-17 |    29
 2024-11-18 |    37
 2024-11-19 |    25
 2024-11-20 |    18
 2024-11-21 |     3
 2024-11-22 |     2
 2024-11-23 |    43
 2024-11-24 |    34
 2024-11-25 |   542
 2024-11-26 |   958
 2024-11-27 |   877
 2024-11-28 |   369
 2024-11-29 |   362
 2024-11-30 |   927
 2024-12-01 |   183
 2024-12-02 |    91
 2024-12-03 |    98
 2024-12-04 |    73
 2024-12-05 |    14
 2024-12-06 |    12
 2024-12-07 |    72
 2024-12-08 |    67
 2024-12-09 |    42
 2024-12-10 |    45
 2024-12-11 |    50
 2024-12-12 |    17
 2024-12-13 |     9
 2024-12-14 |    49
 2024-12-15 |    61
 2024-12-16 |    52
 2024-12-17 |    73
 2024-12-18 |   100
 2024-12-19 |    69
 2024-12-20 |    92
 2024-12-21 |   419
(40 rows)
synapse-db01.elysee01.tchap.gouv.fr | CHANGED | rc=0 >>
  to_char   | count
------------+-------
 2024-06-28 |     8
 2024-06-29 |    11
 2024-06-30 |    13
 2024-07-01 |    10
 2024-07-02 |     5
 2024-11-18 |     1
 2024-11-20 |     1
 2024-11-25 |     3
 2024-11-26 |     6
 2024-11-27 |     8
 2024-11-29 |     1
 2024-11-30 |     5
 2024-12-02 |     3
 2024-12-18 |     1
 2024-12-20 |     2
 2024-12-21 |     2
 2024-12-28 |     2
 2024-12-29 |     4
 2024-12-30 |     2
 2025-01-03 |     1
 2025-01-05 |     1
 2025-01-11 |     1
 2025-01-18 |     1
 2025-01-20 |     2
 2025-01-22 |     2
 2025-02-05 |     1
 2025-02-09 |     1
 2025-02-22 |     1
 2025-02-26 |     1
 2025-03-01 |     1
 2025-03-05 |     1
(31 rows)
synapse-db01.diplomatie01.tchap.gouv.fr | CHANGED | rc=0 >>
  to_char   | count
------------+-------
 2024-06-28 |  1040
 2024-06-29 |  1338
 2024-06-30 |  1417
 2024-07-01 |  1446
 2024-07-02 |   767
 2024-11-17 |     5
 2024-11-18 |     6
 2024-11-19 |     5
 2024-11-20 |     4
 2024-11-22 |     3
 2024-11-23 |     6
 2024-11-24 |     8
 2024-11-25 |   375
 2024-11-26 |   655
 2024-11-27 |   539
 2024-11-28 |   125
 2024-11-29 |   172
 2024-11-30 |   793
 2024-12-01 |   138
 2024-12-02 |    67
 2024-12-03 |    52
 2024-12-04 |    24
 2024-12-05 |     2
 2024-12-06 |     5
 2024-12-07 |    32
 2024-12-08 |    19
 2024-12-09 |    22
 2024-12-10 |    12
 2024-12-11 |     9
 2024-12-12 |     2
 2024-12-13 |     5
 2024-12-14 |    16
 2024-12-15 |    17
 2024-12-16 |    11
 2024-12-17 |     9
 2024-12-18 |    56
 2024-12-19 |    36
 2024-12-20 |    58
 2024-12-21 |   336
 2024-12-22 |   175
(40 rows)
synapse-db01.education01.tchap.gouv.fr | CHANGED | rc=0 >>
  to_char   | count
------------+-------
 2024-06-28 |  6887
 2024-06-29 |  8627
 2024-06-30 |  8891
 2024-07-01 |  9126
 2024-07-02 |  4667
 2024-11-16 |    16
 2024-11-17 |    75
 2024-11-18 |    65
 2024-11-19 |    98
 2024-11-20 |    76
 2024-11-21 |     9
 2024-11-22 |    20
 2024-11-23 |    95
 2024-11-24 |    81
 2024-11-25 |  1954
 2024-11-26 |  3997
 2024-11-27 |  3190
 2024-11-28 |  1921
 2024-11-29 |  2318
 2024-11-30 |  2934
 2024-12-01 |   621
 2024-12-02 |   400
 2024-12-03 |   282
 2024-12-04 |   197
 2024-12-05 |    45
 2024-12-06 |    52
 2024-12-07 |   186
 2024-12-08 |   157
 2024-12-09 |   139
 2024-12-10 |   181
 2024-12-11 |   161
 2024-12-12 |    42
 2024-12-13 |    54
 2024-12-14 |   214
 2024-12-15 |   154
 2024-12-16 |   145
 2024-12-17 |   120
 2024-12-18 |   395
 2024-12-19 |   428
 2024-12-20 |   685
(40 rows)
synapse-db01.dinum01.tchap.gouv.fr | CHANGED | rc=0 >>
  to_char   | count
------------+-------
 2024-06-11 |    32
 2024-06-12 |   142
 2024-06-13 |   115
 2024-06-14 |   135
 2024-06-15 |   101
 2024-11-03 |     2
 2024-11-04 |     1
 2024-11-08 |     3
 2024-11-09 |   103
 2024-11-10 |   119
 2024-11-11 |   101
 2024-11-12 |   110
 2024-11-13 |    17
 2024-11-14 |     3
 2024-11-15 |     1
 2024-11-16 |     6
 2024-11-17 |    14
 2024-11-18 |     6
 2024-11-19 |     9
 2024-11-20 |     2
 2024-11-21 |     1
 2024-11-23 |     9
 2024-11-26 |    19
 2024-11-27 |     3
 2024-11-28 |     1
 2024-11-30 |    10
 2024-12-01 |     5
 2024-12-02 |    12
 2024-12-03 |    12
 2024-12-04 |    18
 2024-12-05 |     1
 2024-12-07 |    22
 2024-12-08 |     7
 2024-12-09 |    23
 2024-12-10 |    14
 2024-12-11 |    11
 2024-12-12 |     1
 2024-12-13 |     4
 2024-12-14 |    16
 2024-12-15 |     6
(40 rows)
synapse-db01.externe01.tchap.gouv.fr | CHANGED | rc=0 >>
  to_char   | count
------------+-------
 2024-06-28 |  2735
 2024-06-29 |  3654
 2024-06-30 |  3643
 2024-07-01 |  3649
 2024-07-02 |  1954
 2024-11-16 |    10
 2024-11-17 |    31
 2024-11-18 |    23
 2024-11-19 |    26
 2024-11-20 |    37
 2024-11-21 |     3
 2024-11-22 |     1
 2024-11-23 |    74
 2024-11-24 |    40
 2024-11-25 |   897
 2024-11-26 |  1479
 2024-11-27 |  1331
 2024-11-28 |   920
 2024-11-29 |   785
 2024-11-30 |   775
 2024-12-01 |   218
 2024-12-02 |   104
 2024-12-03 |    88
 2024-12-04 |    51
 2024-12-05 |    12
 2024-12-06 |    18
 2024-12-07 |    59
 2024-12-08 |    56
 2024-12-09 |    47
 2024-12-10 |    51
 2024-12-11 |    33
 2024-12-12 |    13
 2024-12-13 |    21
 2024-12-14 |    33
 2024-12-15 |    40
 2024-12-16 |    27
 2024-12-17 |    29
 2024-12-18 |   169
 2024-12-19 |   234
 2024-12-20 |   329
(40 rows)
synapse-db01.finances01.tchap.gouv.fr | FAILED | rc=1 >>
ERROR:  relation "email_account_validity" does not exist
LINE 1: ...piration_ts_ms/1000),'YYYY-MM-DD'), count(*) FROM email_acco...
                                                             ^non-zero return code
synapse-db01.pm01.tchap.gouv.fr | CHANGED | rc=0 >>
  to_char   | count
------------+-------
 2024-06-28 |   259
 2024-06-29 |   349
 2024-06-30 |   349
 2024-07-01 |   376
 2024-07-02 |   200
 2024-11-17 |     2
 2024-11-18 |     1
 2024-11-19 |     1
 2024-11-22 |     1
 2024-11-23 |     1
 2024-11-25 |    57
 2024-11-26 |   129
 2024-11-27 |   109
 2024-11-28 |    33
 2024-11-29 |    35
 2024-11-30 |   151
 2024-12-01 |    32
 2024-12-02 |    19
 2024-12-03 |     8
 2024-12-04 |    12
 2024-12-05 |     1
 2024-12-07 |    12
 2024-12-08 |     5
 2024-12-09 |     7
 2024-12-10 |     3
 2024-12-11 |     3
 2024-12-14 |     2
 2024-12-15 |     8
 2024-12-16 |     6
 2024-12-17 |     6
 2024-12-18 |     7
 2024-12-19 |     4
 2024-12-20 |     4
 2024-12-21 |    59
 2024-12-22 |    31
 2024-12-23 |    12
 2024-12-24 |    14
 2024-12-25 |     7
 2024-12-26 |     3
 2024-12-27 |     3
(40 rows)
synapse-db01.justice01.tchap.gouv.fr | FAILED | rc=1 >>
ERROR:  relation "email_account_validity" does not exist
LINE 1: ...piration_ts_ms/1000),'YYYY-MM-DD'), count(*) FROM email_acco...
                                                             ^non-zero return code
synapse-db01.intradef01.tchap.gouv.fr | CHANGED | rc=0 >>
  to_char   | count
------------+-------
 2024-06-28 | 15417
 2024-06-29 | 20153
 2024-06-30 | 20303
 2024-07-01 | 20166
 2024-07-02 | 10600
 2024-11-16 |     6
 2024-11-17 |    29
(7 rows)
synapse-db01.social01.tchap.gouv.fr | CHANGED | rc=0 >>
  to_char   | count
------------+-------
 2024-06-28 |  2423
 2024-06-29 |  3084
 2024-06-30 |  3214
 2024-07-01 |  3111
 2024-07-02 |  1655
 2024-11-16 |     4
 2024-11-17 |    28
 2024-11-18 |    46
 2024-11-19 |    40
 2024-11-20 |    35
 2024-11-21 |     3
 2024-11-22 |     3
 2024-11-23 |    28
 2024-11-24 |    46
 2024-11-25 |   674
 2024-11-26 |  1445
 2024-11-27 |  1199
 2024-11-28 |   240
 2024-11-29 |   259
 2024-11-30 |  1923
 2024-12-01 |   298
 2024-12-02 |   152
 2024-12-03 |    86
 2024-12-04 |    77
 2024-12-05 |    10
 2024-12-06 |    11
 2024-12-07 |   116
 2024-12-08 |    69
 2024-12-09 |    62
 2024-12-10 |    73
 2024-12-11 |    80
 2024-12-12 |    13
 2024-12-13 |     8
 2024-12-14 |    91
 2024-12-15 |    95
 2024-12-16 |    73
 2024-12-17 |    56
 2024-12-18 |   174
 2024-12-19 |    42
 2024-12-20 |    90
(40 rows)
synapse-db01.interieur01.tchap.gouv.fr | CHANGED | rc=0 >>
  to_char   | count
------------+-------
 2024-06-28 |  4145
 2024-06-29 |  5488
 2024-06-30 |  5638
 2024-07-01 |  5654
 2024-07-02 |  3009
 2024-11-16 |    14
 2024-11-17 |    50
 2024-11-18 |    83
 2024-11-19 |    51
 2024-11-20 |    51
 2024-11-21 |    11
 2024-11-22 |     6
 2024-11-23 |    77
 2024-11-24 |    77
 2024-11-25 |  1440
 2024-11-26 |  2949
 2024-11-27 |  2585
 2024-11-28 |   724
 2024-11-29 |   811
 2024-11-30 |  4228
 2024-12-01 |  1055
 2024-12-02 |   615
 2024-12-03 |   372
 2024-12-04 |   308
 2024-12-05 |    96
 2024-12-06 |    71
 2024-12-07 |   407
 2024-12-08 |   275
 2024-12-09 |   222
 2024-12-10 |   204
 2024-12-11 |   221
 2024-12-12 |    91
 2024-12-13 |    63
 2024-12-14 |   250
 2024-12-15 |   206
 2024-12-16 |   202
 2024-12-17 |   190
 2024-12-18 |   409
 2024-12-19 |   246
 2024-12-20 |   354
(40 rows)
synapse-db01.ssi01.tchap.gouv.fr | CHANGED | rc=0 >>
  to_char   | count
------------+-------
 2024-06-28 |   135
 2024-06-29 |   163
 2024-06-30 |   159
 2024-07-01 |   164
 2024-07-02 |    85
 2024-11-17 |     1
 2024-11-20 |     2
 2024-11-25 |    47
 2024-11-26 |   134
 2024-11-27 |   133
 2024-11-28 |    26
 2024-11-29 |    21
 2024-11-30 |   209
 2024-12-01 |    20
 2024-12-02 |    10
 2024-12-03 |     2
 2024-12-04 |     4
 2024-12-07 |     6
 2024-12-08 |     5
 2024-12-09 |     3
 2024-12-10 |     1
 2024-12-11 |     1
 2024-12-14 |     3
 2024-12-15 |     4
 2024-12-16 |     2
 2024-12-17 |     2
 2024-12-18 |    14
 2024-12-19 |     3
 2024-12-20 |     3
 2024-12-21 |    46
 2024-12-22 |    32
 2024-12-23 |    21
 2024-12-24 |    14
 2024-12-25 |    22
 2024-12-26 |     4
 2024-12-27 |     2
 2024-12-28 |    32
 2024-12-29 |    23
 2024-12-30 |    11
 2024-12-31 |     4
(40 rows)

Interpretation :

La date des prochaines expirations commencent a partir du 2024-11-03 sur dinum
cela signifie que les premiers mails de vie partiront a partir 2024-10-04

Proposition :

d'un point de vue operationnel, idealement il faudrait desactiver les comptes avant 2024-10-04
afin de ne pas envoyer un mail de vie aux comptes existants, il faudra appliquer en prod :
- tchapgouv/tchap-backend#54
- quand : juste apres la desactivation des comptes et surtout avant le 2024-10-04

julie-ri commented 3 months ago

c'est très relou car c'est avant notre point avec nos correspondants @Delphgateau

mcalinghee commented 3 months ago

Point avec Delphine - Proposition 2 :

avec une requete SQL, on peut pousser la date d'expiration a un an plus tard pour tous ceux qui ont clique sur le mail de vie (autrement dit compte qui ont une date d'expiration apres le 02/07/24)
attention : un bug a ete detecte sur la mise a jour de la date d'expiration par requete SQL (Julie continuait de recevoir des mails de vie apres mise a jour en BDD)
- ce bug doit etre investigue et resolu avant la mise en place d'une telle solution
Ticket : https://github.com/tchapgouv/tchap-infra/issues/2948

mcalinghee commented 3 months ago

Nous fermons ce ticket pour privilegier celui-ci : https://github.com/tchapgouv/tchap-infra/issues/2948

tchapgouv / tchap-product

Etude - Récupérer la liste des comptes expirés avant la réactivation automatique du module actuel du nettoyage de printemps (fin novembre ?) #367