Security, RBAC and Audit

The SIM Database implements a role-based access control (RBAC) model in the admin schema, stores refresh and password-reset tokens securely, and records every business operation in the audit schema.

RBAC model

Core tables

Table	Purpose	Key columns
admin.role	Role catalogue	id_role, code, libelle
admin.permission	Atomic permission catalogue	id_permission, code, libelle
admin.role_permission	Many-to-many assignments	id_role, id_permission
admin.utilisateur	User accounts	id_utilisateur, login, mot_de_passe, id_role, id_activite_scope, id_employe, id_client, email, actif

admin.utilisateur

A user is a single account. It is linked to:

• one admin.role (mandatory)
• optionally one rh.employe (employee account)
• optionally one client.client (resident/client portal account)
• optionally one finances.activite (cash-register/activity scope)
• optionally one finances.caisse (cash drawer assignment)

Indexes enforce uniqueness:

• ux_utilisateur_id_employe — one account per employee
• ux_utilisateur_id_client — one account per client (resident portal)
• ux_utilisateur_email — optional email is unique when set

Permission naming

Permissions follow the pattern:

<MODULE>.<ACTION>

• Actions: VOIR, CREER, MODIFIER, SUPPRIMER
• Modules: CORE, CLIENT, RESIDENCE, MARCHANDISE, PRESSING, RESTAURANT, SALLE_FETE, FACTURATION, FINANCES, RH, ADMIN, AUDIT

Special permissions extend CRUD with lifecycle actions:

Permission	Purpose
RESIDENT.VOIR	Resident portal read-only access
RAPPORTS.VOIR	Activity-scoped reporting
DEPENSE.*	Expense management
ABONNEMENT.*	Subscription category catalog
PRESSING.TRAITER / MARQUER_PRET / RETIRER / ANNULER / VALIDER	Pressing lifecycle
RESTAURANT.VALIDER	Validate restaurant order
RESIDENCE.VALIDER / ENCAISSER	Validate contract / receive rent
FACTURATION.VALIDER	Validate invoice
RH.VALIDER	Validate payroll
SALLE_FETE.VALIDER	Validate party-room reservation
FINANCES.ENCAISSER	Cash/payment receipt
*.SUPERVISER	Supervisor dashboard access per module
SIGNALEMENT.VOIR / CREER / MODIFIER	Incident reporting

Seeded roles

Code	Label	Notes
DIRIGEANT	Dirigeant	All permissions except RESIDENT.VOIR
ADMINISTRATEUR	Administrateur	All permissions except RESIDENT.VOIR
CAISSIER	Caissier	Cash, payments, invoices, clients
RESPONSABLE_RESIDENCE	Responsable résidence	Housing and tenants
RESPONSABLE_MAGASIN	Responsable magasin	Shop
RESPONSABLE_PRESSING	Responsable pressing	Laundry
RESPONSABLE_RESTAURANT	Responsable restaurant	Restaurant
RESPONSABLE_SALLE_FETE	Responsable salle de fête	Party room
RH	Ressources humaines	Payroll
EMPLOYE	Employé	Minimal read
RESIDENT	Résident	Resident portal
CLIENT	Client	Self-registered public account

Functional roles for front-line staff (e.g. RESTAURANT_SERVEUR, PRESSING_RECEPTIONNISTE, RESIDENCE_CAISSIER, MARCHANDISE_VENDEUR) receive only the permissions needed for a specific step in the workflow.

Special role rules

• ADMINISTRATEUR and DIRIGEANT get all permissions via a CROSS JOIN on admin.permission.
• RESIDENT.VOIR is intentionally revoked from admin roles so that resident-portal scoping cannot be bypassed.
• All roles can create and view their own SIGNALEMENT (incident report); only CAISSIER, RH and RESPONSABLE_* can modify them.

Authentication tokens

admin.refresh_token

Stores SHA-256 hashes of opaque refresh tokens for session rotation.

Column	Notes
id_refresh_token	PK
id_utilisateur	FK → admin.utilisateur (ON DELETE CASCADE)
token_hash	SHA-256 hex, unique
expires_at	Expiration timestamp
revoked_at	Null while valid
replaced_by_hash	Hash of the next token in the rotation chain
user_agent	Client user agent
adresse_ip	IPv4 or IPv6 max 45 chars

Refresh tokens are excluded from the business audit triggers.

admin.password_reset_token

One-time, short-lived reset tokens.

Column	Notes
id_reset_token	PK
id_utilisateur	FK → admin.utilisateur (ON DELETE CASCADE)
token_hash	SHA-256 hex, unique
expires_at	Short TTL
created_at	Created at
used_at	Null until consumed

Password reset tokens are also excluded from the audit triggers.

Audit

audit.journal_audit

Append-only table. One row per business operation.

Column	Notes
id_trace	PK
date_heure	Operation timestamp
id_utilisateur	Actor, FK → admin.utilisateur
module	Schema name
operation	INSERT, UPDATE or DELETE
entite	Table name
entite_id	PK of affected row
description	Human-readable summary
montant	Monetary amount auto-extracted when present
avant	Row state before update/delete
apres	Row state after insert/update

