1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
| CREATE OR REPLACE FUNCTION api_projet.update_api_token(
id_api_key_param integer)
RETURNS void
LANGUAGE 'plpgsql'
COST 100
VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE
header TEXT := '{"alg":"HS256","typ":"JWT"}';
payload_json TEXT;
cle_secrete TEXT;
exp BIGINT;
header_b64 TEXT;
payload_b64 TEXT;
signature_b64 TEXT;
token_local TEXT;
description TEXT;
communes TEXT[];
domaines TEXT[];
signature TEXT;
restrictions TEXT[];
id_api integer;
id_client integer;
active BOOLEAN;
id_role integer;
id_offre integer;
createdat TIMESTAMP;
createdby TEXT;
duree INT;
BEGIN
-- Récupérer la clé secrète
SELECT cs.valeur INTO cle_secrete
FROM api_projet.cle_secrete cs
LIMIT 1;
IF cle_secrete IS NULL THEN
RAISE EXCEPTION 'Clé secrète manquante dans la table cle_secrete';
END IF;
-- Récupérer les infos de la clé API
SELECT
ak.id_api,
ak.id_client,
ak.active
INTO
id_api,
id_client,
active
FROM api_projet.api_key ak
WHERE ak.id_api_key = id_api_key_param;
IF id_api IS NULL THEN
RAISE EXCEPTION 'Aucune API trouvée pour id_api_key = %', id_api_key_param;
END IF;
-- Expiration en secondes depuis l'époque
exp := extract(epoch FROM now() + (duree || ' days')::interval);
-- Récupérer les entités autorisées
SELECT array_agg(ca.id_entite) INTO communes
FROM api_projet.communes_autorisees ca
WHERE ca.id_api_key = id_api_key_param;
SELECT array_agg(dm.nom_domaine) INTO domaines
FROM api_projet.domaines_autorises dm
WHERE dm.id_api_key = id_api_key_param;
SELECT array_agg(rp.nom_restriction) INTO restrictions
FROM api_projet.restrictions_perso rp
WHERE rp.id_api_key = id_api_key_param;
-- Génération du payload JSON compact
payload_json := jsonb_build_object(
'id', id_api_key_param,
'id_api', id_api,
'id_client', id_client,
'active', active
'delivered_on', createdat,
'delivered_by', createdby,
'iat', extract(epoch FROM now())::INT,
'exp', exp,
'communes', communes,
'domaines', domaines,
'restrictions', restrictions
)::text;
-- Encodage Base64URL
header_b64 := replace(replace(encode(convert_to(header, 'utf8'), 'base64'), '+', '-'), '/', '_');
header_b64 := rtrim(header_b64, '=');
payload_b64 := replace(replace(encode(convert_to(payload_json, 'utf8'), 'base64'), '+', '-'), '/', '_');
payload_b64 := rtrim(payload_b64, '=');
-- Signature HMAC SHA256
signature_b64 := encode(hmac(header_b64 || '.' || payload_b64, cle_secrete, 'sha256'), 'base64');
signature := replace(replace(signature_b64, '+', '-'), '/', '_');
signature := rtrim(signature, '=');
-- Token final JWT
token_local := header_b64 || '.' || payload_b64 || '.' || signature;
IF token_local IS NULL OR token_local = '' THEN
RAISE EXCEPTION 'Le token JWT généré est invalide ou vide';
END IF;
-- DEBUG : Affichage temporaire
RAISE NOTICE 'Header: %', header_b64;
RAISE NOTICE 'Payload: %', payload_b64;
RAISE NOTICE 'Signature: %', signature;
RAISE NOTICE 'Token JWT: %', token_local;
-- Mise à jour du token si différent
UPDATE api_projet.api_key ak
SET token = token_local
WHERE ak.id_api_key = id_api_key_param
AND (ak.token IS DISTINCT FROM token_local);
END;
$BODY$; |