Audit functions

Function	Purpose
audit.masquer_donnees(jsonb)	Redacts the mot_de_passe field to *** before it is written
audit.enregistrer_trace()	Trigger function that captures DML on every business table
audit.installer_triggers()	Discovers every table in the 11 business schemas and creates trg_audit_<table>

Actor resolution

audit.enregistrer_trace reads the session variable sim.id_utilisateur. If it is not set, the trace is attributed to the system user (created automatically during migration).

Excluded tables

Token tables are not audited:

• admin.refresh_token
• admin.password_reset_token

Backup and password metadata

admin.sauvegarde and admin.sauvegarde_planification

These tables track logical backups created by make backup. They store start/end time, type (AUTOMATIQUE / MANUELLE), status, size and path. The planning table is a singleton (one row) managed by a unique index on the constant expression (1).

Password hashes

• Dev/demo password hashes are generated by scripts/hash-seeds.mjs and committed in seeds/generated/10_auth_passwords.sql.
• The production admin password is hashed at deploy time by scripts/hash-prod-admin.mjs from .env.prod and piped to psql; it is never persisted outside the database.

Security recipes and examples

Create a new role and grant permissions

-- 1. Insert the role
INSERT INTO admin.role (code, libelle, description)
VALUES ('RESPONSABLE_MARKET', 'Responsable Market', 'Gestion complète du market')
ON CONFLICT (code) DO NOTHING;

-- 2. Create the permissions if they do not exist
INSERT INTO admin.permission (code, libelle) VALUES
    ('MARKET.VOIR',     'Voir le market'),
    ('MARKET.CREER',    'Créer dans le market'),
    ('MARKET.MODIFIER', 'Modifier dans le market'),
    ('MARKET.SUPPRIMER','Supprimer dans le market')
ON CONFLICT (code) DO NOTHING;

-- 3. Link the permissions to the role
INSERT INTO admin.role_permission (id_role, id_permission)
SELECT r.id_role, p.id_permission
FROM admin.role r
CROSS JOIN admin.permission p
WHERE r.code = 'RESPONSABLE_MARKET'
  AND p.code IN ('MARKET.VOIR', 'MARKET.CREER', 'MARKET.MODIFIER', 'MARKET.SUPPRIMER')
ON CONFLICT (id_role, id_permission) DO NOTHING;

Create a user and assign a role

-- The login must be unique; the password is a bcrypt/argon2 hash produced by the backend.
INSERT INTO admin.utilisateur (
    nom, prenom, login, mot_de_passe, id_role, actif, date_creation
)
VALUES (
    'Diallo', 'Fatou', 'fdiallo',
    '$2b$10$...',  -- hashed by backend
    (SELECT id_role FROM admin.role WHERE code = 'RESPONSABLE_MARKET'),
    TRUE,
    now()
)
ON CONFLICT (login) DO NOTHING;

Check a user’s effective permissions

SELECT p.code
FROM admin.utilisateur u
JOIN admin.role r ON r.id_role = u.id_role
JOIN admin.role_permission rp ON rp.id_role = r.id_role
JOIN admin.permission p ON p.id_permission = rp.id_permission
WHERE u.login = 'fdiallo'
ORDER BY p.code;

Set the audit actor before a transaction

Your application should run this before any DML:

SELECT set_config('sim.id_utilisateur', (
    SELECT id_utilisateur::text FROM admin.utilisateur WHERE login = 'fdiallo'
), true);

Then the next INSERT/UPDATE/DELETE on a business table will be attributed to that user in audit.journal_audit.

Inspect the audit trail for a record

SELECT
    ja.date_heure,
    u.login,
    ja.operation,
    ja.entite,
    ja.entite_id,
    ja.montant,
    ja.avant,
    ja.apres
FROM audit.journal_audit ja
LEFT JOIN admin.utilisateur u ON u.id_utilisateur = ja.id_utilisateur
WHERE ja.entite = 'contrat_location'
  AND ja.entite_id = 1
ORDER BY ja.date_heure DESC;

Verify that password fields are masked in the audit trail

SELECT apres
FROM audit.journal_audit
WHERE entite = 'utilisateur'
  AND operation = 'UPDATE'
  AND avant LIKE '%mot_de_passe%'
LIMIT 1;

-- Expected: the apres column contains 'mot_de_passe': '***'

What to do, what not to do

Do	Don’t
Generate dev hashes with make password-hashes	Write raw plaintext passwords into seed files
Set sim.id_utilisateur on every application connection	Leave it unset and let every trace be system
Use ON CONFLICT when inserting roles/permissions	Assume the seed has already run and omit ON CONFLICT
Hash the production admin with scripts/hash-prod-admin.mjs	Commit .env.prod or any real password to Git
Query admin.role_permission for access decisions	Hard-code role names in application code instead of permissions